Find max value from 3 cells, return text, but 2 max values same!
Hi,
I deleted it (!) though I think it was this simple beasty below:
=IF(N3=U3,"SK17",IF(Q3=U3,"EC3",IF(T3=U3,"PL2")))
All this did of course is return the first occuring high value if there were
2 or more. I've chewed over many variations to give an "X" if two (or more)
equal max values occur but to no avail.
"Struggling in Sheffield" wrote:
Hi,
I'm using a simple formula to find and return the maximum value from 3
compared columns (N, Q & T) and place that max value in column U (same row).
Equation in column U is:
=MAX(N3,Q3,T3)
SK17 EC3 PL2 Max Text
N O P Q R S T U V
3 6 5 1 6
4 6 3 3 6
5 5 5 2 5
6 1 4 1 4
7 5 3 5 5
8 2 7 0 7
9 1 1 1 1
10 9 0 4 9
That works fine but my problem is I want to return a text note in column V
based on which column has supplied the max value. However, if the same max
value is in 2 or even all 3 of the checked columns, I need column V to simple
display an "X", as illustrated below.
SK17 EC3 PL2 Max Text
N O P Q R S T U V
3 6 5 1 6 SK17
4 6 3 3 6 SK17
5 5 5 2 5 X
6 1 4 1 4 EC3
7 5 3 5 5 X
8 2 7 0 7 EC3
9 1 1 1 1 X
10 9 0 4 9 SK17
I'm OK until two max values match but then it all falls apart.
As always any advice will be gratefully received.
Thanks,
Steve.
|