IF THEN in Excel 2007 Formula

Topic

New Reply

I have a cell that I use data validation (from a list), I then do a multiple IF/Then in another cell to compute cost – the last of the statement is 0, if there are no choices made. However, Excel is returning the value of the first IF/Then if the cell is empty. Below is the statment:

=IF(E140=”Tune Up”,1,IF(E140=”Double Shift”,2,IF(E140=”Major Production”,3, 0)))

shihalud

Reply | Quote

Replies

Your formula is structured correctly and returns the correct values. An empty cell or a value other than the others you test for return a 0, Tune Up returns a 1, Double Shift returns a 2, and Major Production returns a 3.

What do you mean with “Excel is returning the value of the first IF/Then if the cell is empty.”? What value is being returned?

Reply | Quote

Your formula is structured correctly and returns the correct values. An empty cell or a value other than the others you test for return a 0, Tune Up returns a 1, Double Shift returns a 2, and Major Production returns a 3.

What do you mean with “Excel is returning the value of the first IF/Then if the cell is empty.”? What value is being returned?

When I highlight the cell with the drop down list and delete the entry the computed value goes to 1, not zero. And when I copy the formula to subsequent rows, it lists 1 not 0 as the result and the field containing the drop down list is empty.

Reply | Quote

There must be something you haven’t told us – the setup you described in the first post should do what you want.
Could you attach a stripped down copy of the workbook?

Reply | Quote

There must be something you haven’t told us – the setup you described in the first post should do what you want.
Could you attach a stripped down copy of the workbook?

Thanks for the help – but I just changed the formula to use the ISBLANK option at the beginning and now it returns nothing is the drop down cell is empty.

=IF(ISBLANK(E53),” “,(IF(E53=”Tune Up”,(119+(G53*11.49)),IF(E53=”Double Shift”,(139+(G53*13.49)),IF(E53=”Major Production”,(179+(G53*17.49))))))+K53)

shihalud

Reply | Quote

Thanks for the help – but I just changed the formula to use the ISBLANK option at the beginning and now it returns nothing is the drop down cell is empty.

=IF(ISBLANK(E53),” “,(IF(E53=”Tune Up”,(119+(G53*11.49)),IF(E53=”Double Shift”,(139+(G53*13.49)),IF(E53=”Major Production”,(179+(G53*17.49))))))+K53)

shihalud

So is this now doing what you want?
You might have a problem if you try to perform further calculations with these results because you put a space character in the cell if E53 is blank. You might be better off with =IF(ISBLANK(E53),0,(etc

Reply | Quote

I’d use

=IF(E53=””,””,…

You might also consider using INDEX and MATCH to look up the parameters for the calculations in a table in a worksheet. That would be easier to maintain than placing the parameters in the formula.

Reply | Quote

Your formula returns all the correct answers for me, including 0 if E140 is empty.

Reply | Quote