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

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

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

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

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

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