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?
Exhausted
Posted 2016-11-25T01:02:55.213
Reputation: 55
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", ""))]
Keypunch
Posted 2016-11-25T01:02:55.213
Reputation: 71
No, its close to what i want. – Exhausted – 2016-11-25T02:20:21.637
Could you elaborate? – Keypunch – 2016-11-25T02:21:26.410
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 – 2016-11-25T02:26:10.483
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 – 2016-11-25T02:31:01.090
It doesn't override if G5 = off though - it displays Not Scheduled even if there is a value in I2. – Keypunch – 2016-11-25T02:32:37.303
Yes but it doesn't change the value to "Hit" when a time is entered that is between I2<E2 and I2>F2 – Exhausted – 2016-11-25T02:34:45.637
This is what i need but it doesn't work: =IF(G5="off", "Not Scheduled", "",IF(OR(I2>F2,I2<E2),"Miss Window","Hit")
When i add in a closing bracket like this =IF(G5="off", "Not Scheduled", ""),IF(OR(I2>F2,I2<E2),"Miss Window","Hit") it displays #VALUE! – Exhausted – 2016-11-25T02:39:40.337
Updated my answer - give that a try. – Keypunch – 2016-11-25T02:40:55.500
Pretty much it says if there IS a value, AND it's less than or greater than the window - its a "Miss Window". – Keypunch – 2016-11-25T02:42:02.797
If there is NO value - and G5 is set to off - display "Not Sched" otherwise be blank – Keypunch – 2016-11-25T02:42:35.110
New code worked perfectly! You have the patience of a saint! :) – Exhausted – 2016-11-25T02:44:46.560
Feel free to mark it answered! – Keypunch – 2016-11-25T02:48:03.057
0
AND and OR statements are your friend.
=IF(OR(I2="", G5="off"), "Not Scheduled", AND(I2>=E2,I2<=F2), "Hit", "Miss Window")
wbeard52
Posted 2016-11-25T01:02:55.213
Reputation: 3 149
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.
Roy
Posted 2016-11-25T01:02:55.213
Reputation: 1
This is where i'm at: =IF(AND(I2>F2,I2<E2),"Miss Window","Hit") but i cant get it to work – Exhausted – 2016-11-25T02:01:26.320
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 – 2017-02-27T03:20:51.977