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

excel

19 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jan 08, 2023

    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.

     

    • FreePilot's avatar
      FreePilot
      Copper Contributor
      Jan 08, 2023
      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.
  • 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")))

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jan 08, 2023

        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's avatar
      FreePilot
      Copper Contributor
      Jan 08, 2023

      Lorenzo

       

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

Resources