×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Max of mixed signs

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

Larry

RE: Max of mixed signs

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 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

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.

RE: Max of mixed signs

(OP)
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

RE: Max of mixed signs

You might consider
=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

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.

RE: Max of mixed signs

(OP)
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

RE: Max of mixed signs

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources