Max of mixed signs
Max of mixed signs
(OP)
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
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





RE: Max of mixed signs
=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 C155:D61.
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.
RE: Max of mixed signs
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.
RE: Max of mixed signs
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
RE: Max of mixed signs
=MAX(ABS(C155:C161))
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.
RE: Max of mixed signs
Ctrl-Shift-Enter to get the array formula
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Max of mixed signs
The array formula works, but loses the sign of the result. I still need some kind of IF to retain the sign.
Larry
RE: Max of mixed signs
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.