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
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
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.
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.
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
sorry it SUM ABS = 6
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)
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.
thx noted...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks