Need help with a multiple "if" statement in Excel

[Ralph2]

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.

 

[IDS]

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

 

[Denial]

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

 

[Ralph2]

Thanks.. exactly what I wanted.

 

[electricpete]

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

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

 

[IDS]

A blog post about the switch function:

http://newtonexcelbach.wordpress.com/2012/01/27/the-switch-function-vba-and-udf/

based on electricpete's code.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

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)' ?

 

[IDS]

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/

 

[electricpete]

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-produc...to-indent-vba-code/how-to-indent-vba-code.htm

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

http://www.vbaexpress.com/forum/showthread.php?45727-VBA-Code-Indent

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

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

 

[IDS]

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/