Maximum Absolute with sign
 

How can I create a user function that accomplishes this:

=IF(MAX(H2:H35)<ABS(MIN(H2:H35)),MIN(H2:H35),MAX(H 2:H35))


I want this function to let me simply enter

=MAXABS(h2:h35)

Thanks in advance,

Bram Weisma

--
Message posted from http://www.ExcelForum.com

Maximum Absolute with sign
 

The simplest way is to use exactly the same formula ..

Function MAXABS(rng As Range)

With WorksheetFunction
MAXABS = IIf(.Max(rng) < Abs(.Min(rng)), .Min(rng), .Max(rng))
End With

End Function

Although it's probably slightly more efficient to avoid calculating the Maximum and Minimum more than once ..

Function MAXABS(rng As Range)

Dim MaxVal, MinVal

MaxVal = WorksheetFunction.Max(rng)
MinVal = WorksheetFunction.Min(rng)

MAXABS = IIf(MaxVal < Abs(MinVal), MinVal, MaxVal)

End Function

Enjoy,
Tony

----- bramweisman wrote: -----

How can I create a user function that accomplishes this:

=IF(MAX(H2:H35)<ABS(MIN(H2:H35)),MIN(H2:H35),MAX(H 2:H35))


I want this function to let me simply enter

=MAXABS(h2:h35)

Thanks in advance,

Bram Weisman


---
Message posted from http://www.ExcelForum.com/

Maximum Absolute with sign
 

Tony, Well Done! Thanks a lot!

Bra

--
Message posted from http://www.ExcelForum.com