"IF" STATEMENTS ?????
"IF" STATEMENTS ?????
(OP)
Hi, Im not an experienced excel user. Im trying to set up an equation that will round up the baseplate thickness value in the cell to a value more practical.
i.e. when I get a thickness of .083inches, I would like the cell next to it to read "use a 1 inch base plate. Ive got this far, and it works for one condition.
I have : IF(C82<1,("use a 1inch baseplate")),
This is fine for all values less than 1, but I also want to add a function for other values, like:
ifC82>1, BUT <1.25, "USE A 1.25 INCH BASEPLATE",....and so on.
Has anyone got any ideas???
i.e. when I get a thickness of .083inches, I would like the cell next to it to read "use a 1 inch base plate. Ive got this far, and it works for one condition.
I have : IF(C82<1,("use a 1inch baseplate")),
This is fine for all values less than 1, but I also want to add a function for other values, like:
ifC82>1, BUT <1.25, "USE A 1.25 INCH BASEPLATE",....and so on.
Has anyone got any ideas???





RE: "IF" STATEMENTS ?????
Look up the AND function in the help files for more information. The OR function will come in handy as well.
RE: "IF" STATEMENTS ?????
That worked for two conditions. How would I do it for more than two conditions, i.e. C82<1.5, C82<1.75, C82<2
I looked up the AND and OR, but it seems that they only give for thwo conditions??
Anyone have any thoughts!!!
RE: "IF" STATEMENTS ?????
There are three ways of doing this I believe, the IF way is as follows:
=IF(A1<1,1,IF(A1<1.5,1.5,IF(A1<2,2,"NEW")))
replace "NEW" with IF(A1<2.5,2,5,IF(A1 etc.
However you can only have SEVEN IF statements.
Secondly,
Can you not use CEILING i.e.
in B1 enter =CEILING(A1,0.25) which will round up to the nearest 0.25".
You could also use a LOOKUP table where F1=0, F2=F1+0.2501
, copy this down as far as necessary. G1=0.25, G2=G1+0.25 copy this down as far as necessary.
B1=VLOOKUP(A1,F1:G5,2).
Adjust the table values as necessary and adjust the F1:G5 to the cells of the table, or name the table.
Hope this helps.
----------------------------------
maybe only a drafter
but the best user at this company!
RE: "IF" STATEMENTS ?????
If(AND(AND(c82>1,c82<1.25),AND(c82>1.1,c82<5))"true","false")
As mentioned, you can only have 7 levels of nesting, so try to simplify the problem. If you post more detailed info on what you are doing, we can help. I have done something similar a few years back (so the details are a little fuzzy) but I think I ended up using the lookup functions.
RE: "IF" STATEMENTS ?????
RE: "IF" STATEMENTS ?????
That vlookup is cool!!
I think for my particular application the "ceiling" comand is the perfect. I never heard of it!!
thanks again.
RE: "IF" STATEMENTS ?????
SO A2=IF(A1<1,1,IF(A1<1.5,1.5,IF(A1<2,2,"NEW")))
and A3=IF(A1<2.5,2.5,IF(A1<3.0,3.0,IF(A1<4,4,"NEW")))
Now in a third cell A4(your results cell):
A4=IF(A1<2,A1,A2)
RE: "IF" STATEMENTS ?????
RE: "IF" STATEMENTS ?????
=IF(I848>0.75,(TRUNC((I848-0.005)*4,0)+1)/4,MAX((TRUNC((I848-0.005)*8,0)+1)/8,0.5))
Here I848 was the calcualted cell. What I wanted to accomplish was base plates smaller than 0.75 round to the nearest 0.125 such as 0.5, 0.625 or 0.75, but never less than 0.5. Plates thicker than 0.75, I wanted in 0.25 increments 0.75, 1.0, 1.25 and so on. Also I put a small factor of 0.005 in the formula. I hated when I got an answer of 1.254 and jumped the plate thickness up to 1.375. This will result in an answer of 1.25.
BigTankMan
RE: "IF" STATEMENTS ?????
Thanks for your answers in advanced.
RE: "IF" STATEMENTS ?????
=IF(condition, do_if_condition_is_true, do_if_condition_is_false)
(in some non-english versions of excel the , should be replaced by ; )
So, if the value is in cell A1, and the formula to be executed is for example =10/A1, the if statement becomes:
=IF(A1=0, "cannot divide by zero", 10/A1)
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: "IF" STATEMENTS ?????
If plate thicknesses happened to be standard fractions of a number, then you could simply use a method of rounding or truncating to the nearest multiple. Alternatively, it may be possible to derive a formula to calculate the nearest standard size.
Failing this, a lookup table may be a solution, where a simple table structure contains a list of standard sizes.
RE: "IF" STATEMENTS ?????
yes, you can use a formual in a cell like =if(d2=0,0,d1/d2)
where d1 is the numerator and d2 is the divisor
However, that is both the power and the major problem with Excel. That code is now mathematically wrong.
Much better is
=if(d2=0,"Divide by zero",d1/d2)
which you may not think is any better than your original situation.
Cheers
Greg Locock
RE: "IF" STATEMENTS ?????
The '1636"!D5 can sometimes be a value of 0. This is typical on 4 sheets of my file.
RE: "IF" STATEMENTS ?????
Cheers
Greg Locock
RE: "IF" STATEMENTS ?????
This is a monthly cashflow. Some months I might not produce certain model numbers, thus the value of 0. I am entering the total cost of what ever material cost related to all the products them am dividing realtime percentage of the total bill by the number of units made for that month. If you might have another sugestion PLease let me know.
Here is a realtime situation I am talking about.
Steal cost for total widgets produced for the month of Dec is 15,000 there are 4 diferent models of the widgets each model of widgets is broke out in like sheets so to tell the total cost of each widget. I am taking the cost*(total widgets sold\number of this widget model) thus giving me the true percentage of the total cost as it is related to the number of given widget models sold. So in dec I might sell 15 of model A widgets, 10 model B widgets, 0 model C widgets, and 20 model D widgets.
Later
RE: "IF" STATEMENTS ?????
=if('2004 Cash Projections'!D5=0,0,'2004 Cash Projections'!D31*('1636'!D5/'2004 Cash Projections'!D5))
Will give you a 0% for those months where no sales are made.
As a matter of style I'd rather use
=if('2004 Cash Projections'!D5=0," ",'2004 Cash Projections'!D31*('1636'!D5/'2004 Cash Projections'!D5))
to prevent inadvertent misuse of the result.
Cheers
Greg Locock
RE: "IF" STATEMENTS ?????
This is certainly true if you nest IF's one amongst the other. However, if you concatentate IF's together, there is no limit.
eg:
=if(and(a1>.001,a1<.01),"one entry"," ")
&if(and(a1>.01,a1<.02),"two entry"," ")
&if(and(a1>.02,a1<.03),"three entry"," ")
&if(and(a1>.03,a1<.04),"four entry"," ")
etc.....
I don't know if this will help in this exercise however - just thought you might like to know about it.
RE: "IF" STATEMENTS ?????
RE: "IF" STATEMENTS ?????
RE: "IF" STATEMENTS ?????
RE: "IF" STATEMENTS ?????
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: "IF" STATEMENTS ?????