Maximum Absolute with sign

B

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
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top