More help on a current issue

[lynn]

I posted an issue last week and got a great result. I need
to take this a step further, though. I'm copying the
string of Text outlining my issue and the formula
suggested. In short, the formula works fine. I end up with
a column of info saying either Mild, Moderate, etc.. The
problem I am having now, I'm trying to do a Count
function, to count all of the Milds for instance. A Count
If is giving me a Zero, even though there are clearly
Milds in the column. Another problem is, in the event
all 4 columns are BLANK, the formula is returning Mild as
the answer.

So I actually need 2 things - alter the formula so that if
the 4 columns are Blank, give me an N/A or something along
those lines; then I need a way to count my answers. Below
is my original post, followed by the solution I got to
that part -

100 clients took a survey and had to answer Mild,

Moderate, Severe, Maximal. I have these 4 as headings
for
Columns B,C,D,E. The rows have numbers. Example, client
1
said Mild 5 times, and Severe once. I need a formula
that will let me know what their most 'severe' or highest
response is. In other words, for client 1, although they
said Mild 5 and Severe only 1, Severe ranks 'higher', so
their highest response is Severe. If there was a 1 in
the Maximal column, my answer needs to read Maximal.

Answer:

I assume your four headings are in B1:E1 and the first
data in B2:E2. =INDEX($B$1:$E$1,MAX((B2:E2<>"")*(COLUMN
(B2:E2)-1)))
array-entered (using CTRL+SHIFT+ENTER rather than just
ENTER). Then copy down as far as required.

 

[Paul]

I posted an issue last week and got a great result. I need
to take this a step further, though. I'm copying the
string of Text outlining my issue and the formula
suggested. In short, the formula works fine. I end up with
a column of info saying either Mild, Moderate, etc.. The
problem I am having now, I'm trying to do a Count
function, to count all of the Milds for instance. A Count
If is giving me a Zero, even though there are clearly
Milds in the column. Another problem is, in the event
all 4 columns are BLANK, the formula is returning Mild as
the answer.

So I actually need 2 things - alter the formula so that if
the 4 columns are Blank, give me an N/A or something along
those lines; then I need a way to count my answers. Below
is my original post, followed by the solution I got to
that part -

Moderate, Severe, Maximal. I have these 4 as headings
for
Columns B,C,D,E. The rows have numbers. Example, client
1
said Mild 5 times, and Severe once. I need a formula
that will let me know what their most 'severe' or highest
response is. In other words, for client 1, although they
said Mild 5 and Severe only 1, Severe ranks 'higher', so
their highest response is Severe. If there was a 1 in
the Maximal column, my answer needs to read Maximal.

Answer:

I assume your four headings are in B1:E1 and the first
data in B2:E2. =INDEX($B$1:$E$1,MAX((B2:E2<>"")*(COLUMN
(B2:E2)-1)))
array-entered (using CTRL+SHIFT+ENTER rather than just
ENTER). Then copy down as far as required.

When following up something that's already been started, don't start a new
thread. Instead, just reply to the answer you received.

To return #N/A if all four cells are blank, use:
=IF(AND(ISBLANK(B2:E2)),NA(),INDEX($B$1:$E$1,MAX((B2:E2<>"")*(COLUMN(B2:E2)-
1))))
(again array-entered).
You could replace NA() by something else, say a text string such as "none"
(including the quotes), if you prefer.

As for counting answers in column F (assuming that's where you've put the
formulas), you could use something along the lines:
=COUNTIF($F$2:$F$10,B1)
or
=COUNTIF($F$2:$F$10,"Mild")
suitably adjusted for the number of rows in use in column F.