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!

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

Jobs

Need help with a multiple "if" statement in Excel

Need help with a multiple "if" statement in Excel

(OP)
I am making an Excel spread sheet with various dynamic balance trivia. One such bit is determining the tolerance under various balance standards.
The MIL-STD-167-1 (rarely used) has 3 speed ranges and I would like to have a single formula that would calculate the correct tolerance based on the speed entered.
From 0 to 150 RPM the tolerance is found by multiplying 0.177 x weight
From 150 to 1000 the tolerance is 4000 x weight divided by the speed squared
Above 1000 the tolerance is 4 x weight divided by the speed (this is the same as the API standard)
cell A1 weight (in pounds)
cell A2 speed (RPM)
cell A3 ==IF(A1<151,0.177*A2)

How can I add the other two conditions? Would it be better to start from the high end?

Thank you for your time.

RE: Need help with a multiple "if" statement in Excel

=IF(A1<=150, 0.177*A2, IF(A1<=1000, 4*A2/(A1^2), 4*A2/A1))

Assuming A1 = speed and A2 = weight

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Need help with a multiple "if" statement in Excel

Change the IDS's first "4*A2" to "4000*A2".
(And note that he has assumed the uses of cells A1 and A2 to be as you actually used them in your attempted formula, not as you stated them to be in the text of your post.)

RE: Need help with a multiple "if" statement in Excel

(OP)
Thanks.. exactly what I wanted.thumbsup2

RE: Need help with a multiple "if" statement in Excel

Your problem is solved above, good suggestions.

I find myself needing similar nested strucutre often. Let's say you have a group of columns of data and you want to add another column which is a spline fit on the same independent variable broken into 10 intervals. You'd need a heckuva lot of nested ifs.

A simpler solution for me is to use "case" or "switch" statement - programmed in vba to be useable directly in spreadsheet formulas:
thread770-281108: switch or case statement for excel spreadsheet

=====================================
(2B)+(2B)' ?

RE: Need help with a multiple "if" statement in Excel

Cool.

How do you get the color-coding / indenting / fonts ?
Is it some special programming editor?

One thing I am missing since I upgraded to excel 2013 on my windows 7 64 bit machine... vba auto-indenter program doesn't work.

=====================================
(2B)+(2B)' ?

RE: Need help with a multiple "if" statement in Excel

Hi Pete,

The colour coding is a Wordpress thing: add [sourcecode language="vb"] ... [/sourcecode] around the code and it colours it for you.

My smart-indenter add-in still works in Excel 2013. I'm using:
Smart Indenterv3.4
Rebuilds standard VBA code indenting
© 1998-2004 by Office Automation Ltd

Check http://www.oaltd.co.uk for the latest version

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Need help with a multiple "if" statement in Excel

Thanks Doug,

I just tried it out:
1 - I installed IndenterVBA.exe.
2 - It showed up on Add-in Manager
3 - When I try to enable it in the add-in manager, I get an error message saying the add-in cannot be loaded (not further details)

The developer page you linked says it works for excel 2003

This page says it works thru 2013
http://www.add-ins.com/macro-products-for-Microsof...

This page says it works with 32 bit, not 64 bit.
http://www.vbaexpress.com/forum/showthread.php?457...

I have 64 bit Windows 7.
Do you have 64 bit or 32 bit?

=====================================
(2B)+(2B)' ?

RE: Need help with a multiple "if" statement in Excel

Quote:

This page says it works with 32 bit, not 64 bit.

That'll be the problem.

I have 64 bit Windows but 32 bit Office. There are some significant differences with 64 bit VBA code that interacts with the Windows API.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

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


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close