Max of mixed signs 1

[TangoCleveland]

I have a column of forces from piping stress analysis. I want to find the greatest magnitude and retain the sign. If I have a -20000 lb force and a 10000 lb force, the MAX function will tell me 10000. There are 7 forces altogether, and I'm now using a formula =IF(MAX(ABS(C155),ABS(C156),ABS(C157),ABS(C158),ABS(C159),ABS(C160),ABS(C161))<>MAX(C155:C161),MIN(C155:C161),MAX(C155:C161))

Is there an easier way?

Here are two columns of data to work with:

-29402 80284
49602 -6317
-301 48506
2862 49852
53 49339
-29101 31779
49903 -54823


Larry

 

[zappedagain]

Here is a slight variation you can use with the MIN function:

=IF(ABS(MIN(A1:A2))>MAX(A1:A2),MIN(A1:A2),MAX(A1:A2))

here my data is in A1:A2, your data appears to be in C15561.

Here is a simpler version in three cells

A3=min(A1:A2) find out the largest negative (or lowest) number
A4=max(A1:A2) find out the largest positive number

if(abs(A3)>A4, A3, A4) now compare the magnitudes and take the larger.

 

[electricpete]

I am not sure exactly what is the comparision you want to make (why are there two columns).

Something that will accomplish the same thing as your equation without having to evaluate a separate expression for each cell in the range is:
IF(MAX(C155:C161)<-1*MIN(C155:151),MIN(C155:C161),MAX(C155:C161))

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[TangoCleveland]

pete,
I have several columns of data, want to evaluate each column separately. I didn't word my original question correctly.
Anyway, thanks, guys. Your formulas are shorter.

Larry

 

[joerd]

You might consider
[tt]=MAX(ABS(C155:C161))[/tt]
where you enter the formula as an array formula (instead of <enter> press CTRL-<enter> when you have typed it in). The formula bar should show curly braces {} around the formula.

Cheers,
Joerd

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

 

[electricpete]

Yes that is smaller and more elegant.
Ctrl-Shift-Enter to get the array formula

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[TangoCleveland]

Elegant coding - that's an expression I haven't heard since my Fortran II days!

The array formula works, but loses the sign of the result. I still need some kind of IF to retain the sign.

Larry

 

[joerd]

Thank you pete - it's one of those combos I type without thinking.

Cheers,
Joerd

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