+ Reply to Thread
Results 1 to 6 of 6

Determining total variance between positive and negative numbers

  1. #1
    Forum Contributor
    Join Date
    06-22-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    152

    Determining total variance between positive and negative numbers

    Hi, Can anyone point me in the right direction please?

    I have a column of data that lists the variance between scheduled time for a task and actual time taken. I need a summary cell that gives the total variance for all cells regardless of whether it was over or below schedule. for example, in the column below, I need a formula that will return 8.29 as the difference between scheduled and actual time over the 6 days:
    -0.38
    0.61
    -2.87
    3.45
    -0.90
    -0.08

    thank you!
    Last edited by greyscale; 11-17-2010 at 09:05 PM.

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Determining total variance between positive and negative numbers

    Use:

    Please Login or Register  to view this content.
    entered with Ctrl-Shift-Enter.

    Cheers,
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Forum Contributor
    Join Date
    06-22-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    152

    Re: Determining total variance between positive and negative numbers

    Wow! I had no idea about ABS or CTRL+SHIFT+ENTER. Thanks so much. I have an additional challenge for you. That works beautifully if the data is in adjacent rows, however, due to formatting I have to retain, there are merged cells in between the rows. My formula would be something like
    {=SUM(ABS(A1:A6,A8-A12,A14-A20))} and it doesn't seem to like it. Is there a way to make it work?

  4. #4
    Registered User
    Join Date
    03-03-2010
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: Determining total variance between positive and negative numbers

    Try breaking up the ABS for each array:

    =SUM(ABS(A1:A6),ABS(A8:A12),ABS(A14:A20))

    Then, CTRL+SHIFT+ENTER

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Determining total variance between positive and negative numbers

    maybe
    {=SUM(ABS(CHOOSE({1,2,3},A1:A2,A7:A8,A12:A13)))} as an aside

    =SUMPRODUCT(ABS(A1:A6)) would have worked
    so would
    =SUM(SUMPRODUCT(ABS(A1:A2)),SUMPRODUCT(ABS(A3:A4)),SUMPRODUCT(ABS(A5:A6)))
    or even
    =SUMPRODUCT(ABS(A1:A2)+ABS(A3:A4)+ABS(A5:A6))
    Last edited by martindwilson; 11-17-2010 at 08:44 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Forum Contributor
    Join Date
    06-22-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    152

    Re: Determining total variance between positive and negative numbers

    Perfect! Thank you!

+ 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