If functions with but and or nested

Occasional Contributor

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.

19 Replies

@FreePilot 

 

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"
)

 

@L z.

 

I'll try it.  Thank you.  I've been trying for months.  I'll let you know ASAP. 

I'm sorry. It didn't work.

@FreePilot 

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

@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.

 

I'm very thankful for your participation in helping. I would continue to enjoy your input. To keep the conversation simple I'm going to only send my responses to LZ but please follow along. I mean my answer goes to you also.
Perhaps I can direct you to where my problem is.

https://www.faasafety.gov/gslac/alc/libview_chapter.aspx?id=9091&chapter=Step+1+-+Review+Weather+Min...

In a cell it'll show either "VFR" or "MVFR" or "IFR" or "LIFR". (Never blank.)
If I understand correctly. I believe my formula is basically two grading scales combined. Excel to compute 2 logic tests from two grading scales to come up with one answer.

Like a grading scale for school.
IF Jack test result is;
>=90% then "A"
or
>=80% then "B"
or
>=70% then "C".
and so on. This is simple. Though the formula adds on an additional scale with an and/or [BUT IF] to come up with one answer. ;;
If Jack is;
>=90% or Jill is >50 then "A"
But if Jack is
>=80%
and or Jill is >30%, <50% then "B"
But if Jack is
>=70%
and or
Jill is >20%, <30% then "C"
And so on.

Specifically I have a cell that I type in the cloud ceiling in cell C25. I then input the sky visibility in cell C21.
I want excel to comput the logic of both criteria and apply the rule and show what category the end result is in cell D19.
Example my ceiling in C25 is 6000 that should show "VFR" in cell D19 but the second logic test for visibility in C21 is 1 mile the answers for D19 would then show "IFR" instead. Now let's then change C21 to 6 miles then D19 would then show, "VFR" and so on.

I hope this helps you help me.
This is what I've come up with from a month's trial and error but it still isn't working, close but not 100%. Math is math. How can it work mostly?

=IF(AND(C25>3000,C21>5),"VFR",IF(AND(OR(C25>=1000,C25<=3000),C21>=3,C21<=5),"MVFR",IF(AND(OR(C25>=500,C25<1000),C21>=1,C21<3),"IFR","LIFR")))

@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". 

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")))

Everyone. I figured it out. I'm so excited. I was dreaming it lastnight. Just now I just got on my computer and just started typing it out. IT WORKS!!!!!
Here it is.
=IF(AND(C25>3000,C21>5),"VFR",IF(AND(OR(C25>=1000),(C21>=3)),"MVFR",IF(AND(OR(C25>=500),(C21>=1)),"IFR","LIFR")))

@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")))

 

I'll give it a try. Cleaning it up will help because I didn't give you all the true full formula. I have formulas imbedded in the C Cells..... Thanks for your extra help. I'll keep you posted if you wish. Later I'll send you the full true formula in using. It's crazy long so again if your clean version works to help the end formula I'll do it. Cheers.
It worked. Your cleaner version. Thanks. So now a small monkey wrench that was found after some testing. I'm trying to add one more (or) to the first VFR section.....
If cells C23 and C24 are left blank then "VFR".

@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")))

 

I think I tried that already but I'll look more closely.
Hate to bother you again but there's a slight bug. If C21 is less than 5 the outcome should change the titles ex MVFR or ifr etc....
When I tried C23 and C24 to be blank but C21 to be (example) 0.9. Then the outcome should be "LIFR" but it comes out VFR. This bug only happens when C23 and C24 are left blank. Any other test, the forma is working properly.
The formula is working properly but there is a small bug. If C23 & C24 is blank the outcome is correct with "VFR". though the formula ignores C21.

Example C21= 0.8, the outcome should be "LIFR". It doesn't, it reads "VFR"° again only when the aforementioned cells are blank.

Again only when C23 & C24 are blank the formula acts as if C21 has no influence. If there Is something in those cells the outcome is correct.

@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.