|
Displaying Data
I have a spreadsheet which shows names with 2 elements of performance data.
I want to display the names in a 16 box matrix based on their individual scores. Can anybody help? Thanks -- Sue |
Displaying Data
One interpretation & a way, shown in this sample:
http://www.freefilehosting.net/download/3hc5a Display data in matrix.xls Source data is assumed in A2:C8 names in col A, perf data in cols B & C With perf data for col C uniquely listed in F1:I1 and that for col B listed in E2:E5, In F2, array-entered (press CTRL+SHIFT+ENTER): =IF(ISNA(MATCH(1,($B$2:$B$8=$E2)*($C$2:$C$8=F$1),0 )),"",INDEX($A$2:$A$8,MATCH(1,($B$2:$B$8=$E2)*($C$ 2:$C$8=F$1),0))) Copy F2 across/fill down to I4 to populate the matrix with the names from col A -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sue" wrote: I have a spreadsheet which shows names with 2 elements of performance data. I want to display the names in a 16 box matrix based on their individual scores. Can anybody help? Thanks -- Sue |
Displaying Data
Max
Thanks for this. This formula works but lists of names I have are generally around 20 or more. The spread of performance combination ratings would not be more than 10 per unique combination. If I set the Matrix up with say 10 rows per unique peformance comination, could I adapt the formula to pick up more than 16 names? Sue "Max" wrote: One interpretation & a way, shown in this sample: http://www.freefilehosting.net/download/3hc5a Display data in matrix.xls Source data is assumed in A2:C8 names in col A, perf data in cols B & C With perf data for col C uniquely listed in F1:I1 and that for col B listed in E2:E5, In F2, array-entered (press CTRL+SHIFT+ENTER): =IF(ISNA(MATCH(1,($B$2:$B$8=$E2)*($C$2:$C$8=F$1),0 )),"",INDEX($A$2:$A$8,MATCH(1,($B$2:$B$8=$E2)*($C$ 2:$C$8=F$1),0))) Copy F2 across/fill down to I4 to populate the matrix with the names from col A -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sue" wrote: I have a spreadsheet which shows names with 2 elements of performance data. I want to display the names in a 16 box matrix based on their individual scores. Can anybody help? Thanks -- Sue |
Displaying Data
Sue,
Can you upload a sample file/data* for a clear visual using the free filehost: http://www.freefilehosting.net/ then post a link to it here (like what I did earlier for my sample) *dummy-out the names as appropriate Just copy the "direct link" which is generated after you upload your sample, then paste the link into your reply here The underlying constraint is that each name to be emplaced in the matrix should have a unique performance combination rating. That's the way MATCH works. It returns the first match from left to right, or top to bottom. If you have names with identical combos, then only the "first" one will get plotted in the matrix. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sue" wrote in message ... Max Thanks for this. This formula works but lists of names I have are generally around 20 or more. The spread of performance combination ratings would not be more than 10 per unique combination. If I set the Matrix up with say 10 rows per unique peformance comination, could I adapt the formula to pick up more than 16 names? Sue |
Displaying Data
Hi Max
Thanks for looking at this. Several people will have the same combination of performance ratings. Here's the link to what it might look like. Each of the 16 cells in the matrix can have more than one name in it. http://www.freefilehosting.net/download/3hd0g Best regards -- Sue "Max" wrote: Sue, Can you upload a sample file/data* for a clear visual using the free filehost: http://www.freefilehosting.net/ then post a link to it here (like what I did earlier for my sample) *dummy-out the names as appropriate Just copy the "direct link" which is generated after you upload your sample, then paste the link into your reply here The underlying constraint is that each name to be emplaced in the matrix should have a unique performance combination rating. That's the way MATCH works. It returns the first match from left to right, or top to bottom. If you have names with identical combos, then only the "first" one will get plotted in the matrix. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sue" wrote in message ... Max Thanks for this. This formula works but lists of names I have are generally around 20 or more. The spread of performance combination ratings would not be more than 10 per unique combination. If I set the Matrix up with say 10 rows per unique peformance comination, could I adapt the formula to pick up more than 16 names? Sue |
Displaying Data
Sue,
As mentioned in my earlier response ("underlying constraint"), I'm afraid that this: .. Each of the 16 cells in the matrix can have more than one name in it. means that there are names with identical combos I'm not sure that there's a formulas play which can derive the above You might want to try posting in .programming for a vba solution -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sue" wrote in message ... Hi Max Thanks for looking at this. Several people will have the same combination of performance ratings. Here's the link to what it might look like. Each of the 16 cells in the matrix can have more than one name in it. http://www.freefilehosting.net/download/3hd0g Best regards -- Sue |
Displaying Data
OK. Thanks again for looking at it for me.
-- Sue "Max" wrote: Sue, As mentioned in my earlier response ("underlying constraint"), I'm afraid that this: .. Each of the 16 cells in the matrix can have more than one name in it. means that there are names with identical combos I'm not sure that there's a formulas play which can derive the above You might want to try posting in .programming for a vba solution -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sue" wrote in message ... Hi Max Thanks for looking at this. Several people will have the same combination of performance ratings. Here's the link to what it might look like. Each of the 16 cells in the matrix can have more than one name in it. http://www.freefilehosting.net/download/3hd0g Best regards -- Sue |
| All times are GMT +1. The time now is 02:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com