Jan 08 2023 12:44 AM
I need help. this is my problem...
If C25>3000 and C21>5 then put "green" but if C25>=1000 and/or C21>3 then put "blue" but if C25>=500 and/or C21>1 then put "red" if not then put "pink" ¦.....this is what I have;
¦ =IF(AND(C25>3000,C21>5),"green",IF(oR(C25>=1000,C21>3),"blue",IF(OR(C25>=500,C21>1),"red","pink")))
¦
but its not working. Please help.
Jan 08 2023 01:24 AM
Assuming I understood, what about?
=IFS(
AND(C25 > 3000, C21 > 5), "green",
OR(C25 >= 1000, C21 > 3), "blue",
OR(C25 >= 500, C21 > 1), "red",
TRUE, "pink"
)
Jan 08 2023 01:57 AM
Jan 08 2023 03:46 AM
I'm afraid I don't understand. TBH I have a very hard time to figure out what you exactly mean with:
C25>=1000 and/or C21>3
C25>=500 and/or C21>1
That might be me though... Let's see if someone else undertand & feel free to clarify your thoughts in the meantime
Jan 08 2023 03:49 AM
@FreePilot There's technically nothing wrong with your formula, though you probably applied an incorrect logic to the different IF statements.
Please give an example of a wrong result and explain why it's wrong.
Jan 08 2023 06:50 AM
Jan 08 2023 07:42 AM
Jan 08 2023 11:57 AM
Jan 08 2023 12:16 PM
@FreePilot Perhaps you can clarify what you expect to see in a variety of combinations of C25 and C21, rather than just giving us a formula and say that "it doesn't work".
Jan 08 2023 02:05 PM - edited Jan 08 2023 02:07 PM
I think the issue is how your question is set up to begin with, rather than the equation.
Your equation states that
- If C25>300 AND C21>5, then the answer is green
- If C25>1000 OR C21>3, then the answer is blue
- If C>500 OR C21>1, then the answer is red
- If none of those conditions are met, then the answer will be pink
It will never be green, because the blue conditions are always met. The only answers you will ever get are pink, red or blue.
=IF(AND(C25>3000,C21>6),"green",IF(OR(C25>1000,C21>3),"blue",IF(OR(C25>500,C21>1),"red","pink")))
Jan 09 2023 12:09 AM
Jan 09 2023 01:44 AM
@FreePilot Glad you solved it, but you can get rid of the OR's and many of the brackets. This one should work as well:
=IF(AND(C25 > 3000, C21 > 5), "VFR", IF(AND(C25 >= 1000, C21 >= 3), "MVFR", IF(AND(C25 >= 500, C21 >= 1), "IFR", "LIFR")))
Jan 09 2023 02:08 AM
Jan 09 2023 02:53 AM
Jan 09 2023 03:00 AM
@FreePilot That could be:
= IF(OR(AND(C23="",C24=""),AND(C25 > 3000, C21 > 5)), "VFR", IF(AND(C25 >= 1000, C21 >= 3), "MVFR", IF(AND(C25 >= 500, C21 >= 1), "IFR", "LIFR")))
Jan 09 2023 03:01 AM
Jan 09 2023 03:26 AM
Jan 09 2023 03:37 AM
Jan 09 2023 03:56 AM
@FreePilot It all comes down to clearly defining the logic for all the IFs and put them in the correct order. You mentioned " If cells C23 and C24 are left blank then "VFR". ".
That's exactly what the formula does now. As soon as both these cells are blank it returns VFR and all other conditions are ignored. If that's not what you want you need to clarify. And that's exactly what I suggested in my very first reply. Get the logic correct first, then build a formula. Not the other way around.
Nesting IF functions becomes very ugly and hard to debug if you go to deep. Using IFS, as suggested by @L z. might help. Or perhaps you can use a reference table specifying the applicable flight rules under different circumstances and then use any of Excel's lookup functions.
Can you otherwise upload your workbook? Or share a link to it. That would help as well.