×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

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

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

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

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

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

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:

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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!