get max or min number

[Guest]

i have these numbers
-3
2
1

i want the maximum absolute number but i want to retain the sign afterwards.
So i put =MAX(ABS(G81:I81))
but that returns 3 and not -3
how do i tell the formula to also return the correct sign of the maximum
absolute number in a range of numbers?

 

[Don Guillett]

try this array entered formula
=MAX(IF(ABS(E2:E4)>0,ABS(E2:E4)))

 

[Peo Sjoblom]

One way

=INDEX(G81:I81,MATCH(MAX(ABS(G81:I81)),ABS(G81:I81),0))

entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom

 

[Pete_UK]

Here's one way:

=IF(MAX(G81:I81)<MAX(ABS(G81:I81)),-1,1)*MAX(ABS(G81:I81))

Hope this helps.

Pete

 

[Guest]

Array entered Ctrl + Shift + Enter

=IF(MAX(C1:C6)=MAX(ABS(C1:C6)),MAX(C1:C6),MAX(ABS(C1:C6))*-1)

Regards
Peter

 

[Rick Rothstein \(MVP - VB\)]

Array enter (Ctrl+Shift+Enter) this formula...

=SIGN(G81:I81)*MAX(ABS(G81:I81))

Rick

 

[Rick Rothstein \(MVP - VB\)]

Array enter (Ctrl+Shift+Enter) this formula...

=SIGN(G81:I81)*MAX(ABS(G81:I81))

Never mind... it doesn't work... my sample data was fooling me.

Rick

 

[Rick Rothstein \(MVP - VB\)]

If you want a non-array entered solution, this formula will do that...

=SIGN(ABS(MAX(G81:I81))-ABS(MIN(G81:I81)))*(ABS(MAX(G81:I81))+ABS(MIN(G81:I81))+ABS(ABS(MAX(G81:I81))-ABS(MIN(G81:I81))))/2

Rick

 

[Rick Rothstein \(MVP - VB\)]

If you want a non-array entered solution, this formula will do that...

=SIGN(ABS(MAX(G81:I81))-ABS(MIN(G81:I81)))*(ABS(MAX(G81:I81))+ABS(MIN(G81:I81))+ABS(ABS(MAX(G81:I81))-ABS(MIN(G81:I81))))/2

Rick

And, of course, it returns a wrong answer if the largest and smallest values
have the same absolute magnitude. We can patch it like this, I guess...

=SIGN(0.000000000000001+ABS(MAX(A1:A3))-ABS(MIN(A1:A3)))*(ABS(MAX(A1:A3))+ABS(MIN(A1:A3))+ABS(ABS(MAX(A1:A3))-ABS(MIN(A1:A3))))/2

Depending on the size of the numbers being compared, we might be able to
make that constant shorter (for example, if the values being compared are
integers, then the constant could be 0.1). The above formula favors the
positive value when the extremes have the same magnitude.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Okay, ignore all my other attempts... even though the last modification I
posted will work, there is a much simpler and more straightforward formula
to do what you want. This non-array, normally entered formula should do the
trick...

=IF(ABS(MIN(G81:I81))>ABS(MAX(G81:I81)),MIN(G81:I81),MAX(G81:I81))

(See what a little rest period can do for the thinking processes.<g>)

As with my other formula, this one returns the positive value if there is a
tie in magnitude between the largest negative and positive values.

Rick