When there is a tie for "MAX" values...

[Zakhary]

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,F7,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?

 

[T. Valko]

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","")))," ",",")

 

[Zakhary]

Hi Biff,
Thanks for this.
That is quite nasty, but it does work!

Thanks many!

 

[T. Valko]

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/3000-2077_4-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.

 

[Zakhary]

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!

 

[T. Valko]

Ok, well, you can shorten the formula slightly by using a named range.

Insert>Name>Define
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","")))," ",",")

 

[Rick Rothstein]

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","")))," ",",")