Maximum Absolute with sign

[bramweisman]

How can I create a user function that accomplishes this:

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


I want this function to let me simply enter

=MAXABS(h2:h35)

Thanks in advance,

Bram Weisma

 

[Tony Jollans]

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(H2:H35))


I want this function to let me simply enter

=MAXABS(h2:h35)

Thanks in advance,

Bram Weisman

 

[bramweisman]

Tony, Well Done! Thanks a lot!

Bra