Taking the Highest Value

andyscott

Board Regular
Joined
May 21, 2007
Messages
63
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 ?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

hsk

Well-known Member
Joined
Oct 19, 2006
Messages
567
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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

Board Regular
Joined
May 21, 2007
Messages
63
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Here's one way....

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

Forum statistics

Threads
1,181,647
Messages
5,931,209
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top