How to find the largest change of values in a column
 

I need to know if there is a way to find the largest change in a single
column of numbers, even if they are negative values.

EXAMPLE:
Column A1:A3 contains the following numbers
135.67
23.45
-256.77
I need to show the returned information in Cell B6.
Every function I use returns 135.67 as the largest but I need to return the
-256.77, (and show the negative sign). This is a very simple example but I
hope someone can help.

How to find the largest change of values in a column
 

Ravager,

=MAX(A1:A3,ABS(MIN(A1:A3)))

or array enter (use Ctrl-Shift-Enter)

=MAX(ABS(A1:A3))


HTH,
Bernie
MS Excel MVP


"Ravager" wrote in message
...
I need to know if there is a way to find the largest change in a single
column of numbers, even if they are negative values.

EXAMPLE:
Column A1:A3 contains the following numbers
135.67
23.45
-256.77
I need to show the returned information in Cell B6.
Every function I use returns 135.67 as the largest but I need to return the
-256.77, (and show the negative sign). This is a very simple example but I
hope someone can help.

How to find the largest change of values in a column
 

Bernie Deitrick wrote...
Ravager,

=MAX(A1:A3,ABS(MIN(A1:A3)))

or array enter (use Ctrl-Shift-Enter)

=MAX(ABS(A1:A3))
....

OP stated he needed the sign for negative results. Your formulas only
return absolute values, so no signs.

"Ravager" wrote in message
I need to know if there is a way to find the largest change in a single
column of numbers, even if they are negative values.
....
Every function I use returns 135.67 as the largest but I need to return the
-256.77, (and show the negative sign). This is a very simple example but I
....

Try

=LOOKUP(2,1/(A1:A3^2=MAX(A1:A3^2)),A1:A3)

How to find the largest change of values in a column
 

Thanks, Harlan, I zoned on that.

=IF(MAX(A1:A3)ABS(MIN(A1:A3)),MAX(A1:A3),MIN(A1:A 3))

might be simpler for a novice to understand / maintain.

Bernie
MS Excel MVP


OP stated he needed the sign for negative results. Your formulas only
return absolute values, so no signs.

"Ravager" wrote in message
I need to know if there is a way to find the largest change in a single
column of numbers, even if they are negative values.
...
Every function I use returns 135.67 as the largest but I need to return the
-256.77, (and show the negative sign). This is a very simple example but I
...

Try

=LOOKUP(2,1/(A1:A3^2=MAX(A1:A3^2)),A1:A3)

How to find the largest change of values in a column
 

Bernie Thank You for the quick reply.

I had already tried a formula like that. It does find the largest value but
doesn't return the negative sign as part of the answer. I need to show that
the biggest change I had was a "Negative Value" - a loss.
I am sure it is because of the ABS function.
Thanks again

"Bernie Deitrick" wrote:

Ravager,

=MAX(A1:A3,ABS(MIN(A1:A3)))

or array enter (use Ctrl-Shift-Enter)

=MAX(ABS(A1:A3))


HTH,
Bernie
MS Excel MVP


"Ravager" wrote in message
...
I need to know if there is a way to find the largest change in a single
column of numbers, even if they are negative values.

EXAMPLE:
Column A1:A3 contains the following numbers
135.67
23.45
-256.77
I need to show the returned information in Cell B6.
Every function I use returns 135.67 as the largest but I need to return the
-256.77, (and show the negative sign). This is a very simple example but I
hope someone can help.

How to find the largest change of values in a column
 

Thanks Harlan and Bernie.

Both formulas worked perfectly. I can't believe how fast you were to answer
and solve my problem. I have stared and experimented on
this spreadsheet for 2 hours trying to get the correct answer and you guys
did it in 5 minutes.....gee

"Bernie Deitrick" wrote:

Thanks, Harlan, I zoned on that.

=IF(MAX(A1:A3)ABS(MIN(A1:A3)),MAX(A1:A3),MIN(A1:A 3))

might be simpler for a novice to understand / maintain.

Bernie
MS Excel MVP


OP stated he needed the sign for negative results. Your formulas only
return absolute values, so no signs.

"Ravager" wrote in message
I need to know if there is a way to find the largest change in a single
column of numbers, even if they are negative values.
...
Every function I use returns 135.67 as the largest but I need to return the
-256.77, (and show the negative sign). This is a very simple example but I
...

Try

=LOOKUP(2,1/(A1:A3^2=MAX(A1:A3^2)),A1:A3)