Creating a Sign Max Function in Excel

[Mike Weiler]

I am wondering how to create a formula in excel that will look at a
range of cells with positive and negative values and return the
largest magnitude while still keeping the sign. Here is an example of
what I am looking for....

500, 700, 900, -300, -500, -1100 ....... I want it to return -1100.

500, 700, 900, -300, -500, -100 ....... I want it to return 900.

Does anyone know how to accomplish this?

 

[joeu2004]

I am wondering how to create a formula in excel that will
look at a range of cells with positive and negative values
and return the largest magnitude while still keeping the sign.

Since you want to retain the sign:

=IF(ABS(MIN(A1:A10))<ABS(MAX(A1:A10)),MAX(A1:A10),MIN(A1:A10))

 

[Ron Rosenfeld]

I am wondering how to create a formula in excel that will look at a
range of cells with positive and negative values and return the
largest magnitude while still keeping the sign. Here is an example of
what I am looking for....

500, 700, 900, -300, -500, -1100 ....... I want it to return -1100.

500, 700, 900, -300, -500, -100 ....... I want it to return 900.

Does anyone know how to accomplish this?

Assuming your values are in A1:F1 then

This formula must be **array-entered**:

=INDEX(A1:F1,,MATCH(MAX(ABS(A1:F1)),ABS(A1:F1),0))
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

If the cells are in a vertical range, then you need to remove one comma:

=INDEX(A6:A11,MATCH(MAX(ABS(A6:A11)),ABS(A6:A11),0))