Forum Discussion

FreePilot's avatar
FreePilot
Copper Contributor
Jan 08, 2023

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" ¦....
excel
Lorenzo's avatar
Lorenzo
Silver Contributor
Jan 08, 2023

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

 

rpruitt's avatar
rpruitt
Copper Contributor
Jan 08, 2023

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

Resources