"IF" STATEMENTS ?????

[paullaup]

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,(&quot;use a 1inch baseplate&quot),

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, &quot;USE A 1.25 INCH BASEPLATE&quot;,....and so on.

Has anyone got any ideas???

 

[cowski]

Try this: if(and(c82>1,c82>1.25),&quot;true string&quot;,&quot;false string&quot;

Look up the AND function in the help files for more information. The OR function will come in handy as well.

 

[paullaup]

thanks Cowski,
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!!!

 

[onlyadrafter]

Hello,

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,&quot;NEW&quot))

replace &quot;NEW&quot; 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&quot;.

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!

 

[cowski]

Nested IF's or nested AND's are one way of doing it (as suggested by onlyadrafter). Something like this should work (modify it to your needs):

If(AND(AND(c82>1,c82<1.25),AND(c82>1.1,c82<5))&quot;true&quot;,&quot;false&quot

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.

 

[bltseattle]

A lookup table as described by onlyadrafter would be an excellent way to go, and you will find many more applications for lookup tables once you see how easy it is.

 

[paullaup]

Thanks to all.
That vlookup is cool!!
I think for my particular application the &quot;ceiling&quot; comand is the perfect. I never heard of it!!

thanks again.

 

[jmw]

The limit of seven can be worked around by breaking your range into two groups of IF functions in seperate cells.
SO A2=IF(A1<1,1,IF(A1<1.5,1.5,IF(A1<2,2,&quot;NEW&quot))
and A3=IF(A1<2.5,2.5,IF(A1<3.0,3.0,IF(A1<4,4,&quot;NEW&quot))
Now in a third cell A4(your results cell):
A4=IF(A1<2,A1,A2)

 

[jmw]

PS, to avoid over complicating my IF statements, i usually use a list of answers somewhere (e.g. on a locked hidden sheet,or in hidden coluns on the same sheet). This means that the IF command is simple to set up with the IF staement wizard.

 

[bigtankman]

I had the same problem and wrote this.

=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

 

[ProEDesigner00]

I am &quot;green&quot; to using excel sheets as well. I wanted to show a given value on sheet one into porportional percentages on the related sheets. I got this calculation to work fine. However when a 0 is used as a value the formula in the dependant cells does not compute. Is this if statement the answer to resolving the problem? I have a division in this formula and you cannot divide 0.

Thanks for your answers in advanced.

 

[joerd]

Yes, using IF is the most straightforward way. The IF syntax is:

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

 

[tomatge]

Using nested &quot;IF&quot; statements is OK provided there are not too many possible conditions to evaluate. But what happens if you have 20, 30 or 100 possible outcomes. The IF statement becomes totally unwieldy.

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.

 

[GregLocock]

ProEDesigner00

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,&quot;Divide by zero&quot;,d1/d2)

which you may not think is any better than your original situation.


Cheers

Greg Locock

 

[ProEDesigner00]

Greetings. I will share with you specifically what my statements say and then you can tell me if the &quot;if&quot; statement is the way to go or if the vlookup is what I am after. ='2004 Cash Projections'!D31*('1636'!D5/'2004 Cash Projections'!D5)

The '1636&quot;!D5 can sometimes be a value of 0. This is typical on 4 sheets of my file.

 

[GregLocock]

Yes the if statement will stop the error message, but I think you need to think through why you are attempting a calculation that involves a divide by zero.

Cheers

Greg Locock

 

[ProEDesigner00]

Greg

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

 

[GregLocock]

In that case

=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,&quot; &quot;,'2004 Cash Projections'!D31*('1636'!D5/'2004 Cash Projections'!D5))

to prevent inadvertent misuse of the result.




Cheers

Greg Locock

 

[50gumbys]

I haven't read through all the posts here, but I did notice one comment that there is a limitation of seven levels of an IF statement.
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),&quot;one entry&quot;,&quot; &quot
&if(and(a1>.01,a1<.02),&quot;two entry&quot;,&quot; &quot
&if(and(a1>.02,a1<.03),&quot;three entry&quot;,&quot; &quot
&if(and(a1>.03,a1<.04),&quot;four entry&quot;,&quot; &quot
etc.....
I don't know if this will help in this exercise however - just thought you might like to know about it.

 

[ivymike]

I was going to make the same suggestion. Looks like you beat me to it. Concatenation is my current preferred method of getting results like those that you would get with nested IF statements. One benefit is that you can test your conditions separately, in separate cells, then use a text editor to paste them all together into a big formula.