# Thread: IF THEN in Excel 2007 Formula

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

2. 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?

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

4. [quote name='mbarron' post='785241' date='18-Jul-2009 21:47']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?[/quote]
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.

5. 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?

6. [quote name='HansV' post='785259' date='19-Jul-2009 03:11']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?[/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

7. [quote name='shihalud' post='785501' date='21-Jul-2009 14:19']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[/quote]

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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•