+ Reply to Thread
Results 1 to 5 of 5

Sum of differences

  1. #1
    Registered User
    Join Date
    03-25-2011
    Location
    Glos, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Sum of differences

    Hello all,

    Excuse me if I am in breach of any etiquette by posting this here (I am somewhat new to this site) -let me know if I am.

    I have a question on summing absolute differences, I am looking for a slightly more elegant solution than the one I have currently

    =SUM(ABS(C$73-C$72),ABS(C$72-C$71),ABS(C$71-C$70),ABS(C$70-C$69),.....

    Please note, I'm not trying to sum absolute differences between each pair in the range, only differences between adjacent pairs

    Any help/guidance much appreciated!
    Thanks
    Last edited by Imprisoning; 03-25-2011 at 08:13 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Sum of differences

    Something like:

    =SUMPRODUCT(--(ABS(C70:C73-C69:C72)))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Sum of differences

    It would help if we knew the full range but assuming say C50:C73 then

    Please Login or Register  to view this content.
    above assumes no non-numerics in the range - adjust as necessary.

  4. #4
    Registered User
    Join Date
    03-25-2011
    Location
    Glos, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Sum of differences

    Thank you very much for this. It looks like it's working perfectly.

    May I ask what the double "--" is doing/

    Thanks also to DonkeyOte - I do need the range to be adjustable, which is why I left the question open-ended, but it seems you have both come up with similar solutions, so thanks for the help

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Sum of differences

    Actually, the -- is not needed here. It is usually used to coerce TRUE/FALSE results to 1s and 0s... It was an involuntary introduction that didn't change the result

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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