# How to find the largest change of values in a column

1. ## 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.

2. ## Re: 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" <Ravager@discussions.microsoft.com> wrote in message
news:1E778042-425E-4123-B4A0-4A42630E2F61@microsoft.com...
>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.
>

3. ## Re: 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" <Ravager@discussions.microsoft.com> 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)

4. ## Re: 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:A3))

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" <Ravager@discussions.microsoft.com> 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)
>

5. ## Re: 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" <Ravager@discussions.microsoft.com> wrote in message
> news:1E778042-425E-4123-B4A0-4A42630E2F61@microsoft.com...
> >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.
> >

>
>
>

6. ## Re: 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:A3))
>
> 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" <Ravager@discussions.microsoft.com> 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)
> >

>
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1