Results 1 to 6 of 6

Absolute value sum???

This is a discussion on Absolute value sum??? within the Excel Questions forums, part of the Question Forums category; I create a monthly report that usually has 6 columns of data. They are as follows: Month Actual, Month Budget, ...

  1. #1
    Board Regular
    Join Date
    Nov 2003
    Location
    South Jersey
    Posts
    112

    Default Absolute value sum???

    I create a monthly report that usually has 6 columns of data. They are as follows: Month Actual, Month Budget, Month Variance, Year Actual, Year Budget, Year Variance. My goal is to eliminate any row who has a 0 balances across. Currently I reference the cells to the right in other columns and take their absolute values. Then I sum those lines. Any sum with a value of 0 can consequently be deleted.

    The reason I have to use absolute values to sum a row instead of just taking the sum is as follows. Say a particular row has no month activity, so all 3 columns related to the month are 0. But the year actual has a balance of $100 and the year budget has a balance of 0. Well the year variance would be -$100 and a sum of the line would net a 0 balance. Therefore if I take the absolute values I ensure myself that I will not delete any rows that contain activity.

    If anyone knows a function or how to right this into a macro I would be much appreciative. I think I currently have the problem solved by using Access with setting up each "Or:" criteria as <>0. However I would like to know a way to do this in excel. Thanks.

  2. #2
    MrExcel MVP TommyGun's Avatar
    Join Date
    Dec 2002
    Location
    Clear Lake, TX
    Posts
    4,202

    Default Re: Absolute value sum???

    Assuming that your values are in columes A thru F, you could use this formula...
    Code:
    =SUM(ABS(A1:F1))
    NOTE: This muse be entered as an array formula by using CTRL+SHIFT+ENTER.

  3. #3
    Board Regular
    Join Date
    Nov 2003
    Location
    South Jersey
    Posts
    112

    Default Re: Absolute value sum???

    Quote Originally Posted by TommyGun
    Assuming that your values are in columes A thru F, you could use this formula...
    Code:
    =SUM(ABS(A1:F1))
    NOTE: This muse be entered as an array formula by using CTRL+SHIFT+ENTER.

    At what point do I do the ctrl+shift+enter command? Sorry I'm a bit of a noob.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,907

    Default Re: Absolute value sum???

    Quote Originally Posted by smashclash
    Quote Originally Posted by TommyGun
    Assuming that your values are in columes A thru F, you could use this formula...
    Code:
    =SUM(ABS(A1:F1))
    NOTE: This muse be entered as an array formula by using CTRL+SHIFT+ENTER.

    At what point do I do the ctrl+shift+enter command? Sorry I'm a bit of a noob.
    Type the formula then confirm it with control+shift+enter instead of just with enter. Also, the following with just enter:

    =SUMPRODUCT(ABS(A1:F1))

  5. #5
    New Member
    Join Date
    Jan 2014
    Posts
    1

    Default Re: Absolute value sum???

    Know this is old however I would approach this way:

    =SUMIF(Range,">0")-sumif(Range"<0")

    No need for CSE

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,907

    Default Re: Absolute value sum???

    Quote Originally Posted by IronOrchid View Post
    Know this is old however I would approach this way:

    =SUMIF(Range,">0")-sumif(Range"<0")

    No need for CSE
    Good idea. There is a typo:

    =SUMIF(Range,">0")-sumif(Range,"<0")
    Assuming too much and qualifying too much are two faces of the same problem.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com