1

I have a question about IF statements.

I wanted to use VBA, but the other guy I am working with doesn't know how to do it, so I am trying to only use worksheet formulas. So, I figured the best way to make Excel do what I want is with IF statements.

So I have two columns that calculate an ideal value for 32 rows. Some have one answers and some have two. The next thing I want to do is calculate if the riser needs to be pulled. It will need to be pulled if the calculated DC values from row 2 do not equal the calculated values for row 1. I wrote the following equation in the Riser Pull column.

=IF(E3=E2;"No";IF(F3=E2;"No";IF(E3=F2;"No";IF(F3=F2;"No";"Yes"))))

But for some reason it is not working properly. Any suggestions?

DC  DC2 Riser Pull
B2  -       No
B2  -       No
B7  C7      Yes
B2  -       Yes
C7  B7      Yes
C2  -       Yes
B7  -       No
C7  B7      No
  • Welcome to Super User! Could you explain what you mean by the formula "not working properly"? Which Riser Pull values in the sample table do not match what you want to be returned? – Excellll Mar 18 '15 at 15:19
  • Also, does your table include hyphens in the DC2 column, or does that just signify a blank cell? – Excellll Mar 18 '15 at 15:20
  • Thanks! The error was occuring towards the bottom of the my table (row 29 I think). Manged to fix that by adding another column that chose a DC from either of the first two columns that picked the same DC if possible. The hyphens are there to signifiy a blank cell. – Tony Mar 18 '15 at 15:32
  • Sorry, I was trying to figure out whether the data you provided showed an error. I can't figure out how what you show here differs from what you want. If you could, please indicate which rows in your sample data have an incorrect Riser Pull value. – Excellll Mar 18 '15 at 16:25
2

As mentioned elsewhere, you should be using commas, not semicolons.

In addition, because you only have two possible return values, I would recommend using the OR function instead of nesting your IF statements:

=IF(OR(E3=E2, F3=E2, E3=F2, F3=F2), "No", "Yes")
| improve this answer | |
  • Llama, no you don't, it depends on your language settings. I'm from Belgium and I use ; instead of , – CustomX Mar 18 '15 at 15:25
0

You need to use commas instead of semicolons in your formula:

=IF(E3=E2,"No",IF(F3=E2,"No",IF(E3=F2,"No",IF(F3=F2,"No","Yes"))))

If you are going to have a lot of these yukky nested if clauses, it might still be worth considering a little bit of VBA to introduce a SWITCH function - see this example, which can then be used as a formula and is much easier to follow.

| improve this answer | |
  • Ill have to look into that. Thanks for the advice! – Tony Mar 18 '15 at 15:06
  • 1
    @Gogoud, no you don't, it depends on your language settings. I'm from Belgium and I use ; instead of , – CustomX Mar 18 '15 at 15:25
  • 1
    @CustomX oh I never knew that, how weird – gogoud Mar 18 '15 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.