|
When there is a tie for "MAX" values...
In a statistical summary, I have a column that shows the mode (i.e., the
answer with the highest proportion of entries) for a the entires of a series of likert scale ratings on a survey. I developed a nested "IF" function that uses a series of "MAX" conditions. This does the job for the most part, but there are instances where there is a tie for the mode, and rather than excel showing both numbers, it shows the first in the series of the nested IF. Here's the function I currently have enetered: =IF(B7=MAX(B7,D7,F7,H7,J7,L7),"5",IF(D7=MAX(B7,D7, F7,H7,J7,L7),"4",IF(F7=MAX(B7,D7,F7,H7,J7,L7),"3", IF(H7=MAX(B7,D7,F7,H7,J7,L7),"2",IF(J7=MAX(B7,D7,F 7,H7,J7,L7),"1",IF(L7=MAX(B7,D7,F7,H7,J7,L7),"0"," N/A")))))) What excel enters if 5 and 4 (i.e., B7 and D7) are a tie: 5 What I want excel to enter if 5 and 4 (i.e., B7 and D7) are a tie: 5, 4 Is there a way to revise my formula to make this happen? -- -Zakhary |
When there is a tie for "MAX" values...
Kind of ugly but it works. All on one line:
=SUBSTITUTE(TRIM(IF(COUNT(B7,D7,F7,H7,J7,L7)=0,"N/A", IF(B7=MAX(B7,D7,F7,H7,J7,L7),5,"") &IF(D7=MAX(B7,D7,F7,H7,J7,L7)," 4","") &IF(F7=MAX(B7,D7,F7,H7,J7,L7)," 3","") &IF(H7=MAX(B7,D7,F7,H7,J7,L7)," 2","") &IF(J7=MAX(B7,D7,F7,H7,J7,L7)," 1","") &IF(L7=MAX(B7,D7,F7,H7,J7,L7)," 0","")))," ",",") -- Biff Microsoft Excel MVP "Zakhary" wrote in message ... In a statistical summary, I have a column that shows the mode (i.e., the answer with the highest proportion of entries) for a the entires of a series of likert scale ratings on a survey. I developed a nested "IF" function that uses a series of "MAX" conditions. This does the job for the most part, but there are instances where there is a tie for the mode, and rather than excel showing both numbers, it shows the first in the series of the nested IF. Here's the function I currently have enetered: =IF(B7=MAX(B7,D7,F7,H7,J7,L7),"5",IF(D7=MAX(B7,D7, F7,H7,J7,L7),"4",IF(F7=MAX(B7,D7,F7,H7,J7,L7),"3", IF(H7=MAX(B7,D7,F7,H7,J7,L7),"2",IF(J7=MAX(B7,D7,F 7,H7,J7,L7),"1",IF(L7=MAX(B7,D7,F7,H7,J7,L7),"0"," N/A")))))) What excel enters if 5 and 4 (i.e., B7 and D7) are a tie: 5 What I want excel to enter if 5 and 4 (i.e., B7 and D7) are a tie: 5, 4 Is there a way to revise my formula to make this happen? -- -Zakhary |
When there is a tie for "MAX" values...
Hi Biff,
Thanks for this. That is quite nasty, but it does work! Thanks many! -- -Zakhary "T. Valko" wrote: Kind of ugly but it works. All on one line: =SUBSTITUTE(TRIM(IF(COUNT(B7,D7,F7,H7,J7,L7)=0,"N/A", IF(B7=MAX(B7,D7,F7,H7,J7,L7),5,"") &IF(D7=MAX(B7,D7,F7,H7,J7,L7)," 4","") &IF(F7=MAX(B7,D7,F7,H7,J7,L7)," 3","") &IF(H7=MAX(B7,D7,F7,H7,J7,L7)," 2","") &IF(J7=MAX(B7,D7,F7,H7,J7,L7)," 1","") &IF(L7=MAX(B7,D7,F7,H7,J7,L7)," 0","")))," ",",") -- Biff Microsoft Excel MVP "Zakhary" wrote in message ... In a statistical summary, I have a column that shows the mode (i.e., the answer with the highest proportion of entries) for a the entires of a series of likert scale ratings on a survey. I developed a nested "IF" function that uses a series of "MAX" conditions. This does the job for the most part, but there are instances where there is a tie for the mode, and rather than excel showing both numbers, it shows the first in the series of the nested IF. Here's the function I currently have enetered: =IF(B7=MAX(B7,D7,F7,H7,J7,L7),"5",IF(D7=MAX(B7,D7, F7,H7,J7,L7),"4",IF(F7=MAX(B7,D7,F7,H7,J7,L7),"3", IF(H7=MAX(B7,D7,F7,H7,J7,L7),"2",IF(J7=MAX(B7,D7,F 7,H7,J7,L7),"1",IF(L7=MAX(B7,D7,F7,H7,J7,L7),"0"," N/A")))))) What excel enters if 5 and 4 (i.e., B7 and D7) are a tie: 5 What I want excel to enter if 5 and 4 (i.e., B7 and D7) are a tie: 5, 4 Is there a way to revise my formula to make this happen? -- -Zakhary |
When there is a tie for "MAX" values...
What is in the cells between the referenced cells? Your formula references
cells B7,D7,F7,H7,J7,L7. So what's in C7,E7,G7,I7,K7? If those cells are empty or contain text *and* you're open to using an add-in then you can use a smaller array formula. There is a free add-in here that contains a function that will do what you want. http://www.download.com/Morefunc/300...-10423159.html The function is called MCONCAT. The array formula** would be: =IF(COUNT(B7:L7)=0,"N/A",SUBSTITUTE(TRIM(MCONCAT(IF(B7:L7=MAX(B7:L7), (12-COLUMN(B7:L7))/2,"")&" "))," ",",")) ** 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. -- Biff Microsoft Excel MVP "Zakhary" wrote in message ... Hi Biff, Thanks for this. That is quite nasty, but it does work! Thanks many! -- -Zakhary "T. Valko" wrote: Kind of ugly but it works. All on one line: =SUBSTITUTE(TRIM(IF(COUNT(B7,D7,F7,H7,J7,L7)=0,"N/A", IF(B7=MAX(B7,D7,F7,H7,J7,L7),5,"") &IF(D7=MAX(B7,D7,F7,H7,J7,L7)," 4","") &IF(F7=MAX(B7,D7,F7,H7,J7,L7)," 3","") &IF(H7=MAX(B7,D7,F7,H7,J7,L7)," 2","") &IF(J7=MAX(B7,D7,F7,H7,J7,L7)," 1","") &IF(L7=MAX(B7,D7,F7,H7,J7,L7)," 0","")))," ",",") -- Biff Microsoft Excel MVP "Zakhary" wrote in message ... In a statistical summary, I have a column that shows the mode (i.e., the answer with the highest proportion of entries) for a the entires of a series of likert scale ratings on a survey. I developed a nested "IF" function that uses a series of "MAX" conditions. This does the job for the most part, but there are instances where there is a tie for the mode, and rather than excel showing both numbers, it shows the first in the series of the nested IF. Here's the function I currently have enetered: =IF(B7=MAX(B7,D7,F7,H7,J7,L7),"5",IF(D7=MAX(B7,D7, F7,H7,J7,L7),"4",IF(F7=MAX(B7,D7,F7,H7,J7,L7),"3", IF(H7=MAX(B7,D7,F7,H7,J7,L7),"2",IF(J7=MAX(B7,D7,F 7,H7,J7,L7),"1",IF(L7=MAX(B7,D7,F7,H7,J7,L7),"0"," N/A")))))) What excel enters if 5 and 4 (i.e., B7 and D7) are a tie: 5 What I want excel to enter if 5 and 4 (i.e., B7 and D7) are a tie: 5, 4 Is there a way to revise my formula to make this happen? -- -Zakhary |
When there is a tie for "MAX" values...
The cells in-between those are cells that show the percentage of participants
who answered in that way. So, B7 represents the number of respondents who gave a ranking of 5 for that lline-item, while C7 is the percentage of total participants who gave that ranking. After a page break, the C7 equivalent controls for male and female partipants and shows two percetages - the percentage of total male or female participants who gave the particular ranking, and the percentage of total participants who gave this ranking AND happened to be male or female (what I refer to as the particular gender's "percent-contribution."). These formulas are also a little nested. Example (C42): =ROUND((B42/'Demographics and General'!D8)*100,2)&"%¹ ("&IF(B7=0,0,(ROUND((B42/B7)*100,2)))&"%)²" That ¹ and ² are for viewers to use to refer to the end-notes in the footer of the spreadsheet. Nevertheless, the spreadsheet is a nice one, and as tedious as these excel formulas are, once developed, they make everything so much easier. I am happy with what I have now for my spreadsheet, but will now feel obligated to educate myself on these various formulas that I am otherwise foreign to - SUBSTITUTE, TRIM, COUNT, and MCONCAT. Thanks again! -- -Zakhary "T. Valko" wrote: What is in the cells between the referenced cells? Your formula references cells B7,D7,F7,H7,J7,L7. So what's in C7,E7,G7,I7,K7? If those cells are empty or contain text *and* you're open to using an add-in then you can use a smaller array formula. There is a free add-in here that contains a function that will do what you want. http://www.download.com/Morefunc/300...-10423159.html The function is called MCONCAT. The array formula** would be: =IF(COUNT(B7:L7)=0,"N/A",SUBSTITUTE(TRIM(MCONCAT(IF(B7:L7=MAX(B7:L7), (12-COLUMN(B7:L7))/2,"")&" "))," ",",")) ** 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. -- Biff Microsoft Excel MVP "Zakhary" wrote in message ... Hi Biff, Thanks for this. That is quite nasty, but it does work! Thanks many! -- -Zakhary "T. Valko" wrote: Kind of ugly but it works. All on one line: =SUBSTITUTE(TRIM(IF(COUNT(B7,D7,F7,H7,J7,L7)=0,"N/A", IF(B7=MAX(B7,D7,F7,H7,J7,L7),5,"") &IF(D7=MAX(B7,D7,F7,H7,J7,L7)," 4","") &IF(F7=MAX(B7,D7,F7,H7,J7,L7)," 3","") &IF(H7=MAX(B7,D7,F7,H7,J7,L7)," 2","") &IF(J7=MAX(B7,D7,F7,H7,J7,L7)," 1","") &IF(L7=MAX(B7,D7,F7,H7,J7,L7)," 0","")))," ",",") -- Biff Microsoft Excel MVP "Zakhary" wrote in message ... In a statistical summary, I have a column that shows the mode (i.e., the answer with the highest proportion of entries) for a the entires of a series of likert scale ratings on a survey. I developed a nested "IF" function that uses a series of "MAX" conditions. This does the job for the most part, but there are instances where there is a tie for the mode, and rather than excel showing both numbers, it shows the first in the series of the nested IF. Here's the function I currently have enetered: =IF(B7=MAX(B7,D7,F7,H7,J7,L7),"5",IF(D7=MAX(B7,D7, F7,H7,J7,L7),"4",IF(F7=MAX(B7,D7,F7,H7,J7,L7),"3", IF(H7=MAX(B7,D7,F7,H7,J7,L7),"2",IF(J7=MAX(B7,D7,F 7,H7,J7,L7),"1",IF(L7=MAX(B7,D7,F7,H7,J7,L7),"0"," N/A")))))) What excel enters if 5 and 4 (i.e., B7 and D7) are a tie: 5 What I want excel to enter if 5 and 4 (i.e., B7 and D7) are a tie: 5, 4 Is there a way to revise my formula to make this happen? -- -Zakhary |
When there is a tie for "MAX" values...
Ok, well, you can shorten the formula slightly by using a named range.
InsertNameDefine Name: rng Refers to: =$B$7,$D$7,$F$7,$H$7,$J$7,$L$7 Then the formula becomes: =SUBSTITUTE(TRIM(IF(COUNT(rng)=0,"N/A", IF(B7=MAX(rng),5,"")&IF(D7=MAX(rng)," 4","") &IF(F7=MAX(rng)," 3","")&IF(H7=MAX(rng)," 2","") &IF(J7=MAX(rng)," 1","")&IF(L7=MAX(rng)," 0","")))," ",",") -- Biff Microsoft Excel MVP "Zakhary" wrote in message ... The cells in-between those are cells that show the percentage of participants who answered in that way. So, B7 represents the number of respondents who gave a ranking of 5 for that lline-item, while C7 is the percentage of total participants who gave that ranking. After a page break, the C7 equivalent controls for male and female partipants and shows two percetages - the percentage of total male or female participants who gave the particular ranking, and the percentage of total participants who gave this ranking AND happened to be male or female (what I refer to as the particular gender's "percent-contribution."). These formulas are also a little nested. Example (C42): =ROUND((B42/'Demographics and General'!D8)*100,2)&"%¹ ("&IF(B7=0,0,(ROUND((B42/B7)*100,2)))&"%)²" That ¹ and ² are for viewers to use to refer to the end-notes in the footer of the spreadsheet. Nevertheless, the spreadsheet is a nice one, and as tedious as these excel formulas are, once developed, they make everything so much easier. I am happy with what I have now for my spreadsheet, but will now feel obligated to educate myself on these various formulas that I am otherwise foreign to - SUBSTITUTE, TRIM, COUNT, and MCONCAT. Thanks again! -- -Zakhary "T. Valko" wrote: What is in the cells between the referenced cells? Your formula references cells B7,D7,F7,H7,J7,L7. So what's in C7,E7,G7,I7,K7? If those cells are empty or contain text *and* you're open to using an add-in then you can use a smaller array formula. There is a free add-in here that contains a function that will do what you want. http://www.download.com/Morefunc/300...-10423159.html The function is called MCONCAT. The array formula** would be: =IF(COUNT(B7:L7)=0,"N/A",SUBSTITUTE(TRIM(MCONCAT(IF(B7:L7=MAX(B7:L7), (12-COLUMN(B7:L7))/2,"")&" "))," ",",")) ** 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. -- Biff Microsoft Excel MVP "Zakhary" wrote in message ... Hi Biff, Thanks for this. That is quite nasty, but it does work! Thanks many! -- -Zakhary "T. Valko" wrote: Kind of ugly but it works. All on one line: =SUBSTITUTE(TRIM(IF(COUNT(B7,D7,F7,H7,J7,L7)=0,"N/A", IF(B7=MAX(B7,D7,F7,H7,J7,L7),5,"") &IF(D7=MAX(B7,D7,F7,H7,J7,L7)," 4","") &IF(F7=MAX(B7,D7,F7,H7,J7,L7)," 3","") &IF(H7=MAX(B7,D7,F7,H7,J7,L7)," 2","") &IF(J7=MAX(B7,D7,F7,H7,J7,L7)," 1","") &IF(L7=MAX(B7,D7,F7,H7,J7,L7)," 0","")))," ",",") -- Biff Microsoft Excel MVP "Zakhary" wrote in message ... In a statistical summary, I have a column that shows the mode (i.e., the answer with the highest proportion of entries) for a the entires of a series of likert scale ratings on a survey. I developed a nested "IF" function that uses a series of "MAX" conditions. This does the job for the most part, but there are instances where there is a tie for the mode, and rather than excel showing both numbers, it shows the first in the series of the nested IF. Here's the function I currently have enetered: =IF(B7=MAX(B7,D7,F7,H7,J7,L7),"5",IF(D7=MAX(B7,D7, F7,H7,J7,L7),"4",IF(F7=MAX(B7,D7,F7,H7,J7,L7),"3", IF(H7=MAX(B7,D7,F7,H7,J7,L7),"2",IF(J7=MAX(B7,D7,F 7,H7,J7,L7),"1",IF(L7=MAX(B7,D7,F7,H7,J7,L7),"0"," N/A")))))) What excel enters if 5 and 4 (i.e., B7 and D7) are a tie: 5 What I want excel to enter if 5 and 4 (i.e., B7 and D7) are a tie: 5, 4 Is there a way to revise my formula to make this happen? -- -Zakhary |
When there is a tie for "MAX" values...
Since the "in-between" cells contain text representations of percentages
(the OP has concatenated the percent sign onto the number), you can use MAX(B7:L7) and it should produce the same value as the MAX(B7,D7,F7,H7,J7,L7) function call you used in your original formula... that should "simplify" the formula somewhat without having to use the named range. =SUBSTITUTE(TRIM(IF(COUNT(B7:L7)=0,"N/A",IF(B7=MAX(B7:L7),5,"")& IF(D7=MAX(B7:L7)," 4","")&IF(F7=MAX(B7:L7)," 3","")&IF(H7=MAX(B7:L7), " 2","")&IF(J7=MAX(B7:L7)," 1","")&IF(L7=MAX(B7:L7)," 0","")))," ",",") -- Rick (MVP - Excel) "T. Valko" wrote in message ... Ok, well, you can shorten the formula slightly by using a named range. InsertNameDefine Name: rng Refers to: =$B$7,$D$7,$F$7,$H$7,$J$7,$L$7 Then the formula becomes: =SUBSTITUTE(TRIM(IF(COUNT(rng)=0,"N/A", IF(B7=MAX(rng),5,"")&IF(D7=MAX(rng)," 4","") &IF(F7=MAX(rng)," 3","")&IF(H7=MAX(rng)," 2","") &IF(J7=MAX(rng)," 1","")&IF(L7=MAX(rng)," 0","")))," ",",") -- Biff Microsoft Excel MVP "Zakhary" wrote in message ... The cells in-between those are cells that show the percentage of participants who answered in that way. So, B7 represents the number of respondents who gave a ranking of 5 for that lline-item, while C7 is the percentage of total participants who gave that ranking. After a page break, the C7 equivalent controls for male and female partipants and shows two percetages - the percentage of total male or female participants who gave the particular ranking, and the percentage of total participants who gave this ranking AND happened to be male or female (what I refer to as the particular gender's "percent-contribution."). These formulas are also a little nested. Example (C42): =ROUND((B42/'Demographics and General'!D8)*100,2)&"%¹ ("&IF(B7=0,0,(ROUND((B42/B7)*100,2)))&"%)²" That ¹ and ² are for viewers to use to refer to the end-notes in the footer of the spreadsheet. Nevertheless, the spreadsheet is a nice one, and as tedious as these excel formulas are, once developed, they make everything so much easier. I am happy with what I have now for my spreadsheet, but will now feel obligated to educate myself on these various formulas that I am otherwise foreign to - SUBSTITUTE, TRIM, COUNT, and MCONCAT. Thanks again! -- -Zakhary "T. Valko" wrote: What is in the cells between the referenced cells? Your formula references cells B7,D7,F7,H7,J7,L7. So what's in C7,E7,G7,I7,K7? If those cells are empty or contain text *and* you're open to using an add-in then you can use a smaller array formula. There is a free add-in here that contains a function that will do what you want. http://www.download.com/Morefunc/300...-10423159.html The function is called MCONCAT. The array formula** would be: =IF(COUNT(B7:L7)=0,"N/A",SUBSTITUTE(TRIM(MCONCAT(IF(B7:L7=MAX(B7:L7), (12-COLUMN(B7:L7))/2,"")&" "))," ",",")) ** 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. -- Biff Microsoft Excel MVP "Zakhary" wrote in message ... Hi Biff, Thanks for this. That is quite nasty, but it does work! Thanks many! -- -Zakhary "T. Valko" wrote: Kind of ugly but it works. All on one line: =SUBSTITUTE(TRIM(IF(COUNT(B7,D7,F7,H7,J7,L7)=0,"N/A", IF(B7=MAX(B7,D7,F7,H7,J7,L7),5,"") &IF(D7=MAX(B7,D7,F7,H7,J7,L7)," 4","") &IF(F7=MAX(B7,D7,F7,H7,J7,L7)," 3","") &IF(H7=MAX(B7,D7,F7,H7,J7,L7)," 2","") &IF(J7=MAX(B7,D7,F7,H7,J7,L7)," 1","") &IF(L7=MAX(B7,D7,F7,H7,J7,L7)," 0","")))," ",",") -- Biff Microsoft Excel MVP "Zakhary" wrote in message ... In a statistical summary, I have a column that shows the mode (i.e., the answer with the highest proportion of entries) for a the entires of a series of likert scale ratings on a survey. I developed a nested "IF" function that uses a series of "MAX" conditions. This does the job for the most part, but there are instances where there is a tie for the mode, and rather than excel showing both numbers, it shows the first in the series of the nested IF. Here's the function I currently have enetered: =IF(B7=MAX(B7,D7,F7,H7,J7,L7),"5",IF(D7=MAX(B7,D7, F7,H7,J7,L7),"4",IF(F7=MAX(B7,D7,F7,H7,J7,L7),"3", IF(H7=MAX(B7,D7,F7,H7,J7,L7),"2",IF(J7=MAX(B7,D7,F 7,H7,J7,L7),"1",IF(L7=MAX(B7,D7,F7,H7,J7,L7),"0"," N/A")))))) What excel enters if 5 and 4 (i.e., B7 and D7) are a tie: 5 What I want excel to enter if 5 and 4 (i.e., B7 and D7) are a tie: 5, 4 Is there a way to revise my formula to make this happen? -- -Zakhary |
| All times are GMT +1. The time now is 05:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com