none
IF Then statement in Excel RRS feed

  • Question

  • If cell F9 has a √ then I want the results for the formula from cell E9 to show up in cell J9. If cell F9 is empty then I want cell J9 to be empty. I want cell J25 to total all totals showing up in column J. I hope I explained it so you can understand what I am trying to do. Please help.

    I tried the following but it is not working. It shows the actual formula in text form but not the results of the formula.

    Formula in cell J9:
    =IF(F9="","","=SUM(C9-(C9*D9))")

    I also tried this:
    =IF(F9="","","E9")

    The SUM formula in E9 is =SUM(C9-(C9*D9))

    Can you help  me?
    Can this be done?

    Friday, March 11, 2016 4:47 PM

Answers

  • Hi, kimmikim

    you have mentioned that you have formula and when the value of i4 is something else then "" or "x" then it is showing False.

    because you did not set anything that why it is showing False. you have set that to show "" when condition is True but here you also have to set what to display if condition is False.

    You want that it should display "" instead of False. so try the Following line. I just edit your code little.

    =IF(AND(G4="x",I4=""),C4-C4*D4,IF(AND(G4="x",I4="x"),"",""))  

    Now it will show "" instead of False

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Wednesday, March 16, 2016 4:12 AM
    Moderator

All replies

  • Formula in cell J9 could be either:

    =IF(F9="","",C9-C9*D9)

    Or:

    =IF(F9="","",E9)

    And in J25

    =SUM(J1:J24)

    Friday, March 11, 2016 5:06 PM
  • That worked! Thank you.
    Friday, March 11, 2016 5:54 PM
  • Okay, I have another question. I have another formula, IF / AND function. I got it to work but it comes up displaying "FALSE" and I want it to display a blank cell. How can I get it to do that. Here is the formula.

    =IF(AND(G4="x",I4=""),C4-C4*D4,IF(AND(G4="x",I4="x"),""))

    It works and it does what I want it to except the cell is displaying the word "FALSE" when there aren't any x's and I want it to just be blank until I enter in the x in the other cells.

    Please let me know what I am doing wrong.

    Thank you.

    Kim


    • Edited by kimmikim Monday, March 14, 2016 8:31 PM
    Monday, March 14, 2016 8:12 PM
  • Okay, I have another question. I have another formula, IF / AND function. I got it to work but when the both cells are blank it comes up displaying "FALSE" and I want it to display a blank cell. How can I get it to do that. Here is the formula.


    =IF(AND(G4="x",I4=""),C4-C4*D4,IF(AND(G4="x",I4="x"),""))


    It works and it does what I want it to except the cell is displaying the word "FALSE" when there aren't any x's and I want it to just be blank until I enter in the x in the other cells.

    Please let me know what I am doing wrong.

    Thank you.

    Kim

    Wednesday, March 16, 2016 12:31 AM
  • This is in Excel 2007.
    Wednesday, March 16, 2016 12:31 AM
  • Hi, kimmikim

    you have mentioned that you have formula and when the value of i4 is something else then "" or "x" then it is showing False.

    because you did not set anything that why it is showing False. you have set that to show "" when condition is True but here you also have to set what to display if condition is False.

    You want that it should display "" instead of False. so try the Following line. I just edit your code little.

    =IF(AND(G4="x",I4=""),C4-C4*D4,IF(AND(G4="x",I4="x"),"",""))  

    Now it will show "" instead of False

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Wednesday, March 16, 2016 4:12 AM
    Moderator
  • Thank you. I understand that. And it worked perfectly. Thank you again.

    BTW, the survey link did not work. Even when I tried to copy and paste it in my browser. Still didn't work.

    You all are great. I really appreciate having a place to come and get answers to my questions.

    Thanks!

    Kim

    Wednesday, March 16, 2016 1:03 PM
  • When you have two cells that can take two values, there are four combinations to worry about

    G4="",I4=""

    G4="x",I4=""

    G4="x",I4="x"

    G4="",I4="x"

    =IF(AND(G4="",I4=""),"",IF(AND(G4="x",I4=""),C4-C4*D4,IF(AND(G4="x",I4="x"),"",IF(AND(G4="",I4="x"),"Need this","Should never appear"))))

    Wednesday, March 16, 2016 2:13 PM
  • Hi,

    Yes there are 4 combinations. But when we using “And” at that time only 1 combination has true value and 3 combinations have False value. So we have to only think about 2 results i.e. True and False.

    Result when we use And

    Condition 1

    Condition 2

    Result

    True

    True

    True

    True

    False

    False

    False

    True

    False

    False

    False

    False

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Thursday, March 17, 2016 1:52 AM
    Moderator
  • "So we have to only think about 2 results i.e. True and False."

    That is an unfortunate statement that is completely misleading and incorrect.

    For that one combination, there will be TRUE and FALSE, so yes, you only need to think about two outcomes from that one combination.  But there are four combinations, any of which can be TRUE. So you need to think about all four - if there are only one or two or three that you care about, then the formula can be simplified, but you need to consider all four when designing the formula.  There are other ways to approach the problem, using AND, OR, COUNTIF, etc. but the real issue is that all need to be considered.

    Thursday, March 17, 2016 4:23 PM
  • Hi, Bernie Deitrick, Excel MVP 2000-2010

    >> But there are four combinations, any of which can be TRUE.

    Yes there are 4 combinations and any of them can be true but here op clearly want that if that particular combination will true then some operation should be performed. So if that combination will true then rest all combinations will be false. To achieve this op using “And”. If you want to take care about all the combinations then you can use “or”. So if any one condition true and another is false then also the result will fall in to true.

    >> in the given code the care is taken about all the 4 combinations.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Friday, March 18, 2016 2:06 AM
    Moderator