# Excel formula to find sum of difference

1. ## 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. ## 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. ## 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))

4. ## 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. ## Re: Excel formula to find sum of difference

sorry it SUM ABS = 6

6. ## 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. ## 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. ## Re: Excel formula to find sum of difference

thx noted...

##### Users Browsing this Thread

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

#### 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