Greater than

C

contrarian

Hi all,

Let's say cells A1,A2 & A3 all have a number in them.

Do you know how to select the GREATEST of the three? I don't seem t
be able to find the right formula.

Also......and here's the catch......those numbers can be positive O
negative, but I want the greatest number REGARDLESS.

example:

2.5 3.5 9.5

Obviously 9.5 is the correct choice. But.......

3.0 -7.5 4.5

The "greatest" number mathematically is 4.5, but I want it to ignor
negatives and pick 7.5!!!

There's definitely a way to do this, but I'm not sure how!

Cheer
 
K

Kevin H. Stecyk

contrarian wrote
Hi all,

Let's say cells A1,A2 & A3 all have a number in them.

Do you know how to select the GREATEST of the three? I don't seem to
be able to find the right formula.

Also......and here's the catch......those numbers can be positive OR
negative, but I want the greatest number REGARDLESS.

example:

2.5 3.5 9.5

Obviously 9.5 is the correct choice. But.......

3.0 -7.5 4.5

The "greatest" number mathematically is 4.5, but I want it to ignore
negatives and pick 7.5!!!

There's definitely a way to do this, but I'm not sure how!

Cheers
Click to expand...

Hi,

Here's one way....

=INDEX(A1:C1,1,MATCH(MAX(ABS(A1:C1)),ABS(A1:C1),0))

Enter this as an array formula - control shift enter.

{=INDEX(A1:C1,1,MATCH(MAX(ABS(A1:C1)),ABS(A1:C1),0))}

It will look like that when you are done. Using this formula, you will
get -7.5 in your second example. If you want Positive 7.5, then

=ABS(INDEX(A1:C1,1,MATCH(MAX(ABS(A1:C1)),ABS(A1:C1),0)))

Enter this as an array formula - control shift enter to get

{=ABS(INDEX(A1:C1,1,MATCH(MAX(ABS(A1:C1)),ABS(A1:C1),0)))}

To be honest, I am not sure why an array entered formula is necessary for it
to work, but it does work. Perhaps someone else will provide an explanation
or a better solution.

Effectively, you are using the match function to FIND the largest value, and
then using the Index function to pull it up.

Hope that helps.

Best regards,
Kevin
 
B

beeawwb

Entered as an Array (Ctrl-Shift-Enter) this worked for me.

=MAX(ABS(A1:A3))

Hope that helps,

-Bo
 
K

Kevin H. Stecyk

Ooops, rather than using A1:A3, I used A1:C1. I am sure you can adapt the
formulas.
 
C

contrarian

Thanks much Bob!

Does the trick, although I find I have to do the Absolute value
separatley for each sell first, THEN do the MAX thing.

It just doesn't seem to work as one formula!!!

Cheers
Steve
 
B

beeawwb

It will work as 1 formula, you just have to enter it as an arra
formula.

Go to the cell you want to show the max value.

Type =MAX(ABS(A1:A3)) BUT DON'T HIT ENTER

Push and hold Ctrl-Shift, then you can push enter.

If you did it right, the whole formula will be enclosed in { }

{=MAX(ABS(A1:A3))}

And it will show up the way you need.

-Bo
 
B

bj

if the data were in cells A1:A3
enter =max(abs(A1:A3))


Depending on the vertsion of EXCEL you have You may have
to enter it as a array or just hit enter. On EXCEL 2002
just hit enter.
 
Top