0

I'm trying to join the following 3 IF statements in Excel but every time i try i get an argument error or else it reverts back to displaying "True" or "False" instead of "Miss Window", "Hit", "Not Scheduled" & Blank.

=IF(I2<E2,"Miss Window","Hit")

=IF(I2>F2,"Miss Window","Hit")

=IF(G5="off", "Not Scheduled", "")

Suggestions?

  • This is where i'm at: =IF(AND(I2>F2,I2<E2),"Miss Window","Hit") but i cant get it to work – Exhausted Nov 25 '16 at 2:01
  • How are E2, F2, and G5 related? What happens if more than one of your conditions is true? What if none are true? What if two tests are in conflict (say I2<E2 and I2<F2)? – fixer1234 Feb 27 '17 at 3:20
2

I'm assuming that you want "Miss Window", "Hit", or "Not Scheduled" to show up in a single field.

If I2 Is NOT Empty (True = Next IF)
If I2<E2 - (True = Miss Window / False = Next IF)
If I2>F2 - (True = Miss Window / False = "Hit")

If I2 IS Empty
If G5 = "off" - (True = "Not Scheduled" / False = ""

The following is the code:

    =IF (I2 <> "", IF(I2<E2, "Miss Window", IF(I2>F2,"Miss Window","Hit")), IF(G5="off", "Not Scheduled", ""))]
  • No, its close to what i want. – Exhausted Nov 25 '16 at 2:20
  • Could you elaborate? – Keypunch Nov 25 '16 at 2:21
  • No, its close to what i want. What i need is if I2<E2 or I2>F2 its a missed window but if its within the two parameters then i need it to be a Hit. So i have a time frame... say 10:00am to 12:00pm. If the time is before or after those figures its a "miss", if the time is within those figures its a Hit and i need the G column to override that statement if a G tab contains "Off" and display "Not Scheduled" however if a time is entered and the G column shows Off i need it to again override and put in Hit or Miss That makes no sense! haha – Exhausted Nov 25 '16 at 2:26
  • Ok so far this is working perfectly: =IF(OR(I2>F2,I2<E2),"Miss Window","Hit") Now i need to add if G5=Off, show "not scheduled" and if G5=BLANK show nothing and i need to prioritise that ahead of the above formula – Exhausted Nov 25 '16 at 2:31
  • It doesn't override if G5 = off though - it displays Not Scheduled even if there is a value in I2. – Keypunch Nov 25 '16 at 2:32
0

AND and OR statements are your friend.

=IF(OR(I2="", G5="off"), "Not Scheduled", AND(I2>=E2,I2<=F2), "Hit", "Miss Window")
0

To make G5 dominate ("override") the formula, test it first as wbeard does. Only AFTER it is evaluated do you address the question of "Hit" or "Miss." It CAN be done after Hit or Miss is considered, but like Ptolemy's epicycles, why do so when there is a simpler, more logical way?

Given the physicality of the context, I2 does not need to be tested for an entry. If blank, it will be treated as "0" and therefore will naturally never give an error when compared to E2 and F2.

Those are your guiding order (G5, then Hit or Miss) and theoretical concern to speed up your spreadsheet or at least simplify its logic (blank I2 is no concern). Suitably simplified logic seems a mild thing, but the clearer your logic, the easier it is to maintain (i.e.: endlessly change it when your boss gets a new thought) and to audit for mistakes.

Speeding up calculation isn't really important nowadays, in itself, unless one had tens of thousands of instances of the formula, as memory is plentiful and CPU's are very fast, but good practices keep your thinking sharp and pay off in other situations when something added "just to be sure" or "as a good general practice" literally provides nothing of value.

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.