# 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  Register To Reply

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.  Register To Reply

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))  Register To Reply

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  Register To Reply

5. ## Re: Excel formula to find sum of difference

sorry it SUM ABS = 6  Register To Reply

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)  Register To Reply

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.  Register To Reply

8. ## Re: Excel formula to find sum of difference

thx noted...  Register To Reply