+ Reply to Thread
Results 1 to 6 of 6

How to find the largest change of values in a column

  1. #1
    Ravager
    Guest

    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. #2
    Bernie Deitrick
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    >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. #3
    Harlan Grove
    Guest

    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" <[email protected]> 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. #4
    Bernie Deitrick
    Guest

    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" <[email protected]> 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. #5
    Ravager
    Guest

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > >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. #6
    Ravager
    Guest

    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" <[email protected]> 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)
    > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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