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.
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
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
(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
RE: Need help with a multiple "if" statement in Excel
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
http://newtonexcelbach.wordpress.com/2012/01/27/th...
based on electricpete's code.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Need help with a multiple "if" statement in Excel
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
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
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
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/