Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Your formula returns all the correct answers for me, including 0 if E140 is empty.
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [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
    Regards
    John



  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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
  •