Conditional Maximum of a row of cells.

[temptemp9]

Hello everyone.
This looks like a simple problem, but I have to admit that I'm stumped.. This is my scenario:

A B C D E F G
15 25 38 42 -> 42
0 0 0 0 -> 0
-3 -5 -1 0 -> -1

The 'G' column should represent the maximum value of the cells in the same row (e.g., G1 = max(A1:D1)), with the following caveats:
- If all values are > 0, print the maximum;
- If all values are = 0, print 0.
- If all values are either 0 or negative, print the maximum of the negatives.

Can somebody point me to the right direction? Thanks in advance!

[cgc17]

Quick question - when you say maximum of the negatives, you mean closest to 0, right? Or do you mean the lowest value?

[cgc17]

Here is a formula to do what you want (assuming you are not looking for the lowest negative number - aka the one with the highest absolute value):

=IF(MIN(A1:D1)>0,MAX(A1:D1),IF(AND(MIN(A1:D1)=0,MAX(A1:D1)=0),0,IF(MAX(A1:D1)<=0,MAX(A1:D1))))

I attached an example sheet as well...

[mahju]

Hi
Try this

Please Login or Register  to view this content.

Regards