Need another formula

  • Thread starter Thread starter lynn
  • Start date Start date
L

lynn

I posted an issue yesterday and got a great result, but I
was mistaken in what I needed the formula to do. This is
what I need -

100 clients took a servey 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.

I beleive the formula may have to involve the position,
meaning in my 4 columns, whatever column furthest to the
RIGHT that has any data would be my answer. Can anyone
assist ?
 
lynn said:
I posted an issue yesterday and got a great result, but I
was mistaken in what I needed the formula to do. This is
what I need -

100 clients took a servey 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.

I beleive the formula may have to involve the position,
meaning in my 4 columns, whatever column furthest to the
RIGHT that has any data would be my answer. Can anyone
assist ?
Click to expand...

I assume your four headings are in B1:E1 and the first data in B2:E2.
You could use this formula (probably for F2):
=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.
 
That's giving me #Value! error....


-----Original Message-----


I assume your four headings are in B1:E1 and the first data in B2:E2.
You could use this formula (probably for F2):
=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.


.
Click to expand...
 
I had it produce 'Excel error generated messages' and shut
me down, so I re-started my computer and re-invented the
file and it worked! A bug???
I left A1 blank and placed the 4 categories starting in B1
and the clients starting in A2. The formula was copied
from this thread and pasted into F2 then Ctrl+Shift+Enter.
I was then able to save the working file with no problems!
HTH
 
Ooops... i was so excited to try it that I missed that
step.... Thanks !! Works great !
 
Paul - I need to take this a step further. Now I need to
count the final column to see how many Milds there are,
and so on. A CountIf function is giving me a Zero, even
though there are many "Mild" results. Am I missing
something ?
 
One more issue - the array formula is working dont, except
if all 4 columns are blank. For the blank columns, the
formula is returning Mild. How can I get it so if all 4
are blank, I get something else as my answer,like N/A ?
 
Back
Top