Max absolute value comand?? 3

[SRO]

Is there a comand to get the Max absolute value for a group of numbers. ie

1, 6, -14, 7 = 14

 

[joerd]

Type

=MAX(ABS(A1:A4))

and press Ctrl+Enter (enters it as an array formula). The formula now looks like:

{=MAX(ABS(A1:A4))}

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[SRO]

joerd:

Thanks, but the problem is that the values arn't in the same column or row.

 

[jproj]

Just separate the values with comma's:

=MAX(ABS(A1),ABS(B4),ABS(E19),....)

jproj

 

[joerd]

Unfortunately, you cannot use =MAX(ABS(A1,D9,F7,E12)) and select the cells holding the Ctrl key, what you would normally do. I see nothing quicker than =MAX(ABS(A1),ABS(D9),ABS(F7),ABS(E12)) unless you venture into a VBA function, which will look something like:

Function MaxAbs(ParamArray R() As Variant)
Dim C As Variant, V As Double

    V = 0
    For Each C In R
        If Abs(C.Value) > V Then V = Abs(C.Value)
    Next C
    MaxAbs = V
End Function

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[melone]

Or make a table of the desired values....

 

[SRO]

Thanks for all the input, I was just hoping there would be a simple comand. Thanks.

 

[needahandle]

Try combining Min and Max;

=MAX(MAX(A1:A4),-MIN(A1:A4))

 

[bltseattle]

I like needahandle's solution. Nicely done!