ABSSum(TheRange)

ABSSum is a function within ddGlobal and forms part of the Func_Numeric suite of macros.  ABSSum, or “absolute sum” will convert any negative number into a positive number and add that.  I wrote this function over a decade ago and I can no longer remember what the original purpose of it was.  It was most likely to solve some puzzle, hack some encryption or might have been just because I was bored.  The other reason which is most likely the more plausible one and the one that I currently use this formula for is to determine the absolute delta between two values.

For instance, assume you have two columns, say budget and actual, assume the totals of the two columns is exactly the same, but that does not mean all your individual rows are the same.  You may have under budget an item with R100 and over budgeted another with a R100, your budgeting skills is as such 100% accurate, in fact, you are out with R200.  (Good luck convincing your Financial Director on that one!)

So yes, the above might not have been the original reason for writing this Macro, but as I include only Macros in ddGlobal which I think may be used in other scenarios than the one it was originally written for, this one can made it into the suite and managed to hang in there ever since.

The following sample demonstrate the differences between Sum and AbsSum.

  Scenario 1 Scenario 2 Scenario 3
Value: A 100 -50 10
Value: B -50 -30 20
Value: C 30 80 30
Sum(A:C) 80 0 60
AbsSum(A:B) 180 160 60

the above formula reads:

+ABSSum(B2:B4)

Using just Excel functions, you could in theory achieve the same results using Arrays and the following formula:

{=+SUM(ABS(B2:B4))}

I found a lot of people are uncomfortable with arrays, so if you are new to arrays, you need to type the above formula and then press CTRL+SHFT+ENTER.

The ABSSum macro looks like this:

Function ABSSum(TheRange As Range) As Double
'This Function calculates the sum of the absolute values of TheRange.
'Written by Denis Dell
Dim i, j As Integer
Dim Total As Double
    With TheRange
        For i = 1 To .Rows.Count
            For j = 1 To .Columns.Count
                Total = Total + Abs(.Cells(i, j).Value)
            Next j
        Next i
    End With
ABSSum = Total
End Function

Straight forward macro that works, however I would be hesitant to use this macro on very large calculations as I don’t think it is fully optimized.  Let me correct that, I don’t THINK it is not optimized, I KNOW it is not optimized.  Then again, if I can not even remember why I originally wrote it, I suppose there is limited need to optimize it now.  If you need to run this on a couple of thousand cells, consider using either the array formula above or ABS your values separately and then just use the normal sum function.

This entry was posted in Articles, ddGlobal, Tips and tagged , . Bookmark the permalink.



Leave a Reply