=ifs or =if(or(

[Mel Smith]

Hello folks,

Could someone help me with this formula, please? I have two columns containing values (C4:C37) and (E4:E37). What I am trying (and failing) to achieve is in G4 (and I will copy it down to G37) is if the value in C4 or E4 is the Max value of C4:C37 OR E4:E37 then display that value, otherwise show nothing ("").

Many thanks.

Mel

 

[Norie]

Perhaps.

=IF(C4=MAX($C$4:$C$37),C4,IF(E4=MAX($E$4:$E$37),E4,""))

 

[Mel Smith]

Many thanks, Norie! I was obviously over-thinking it!

Mel

 

[JoeMo]

You didn't say what you want to see if both C4 and E4 are the largest values in their respective ranges. This will return the larger of C4 or E4 in that event.
=IF(OR(C4=MAX(C4:C37),E4=MAX(E4:E37)),MAX(C4:E37),"")

 

[Mel Smith]

Hi JoMo,

Yes, sorry. What I want to display is the largest value, which might be in C4:C37 and/or E4:E37. It is possible that there might be 3, 4 or more highest values and I'd like to display them all.

I slightly amended your code to read: =IF(OR(C4=MAX($C$4:$C$37),E4=MAX($E$4:$E$37)),MAX($C$4:$C37,$E4:$E$37),"") and copied it down and I found that in cell E4 the highest overallvalue was displayed (this was E4) but further down the column the value displayed was not the highest overall value but rather the highest value from C4:C37 (the formula in the "errant" cell being: =IF(OR(C21=MAX($C$4:$C$37),E21=MAX($E$4:$E$37)),MAX($C$4:$C54,$E21:$E$37),"")

Mel

 

[Mel Smith]

On further checking/testing using the formula: =IF(OR(C4=MAX($C$4:$C$37),E4=MAX($E$4:$E$37)),MAX($C$4:$C$37,$E$4:$E$37),"")

I am now having the correct highest value displayed (in this case 24) BUT this is displayed in two cells, G8 and G28 where C8 contains 24 but E28 contains 15 (the highest value in column E).

Where am I going wrong?

Mel

 

[Mel Smith]

I've changed the formula back to: =IF(OR(C28=MAX($C$4:$C$37),E28=MAX($E$4:$E$37)),MAX($C$4:$E$37),"") but I am still getting the same incorrect value displayed in cell E28

Perplexed!

Mel

 

[Norie]

Mel

What is it you are trying to achieve?

 

[JoeMo]

I've changed the formula back to: =IF(OR(C28=MAX($C$4:$C$37),E28=MAX($E$4:$E$37)),MAX($C$4:$E$37),"") but I am still getting the same incorrect value displayed in cell E28

Perplexed!

Mel

First, the 'changed' formula does exactly what the earlier formula does as MAX($C$4:$E$37) = MAX($C$4:$C$37,$E$4:$E$37).
And ... the result you are getting is correct based on the formula you are using. If either C28 or E28 is a max then the formula returns the Max value among all the values in the range C4:E37. So, it may be that E28 is the max value in E4:E37, but if the max in C4:C37 is larger than that, the formula will return the latter.

 

[Mel Smith]

In cells G4:G37 I'm trying to get the highest value from cells C4:C37 and/or cells E4:E37. In G4, for example, if the highest values from C4:C37 AND E4:E37 is not in either C4 or E4 then display "" and then have this copied down to G37. If, say the highest value in column C was 25 and the highest value in column E was 24 then the only value I want to display in G# will be 25 (the highest overall.

I hope this makes sense.

Mel