Forum Discussion
If functions with but and or nested
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" ¦....
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"
)
I'm sorry. It didn't work.
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>1That might be me though... Let's see if someone else undertand & feel free to clarify your thoughts in the meantime
- 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")))
- 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".
- 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+Minimums
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.
