What is the best way to combine these 2 formulas and simplify it.

=IF(SPECIES@row = "Roses", IF([TYPE OF POT]@row = "LPD", IF(PINK@row = "", IF(RED@row = "", [Type]@row / 72, (PINK@row + RED@row / 4) / 72))))

=IF(SPECIES@row = "Roses", IF([TYPE OF POT]@row = "LPS", IF(PINK@row = "", IF(RED@row = "", [Type]@row / 36, (PINK@row + RED@row / 4) / 36))))

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Mary Kam

    If I'm understanding you correctly, you want to run the first formula if all the criteria are met, and if not (ex. if TYPE OF POT is "LPS" instead of "LPD"), then run the second formula.

    If so, try something like this:

    =IF(AND(SPECIES@row = "Roses", [TYPE OF POT]@row = "LPD"), IF(AND(PINK@row = "", RED@row = ""), Type@row / 72, (PINK@row + RED@row / 4) / 72), IF(AND(SPECIES@row = "Roses", [TYPE OF POT]@row = "LPS"), IF(AND(PINK@row = "", RED@row = ""), Type@row / 36, (PINK@row + RED@row / 4) / 36)))


    It uses the "Value if False" to go into your second statement. Let me know if this works for you and if it makes sense!

    Cheers,

    Genevieve

  • Mary Kam
    Mary Kam ✭✭✭✭
    Answer ✓

    Thank you for your rapid response, it worked perfectly.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Mary Kam

    If I'm understanding you correctly, you want to run the first formula if all the criteria are met, and if not (ex. if TYPE OF POT is "LPS" instead of "LPD"), then run the second formula.

    If so, try something like this:

    =IF(AND(SPECIES@row = "Roses", [TYPE OF POT]@row = "LPD"), IF(AND(PINK@row = "", RED@row = ""), Type@row / 72, (PINK@row + RED@row / 4) / 72), IF(AND(SPECIES@row = "Roses", [TYPE OF POT]@row = "LPS"), IF(AND(PINK@row = "", RED@row = ""), Type@row / 36, (PINK@row + RED@row / 4) / 36)))


    It uses the "Value if False" to go into your second statement. Let me know if this works for you and if it makes sense!

    Cheers,

    Genevieve

  • Mary Kam
    Mary Kam ✭✭✭✭
    Answer ✓

    Thank you for your rapid response, it worked perfectly.

  • Genevieve P.
    Genevieve P. Employee Admin

    Wonderful! I'm glad I could help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!