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?

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Karish" wrote in message
...
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?

One way

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

entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom




"Karish" wrote in message
...
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?

Here's one way:

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

Hope this helps.

Pete

On Oct 24, 11:08 pm, Karish wrote:
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?

Array entered Ctrl + Shift + Enter

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

Regards
Peter

"Karish" wrote:

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?

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

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

Rick


"Karish" wrote in message
...
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?

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

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


"Karish" wrote in message
...
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?

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

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:I8 1),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


"Karish" wrote in message
...
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?