Two MaxAbs functions

Excel does not have a built-in function to find the maximum absolute value of a range, perhaps because the Max() and Abs() functions can be combined in an array function:

  • =Max(Abs(datarange))

This solution has a number of drawbacks however:

  • The function must be entered as an array function, by pressing Ctrl-Shift-Enter, rather than just enter.
  • If it is entered with just pressing the Enter key it displays the wrong value, rather than an error message.
  • Even if it is entered correctly, if anyone presses F2 then enter it will revert to a normal function, and display the wrong result.
  • It is not available from VBA.

For all these reasons I decided to write a MaxAbs function in VBA, that can be called either from the worksheet, or from another VBA routine.  Here is the code, Version 1:

Function MaxAbs(Dataa As Variant) As Double
Dim MaxVal As Double, Val As Variant

    If TypeName(Dataa) = "Range" Then Dataa = Dataa.Value2

    For Each Val In Dataa
        If Abs(Val) > MaxVal Then MaxVal = Abs(Val)
    Next Val

    MaxAbs = MaxVal

End Function

Having done that, I wondered if calling the built-in Max function might work better, particularly for big data ranges. My first effort was:

Function MaxAbs2(Dataa As Variant) As Double
Dim MaxVal1 As Double, MaxVal2 As Double

    If TypeName(Dataa) = "Range" Then Dataa = Dataa.Value2

    MaxVal1 = WorksheetFunction.Max(Dataa)
    MaxVal2 = -WorksheetFunction.Min(Dataa)

    If MaxVal1 > MaxVal2 Then MaxAbs2 = MaxVal1 Else MaxAbs2 = MaxVal2

End Function

This proved to be slower than the first version, even for very big data ranges, but working with ranges rather than variant arrays:

Function MaxAbsR(Dataa As Range) As Double
Dim MaxVal1 As Double, MaxVal2 As Double
    MaxVal1 = WorksheetFunction.Max(Dataa)
    MaxVal2 = -WorksheetFunction.Min(Dataa)

    If MaxVal1 > MaxVal2 Then MaxAbsR = MaxVal1 Else MaxAbsR = MaxVal2

End Function

made the code faster than the original version for anything more than about 15 rows. The drawback with this version is that if you are working with double or variant arrays in VBA (which I usually am), these would need to be converted to range objects first, so I ended up with the two versions:

  • MaxAbs() for use with arrays in VBA
  • MaxAbsR() for use as a UDF on the spreadsheet, or on range objects in VBA

The first function can also be easily adapted to provide the minimum absolute value:

Function MinAbs(Dataa As Variant) As Double
Dim MinVal As Double, Val As Variant

    If TypeName(Dataa) = "Range" Then Dataa = Dataa.Value2
    MinVal = 1E+308
    For Each Val In Dataa
        If Abs(Val) < MinVal Then MinVal = Abs(Val)
    Next Val

    MinAbs = MinVal

End Function

Other than changing max to min, and > to <, the only difference is that MinVal is set to a very large number before starting the loop; otherwise it would always return zero.

Advertisements
This entry was posted in Arrays, Excel, Maths, Newton, UDFs, VBA and tagged , , , , . Bookmark the permalink.

9 Responses to Two MaxAbs functions

  1. james says:

    Doug

    Nice work … can this be adapted to find Min(Abs() as this would find closest match?

    Cheers
    James

    Like

  2. james says:

    Doug

    Thanks, I had a go at converting MaxAbsR to MinAbsR …. see below ….but failed miserably … can you point out error of may ways?

    Cheers
    James

    Function MinAbsR(Dataa As Range) As Double
    Dim MinVal1 As Double, MinVal2 As Double

    MinVal1 = WorksheetFunction.Min(Dataa)
    MinVal2 = -WorksheetFunction.Max(Dataa)

    If MinVal1 < MinVal2 Then MinAbsR = MinVal1 Else MinAbsR = MinVal2

    End Function

    Like

    • dougaj4 says:

      That function will return the maximum absolute value as a negative number.

      I can’t see a way to return the smallest absolute value without stepping through the data because you can’t apply the Abs() function to a range in VBA. You would have to apply Abs() to each individual value, in which case the MinAbs version as written should be quicker.

      Like

  3. Lori says:

    that’s interesting, i wouldn’t have thought there would be much difference in performance between range and variant versions.

    in relation to drawbacks of using MAX(ABS(A1:B2)), here are two other options not requiring array-entry and two short vba alternatives based on these. (Note: using Application. without WorksheetFunction. allows for arrays but some functions like ABS() are missing – this may be because there is a vba equivalent function.)


    =MAX(-MIN(A1:B2),MAX(A1:B2))
    =MAX(INDEX(ABS(A1:B2),0))

    Function maxabs(Data)
    With WorksheetFunction
    maxabs = .Max(-.Min(Data), .Max(Data))
    End With
    End Function
    Function maxabs2(Data)
    With Application
    maxabs2 = .Max(.ImAbs(.Transpose(Data)))
    End With
    End Function

    Like

  4. Lori says:

    Looking at the posted code again, i think the difference is mainly due to the line:
    If TypeName(Dataa) = "Range" Then Dataa = Dataa.Value2
    which i’m not sure is required in MaxAbs2. Changing Range to Variant in the MaxAbsR function should allow either case (range or array) and doesn’t seem to noticeably change performance.

    Like

    • dougaj4 says:

      Thanks for the comments Lori. Regarding the array vs. range question, if you are working on data in VBA it is usually much quicker to use a variant array than a range. I’m usually doing that, so I tend to convert to an array automatically. The exception is using worksheetfunctions on more than a small number of cells. In that case (as above) it is better to work with a range.

      Like

  5. Pingback: Excel Roundup 20140127 | Contextures Blog

  6. Deepak Suwalka says:

    It’s a nice post about absolute maximum and minimum. I really like it. It’s really helpful. Thanks for sharing it.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s