+ Reply to Thread
Results 1 to 8 of 8

Excel formula to find sum of difference

  1. #1
    Forum Contributor
    Join Date
    08-18-2017
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    308

    Excel formula to find sum of difference

    hi excelers,
    is it possible to use one single function or small formula? to find the ABS sum of differences between all values in a array.
    example:
    1
    2
    3
    __
    =2

    cuz:
    1-2 = ABS 1
    2-3 = ABS 1
    SUM = 2

    any help would be gratful thx

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Excel formula to find sum of difference

    Something like

    =SUM(ABS(A1:A2-A2:A3))

    Array confirmed with Shift Ctrl Enter.

    edit:-

    From your example, I assumed that you meant SUM of ABS differences and not, as you stated, ABS SUM of differences.

  3. #3
    Forum Contributor
    Join Date
    08-18-2017
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Excel formula to find sum of difference

    thx jason, that works nicely
    one last thing is it possible to fit a INDEX into your formula as so I can change the size of the array windows to calculate?

    sum thing like this: =SUM(INDEX(A3:A30000-A4:A30000,Period))
    Last edited by QuantEdge; 09-22-2018 at 11:00 AM.

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Excel formula to find sum of difference

    Try

    =SUM(ABS(A1:INDEX(A1:A200,B1)-A2:INDEX(A2:A201,B1)))

    Where B2 refers to number of rows.

    Following up on the comment I made in my previous reply about SUM ABS not ABS SUM, in case you missed the edit, or didn't realise there is a difference. What would the correct result be with an array of 5,3,7?

    SUM ABS = 6 but ABS SUM = 2

  5. #5
    Forum Contributor
    Join Date
    08-18-2017
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Excel formula to find sum of difference

    sorry it SUM ABS = 6

  6. #6
    Forum Contributor
    Join Date
    08-18-2017
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Excel formula to find sum of difference

    thx again Jason,
    that solution works very well and save a lot of time) star for u
    is it possible to make your formula a little shorter or simpler for massive calculating....! or if anyone out there no a shorter one)

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Excel formula to find sum of difference

    That is the simplest one you will get for the task.

    If you're going to have multiple formulas doing the same calculation over different portions of the array then a helper column would probably be beneficial, but not for a single formula.

  8. #8
    Forum Contributor
    Join Date
    08-18-2017
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Excel formula to find sum of difference

    thx noted...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula to find difference between two values
    By xcentriq in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2017, 11:22 AM
  2. Replies: 2
    Last Post: 02-09-2016, 08:24 AM
  3. [SOLVED] Formula to find max value difference
    By bigband1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-02-2015, 08:29 AM
  4. Formula to find difference with changing initial number?
    By klm1312 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-22-2014, 09:37 PM
  5. Replies: 1
    Last Post: 05-21-2014, 04:37 PM
  6. Replies: 2
    Last Post: 05-15-2006, 02:35 AM
  7. how do i put formula to find difference between two dates
    By A.D in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-12-2005, 01:06 PM

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