×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

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

Students Click Here

Jobs

"IF" STATEMENTS ?????

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

RE: "IF" STATEMENTS ?????

Try this: if(and(c82>1,c82>1.25),"true string","false string"

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

RE: "IF" STATEMENTS ?????

(OP)
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!!!

RE: "IF" STATEMENTS ?????

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,"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 ?????

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

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.

RE: "IF" STATEMENTS ?????

(OP)
Thanks to all.
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 ?????

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,"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 ?????

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.

RE: "IF" STATEMENTS ?????

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

RE: "IF" STATEMENTS ?????

I am "green" 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.

RE: "IF" STATEMENTS ?????

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.

RE: "IF" STATEMENTS ?????

Using nested "IF" 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.

RE: "IF" STATEMENTS ?????

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

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

Cheers

Greg Locock

RE: "IF" STATEMENTS ?????

Greetings.  I will share with you specifically what my statements say and then you can tell me if the "if" 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"!D5 can sometimes be a value of 0.  This is typical on 4 sheets of my file.

RE: "IF" STATEMENTS ?????

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

RE: "IF" STATEMENTS ?????

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

RE: "IF" STATEMENTS ?????

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

to prevent inadvertent misuse of the result.


Cheers

Greg Locock

RE: "IF" STATEMENTS ?????

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),"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 ?????

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.

RE: "IF" STATEMENTS ?????

You could also consider Case Select. It is a way of looping. I haven't used it but John Walkenbach covers it in his books.

RE: "IF" STATEMENTS ?????

Paullaup.Since you require standard thickess plates why not create a table of thicknesses and LOOKUP this table?

RE: "IF" STATEMENTS ?????

That was suggested two weeks ago by bltseattle.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: "IF" STATEMENTS ?????

(OP)
Thanks all, foir your help. I've really learned a lot

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources