Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2001
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Absolute Values ABS in SUM (Excel 2000)

    How do I sum a group of cells when I want to use the absolute value of each cell? SUM(ABS(A1:A3)) doesn't seem to work. I can use ABS(A1) + ABS(A2) + ABS(A3)... but that gets tedious for large sets.

    For example: A1 = 2, A2=4, A3=-5
    I want the sum to be 11, not 1.

    Thanks,
    Pete

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Absolute Values ABS in SUM (Excel 2000)

    Hi Peter,

    If you will enter the formula as an array formula using CTRL-SHIFT-ENTER instead of just ENTER, I think your formula do as you expect.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Absolute Values ABS in SUM (Excel 2000)

    Also:

    =SUMPRODUCT(ABS(A1:A3))

    A1:A3 must not have any text value or formula generated "".
    Microsoft MVP - Excel

Posting Permissions

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