Taking the Highest Value

[andyscott]

Hi Guys,

I was wondering how you can take the highest value in a series of cells ?

i.e. I have a series of IF statements that return TEXT values in 4 cells.

The values can either be "HL1, HL2, HL3, HL4, PT" in cells A1 through A4.

What I am trying to do is come up with a way to take the HIGHEST of these values.

i.e. so if cells A1 & A3 = "HL4" and A2 = "HL3" ..... then it takes "HL4" because its the highest value.

i.e. 2 ... so if cells A2 & A3 = "HL2" and A4 = "HL3" ..... then it takes "HL3" because its the highest value etc

Little unsure about how to do this ? Any ideas ?

 

[hsk]

Are there only 4 values .... then may be this can help

=MAX(RIGHT(A1,1),RIGHT(A2,1),RIGHT(A3,1),RIGHT(A4,1))

 

[tusharm]

How does PT compare with the various HL values? And, how does HL04 compare with HL4?

If you want to rely on XL's native ranking, sort the data in descending order and use the value in the first cell.

Hi Guys,

I was wondering how you can take the highest value in a series of cells ?

i.e. I have a series of IF statements that return TEXT values in 4 cells.

The values can either be "HL1, HL2, HL3, HL4, PT" in cells A1 through A4.

What I am trying to do is come up with a way to take the HIGHEST of these values.

i.e. so if cells A1 & A3 = "HL4" and A2 = "HL3" ..... then it takes "HL4" because its the highest value.

i.e. 2 ... so if cells A2 & A3 = "HL2" and A4 = "HL3" ..... then it takes "HL3" because its the highest value etc

Little unsure about how to do this ? Any ideas ?

 

[andyscott]

PT Highest

Hi Guys,

Thanks for the responses .... PT is the HIGHEST level

so HL1 is the lowest and PT is the highest .....

any idea's of the formula ? i.e. the MAX doesnt work because it relies on all 4 values .....

i.e. I need it to return the highest value no matter what

i.e. if A1 = HL1, and A2 --> A4 = 0 ... then return HL1
i.e. if A1 = HL1, A2 = HL2, A3 = HL3 and A4 = 0 .... then return HL3

etc ..... ? Thanks!

 

[barry houdini]

Here's one way....

=LOOKUP(2,1/COUNTIF(A1:A4,{"HL1","HL2","HL3","HL4","PT"}),{"HL1","HL2","HL3","HL4","PT"})

 

[andyscott]

Thanks!

Legend ... works a treat! thanks! :D :D