Absolute value sum???

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Absolute value sum???

  1. #1
    Board Regular
    Join Date
    Nov 2003
    Location
    South Jersey
    Posts
    114
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    114
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    81,447
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    81,447
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    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.

User Tag List

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