Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count most common answer.
I have 3 columns of 24 cells headed 'gold' 'silver' and 'bronze'. each of the
24 cells could have 1 of 5 answers depending on other calculations elsewhere on the sheet. What function can I put into cell 25 of each column to count to most common name? Or to put it another way, to see who won most golds, silvers and bronzes out of the 5 people? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count most common answer.
Well, you could do a little summary table below your data where you
list the five names, eg in A26:A30, and then in B26 you can use this formula: =COUNTIF(A$1:A$24,$A26) Then you can copy this into C26:D26, and then copy B26:D26 down to B30. It will show how many Gold, Silver and Bronze each person received, assuming Gold is in A1:A24, Silver is in B1:B24, and Bronze is in C1:C24. You could then use conditional formatting to highlight the highest in each category, using MAX. Hope this helps. Pete On Sep 2, 10:49*pm, Darren wrote: I have 3 columns of 24 cells headed 'gold' 'silver' and 'bronze'. each of the 24 cells could have 1 of 5 answers depending on other calculations elsewhere on the sheet. What function can I put into cell 25 of each column to count to most common name? Or to put it another way, to see who won most golds, silvers and bronzes out of the 5 people? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count most common answer.
Try this array formula** :
=INDEX(A2:A24,MODE(MATCH(A2:A24,A2:A24,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across as needed. -- Biff Microsoft Excel MVP "Darren" wrote in message ... I have 3 columns of 24 cells headed 'gold' 'silver' and 'bronze'. each of the 24 cells could have 1 of 5 answers depending on other calculations elsewhere on the sheet. What function can I put into cell 25 of each column to count to most common name? Or to put it another way, to see who won most golds, silvers and bronzes out of the 5 people? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count most common answer.
That works if you have a static list of 5 people & treat all evenly. I have a
simliar problem but i have an open list of participants and also want to assign points/weighting. Here is my data: A B C D E F G H I 1 Tom Frank Sue Bert Gary Sue Tom Sue Bert Pts 3 2 1 3 2 1 3 2 1 What i want to see is what is the most common name for event 1 (A1 to I1) but also apply weighting/points to the event. So if A1, D1 & G1 equals 3 points then B1, E1 & H1 = 2 points & C1, F1 & I1 - 1 points. So your Index formula works to show who is mentioned the most = Sue. But I want to see who has the most points for the event = Tom. Hope this is clear. Any help would be much appreciated as i have been going through the lists manually and compiling. "T. Valko" wrote: Try this array formula** : =INDEX(A2:A24,MODE(MATCH(A2:A24,A2:A24,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across as needed. -- Biff Microsoft Excel MVP "Darren" wrote in message ... I have 3 columns of 24 cells headed 'gold' 'silver' and 'bronze'. each of the 24 cells could have 1 of 5 answers depending on other calculations elsewhere on the sheet. What function can I put into cell 25 of each column to count to most common name? Or to put it another way, to see who won most golds, silvers and bronzes out of the 5 people? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count most common answer.
Try this array formula** :
=INDEX(B1:J1,MATCH(MAX(SUMIF(B1:J1,B1:J1,B2:J2)),S UMIF(B1:J1,B1:J1,B2:J2),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that if more than one person has the highest total the formula will return the leftmost name with the highest total. -- Biff Microsoft Excel MVP "Ben" wrote in message ... That works if you have a static list of 5 people & treat all evenly. I have a simliar problem but i have an open list of participants and also want to assign points/weighting. Here is my data: A B C D E F G H I 1 Tom Frank Sue Bert Gary Sue Tom Sue Bert Pts 3 2 1 3 2 1 3 2 1 What i want to see is what is the most common name for event 1 (A1 to I1) but also apply weighting/points to the event. So if A1, D1 & G1 equals 3 points then B1, E1 & H1 = 2 points & C1, F1 & I1 - 1 points. So your Index formula works to show who is mentioned the most = Sue. But I want to see who has the most points for the event = Tom. Hope this is clear. Any help would be much appreciated as i have been going through the lists manually and compiling. "T. Valko" wrote: Try this array formula** : =INDEX(A2:A24,MODE(MATCH(A2:A24,A2:A24,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across as needed. -- Biff Microsoft Excel MVP "Darren" wrote in message ... I have 3 columns of 24 cells headed 'gold' 'silver' and 'bronze'. each of the 24 cells could have 1 of 5 answers depending on other calculations elsewhere on the sheet. What function can I put into cell 25 of each column to count to most common name? Or to put it another way, to see who won most golds, silvers and bronzes out of the 5 people? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count most common answer.
thanks but when i insert the formula it returns Bert(only 4 points) as the
answer when it should be Tom with 6 points. I'm using Excel 2003. Not sure what the problem is? "T. Valko" wrote: Try this array formula** : =INDEX(B1:J1,MATCH(MAX(SUMIF(B1:J1,B1:J1,B2:J2)),S UMIF(B1:J1,B1:J1,B2:J2),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that if more than one person has the highest total the formula will return the leftmost name with the highest total. -- Biff Microsoft Excel MVP "Ben" wrote in message ... That works if you have a static list of 5 people & treat all evenly. I have a simliar problem but i have an open list of participants and also want to assign points/weighting. Here is my data: A B C D E F G H I 1 Tom Frank Sue Bert Gary Sue Tom Sue Bert Pts 3 2 1 3 2 1 3 2 1 What i want to see is what is the most common name for event 1 (A1 to I1) but also apply weighting/points to the event. So if A1, D1 & G1 equals 3 points then B1, E1 & H1 = 2 points & C1, F1 & I1 - 1 points. So your Index formula works to show who is mentioned the most = Sue. But I want to see who has the most points for the event = Tom. Hope this is clear. Any help would be much appreciated as i have been going through the lists manually and compiling. "T. Valko" wrote: Try this array formula** : =INDEX(A2:A24,MODE(MATCH(A2:A24,A2:A24,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across as needed. -- Biff Microsoft Excel MVP "Darren" wrote in message ... I have 3 columns of 24 cells headed 'gold' 'silver' and 'bronze'. each of the 24 cells could have 1 of 5 answers depending on other calculations elsewhere on the sheet. What function can I put into cell 25 of each column to count to most common name? Or to put it another way, to see who won most golds, silvers and bronzes out of the 5 people? . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count most common answer.
Here's a small sample file that demonstrates this.
xBen.xls 17kb http://cjoint.com/?bmsUtsNfjF -- Biff Microsoft Excel MVP "Ben" wrote in message ... thanks but when i insert the formula it returns Bert(only 4 points) as the answer when it should be Tom with 6 points. I'm using Excel 2003. Not sure what the problem is? "T. Valko" wrote: Try this array formula** : =INDEX(B1:J1,MATCH(MAX(SUMIF(B1:J1,B1:J1,B2:J2)),S UMIF(B1:J1,B1:J1,B2:J2),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that if more than one person has the highest total the formula will return the leftmost name with the highest total. -- Biff Microsoft Excel MVP "Ben" wrote in message ... That works if you have a static list of 5 people & treat all evenly. I have a simliar problem but i have an open list of participants and also want to assign points/weighting. Here is my data: A B C D E F G H I 1 Tom Frank Sue Bert Gary Sue Tom Sue Bert Pts 3 2 1 3 2 1 3 2 1 What i want to see is what is the most common name for event 1 (A1 to I1) but also apply weighting/points to the event. So if A1, D1 & G1 equals 3 points then B1, E1 & H1 = 2 points & C1, F1 & I1 - 1 points. So your Index formula works to show who is mentioned the most = Sue. But I want to see who has the most points for the event = Tom. Hope this is clear. Any help would be much appreciated as i have been going through the lists manually and compiling. "T. Valko" wrote: Try this array formula** : =INDEX(A2:A24,MODE(MATCH(A2:A24,A2:A24,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across as needed. -- Biff Microsoft Excel MVP "Darren" wrote in message ... I have 3 columns of 24 cells headed 'gold' 'silver' and 'bronze'. each of the 24 cells could have 1 of 5 answers depending on other calculations elsewhere on the sheet. What function can I put into cell 25 of each column to count to most common name? Or to put it another way, to see who won most golds, silvers and bronzes out of the 5 people? . |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count most common answer.
thanks i think it got it.
"T. Valko" wrote: Here's a small sample file that demonstrates this. xBen.xls 17kb http://cjoint.com/?bmsUtsNfjF -- Biff Microsoft Excel MVP "Ben" wrote in message ... thanks but when i insert the formula it returns Bert(only 4 points) as the answer when it should be Tom with 6 points. I'm using Excel 2003. Not sure what the problem is? "T. Valko" wrote: Try this array formula** : =INDEX(B1:J1,MATCH(MAX(SUMIF(B1:J1,B1:J1,B2:J2)),S UMIF(B1:J1,B1:J1,B2:J2),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that if more than one person has the highest total the formula will return the leftmost name with the highest total. -- Biff Microsoft Excel MVP "Ben" wrote in message ... That works if you have a static list of 5 people & treat all evenly. I have a simliar problem but i have an open list of participants and also want to assign points/weighting. Here is my data: A B C D E F G H I 1 Tom Frank Sue Bert Gary Sue Tom Sue Bert Pts 3 2 1 3 2 1 3 2 1 What i want to see is what is the most common name for event 1 (A1 to I1) but also apply weighting/points to the event. So if A1, D1 & G1 equals 3 points then B1, E1 & H1 = 2 points & C1, F1 & I1 - 1 points. So your Index formula works to show who is mentioned the most = Sue. But I want to see who has the most points for the event = Tom. Hope this is clear. Any help would be much appreciated as i have been going through the lists manually and compiling. "T. Valko" wrote: Try this array formula** : =INDEX(A2:A24,MODE(MATCH(A2:A24,A2:A24,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across as needed. -- Biff Microsoft Excel MVP "Darren" wrote in message ... I have 3 columns of 24 cells headed 'gold' 'silver' and 'bronze'. each of the 24 cells could have 1 of 5 answers depending on other calculations elsewhere on the sheet. What function can I put into cell 25 of each column to count to most common name? Or to put it another way, to see who won most golds, silvers and bronzes out of the 5 people? . . |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count most common answer.
Good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Ben" wrote in message ... thanks i think it got it. "T. Valko" wrote: Here's a small sample file that demonstrates this. xBen.xls 17kb http://cjoint.com/?bmsUtsNfjF -- Biff Microsoft Excel MVP "Ben" wrote in message ... thanks but when i insert the formula it returns Bert(only 4 points) as the answer when it should be Tom with 6 points. I'm using Excel 2003. Not sure what the problem is? "T. Valko" wrote: Try this array formula** : =INDEX(B1:J1,MATCH(MAX(SUMIF(B1:J1,B1:J1,B2:J2)),S UMIF(B1:J1,B1:J1,B2:J2),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that if more than one person has the highest total the formula will return the leftmost name with the highest total. -- Biff Microsoft Excel MVP "Ben" wrote in message ... That works if you have a static list of 5 people & treat all evenly. I have a simliar problem but i have an open list of participants and also want to assign points/weighting. Here is my data: A B C D E F G H I 1 Tom Frank Sue Bert Gary Sue Tom Sue Bert Pts 3 2 1 3 2 1 3 2 1 What i want to see is what is the most common name for event 1 (A1 to I1) but also apply weighting/points to the event. So if A1, D1 & G1 equals 3 points then B1, E1 & H1 = 2 points & C1, F1 & I1 - 1 points. So your Index formula works to show who is mentioned the most = Sue. But I want to see who has the most points for the event = Tom. Hope this is clear. Any help would be much appreciated as i have been going through the lists manually and compiling. "T. Valko" wrote: Try this array formula** : =INDEX(A2:A24,MODE(MATCH(A2:A24,A2:A24,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy across as needed. -- Biff Microsoft Excel MVP "Darren" wrote in message ... I have 3 columns of 24 cells headed 'gold' 'silver' and 'bronze'. each of the 24 cells could have 1 of 5 answers depending on other calculations elsewhere on the sheet. What function can I put into cell 25 of each column to count to most common name? Or to put it another way, to see who won most golds, silvers and bronzes out of the 5 people? . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNT COMMON DATA | Excel Worksheet Functions | |||
count text values and return most common occurence | Excel Discussion (Misc queries) | |||
count # of instances in cell (a b a) answer 2 formula if possible | Excel Worksheet Functions | |||
Table to pick out most common entries and count occurences of each | Excel Worksheet Functions | |||
Count unique alpha numeric "characters" in a common cell | Excel Worksheet Functions |