Percentage complete based on Y or N for six categories.

Hello

I am trying to develop a formula that would provide percentage complete triggering on Y for each category. So, in short, if one Y is seen, display 18, if two Y's are seen, display 36, if three Y's are seen, display 54, if four Y's are seen, display 72, if five Y's are seen, display 90, and if six Y's are seen, display 100.

The formula below seems to be triggering but going in order from the first category to the six category. A Y can appear in any Test category in random order.

=IF(AND(TestOne@row = "Y"), "18", IF(AND(TestOne@row = "Y", TestTwo@row = "Y"), "36", IF(AND(TestOne@row = "Y", TestTwo@row = "Y", TestThree@row = "Y"), "54", IF(AND(TestOne@row = "Y", TestTwo@row = "Y", TestThree@row = "Y", TestFour@row = "Y"), "72", IF(AND(TestOne@row = "Y", TestTwo@row = "Y", TestThree@row = "Y", TestFour@row = "Y", TestFive@row = "Y"), "90", IF(AND(TestOne@row = "Y", TestTwo@row = "Y", TestThree@row = "Y", TestFour@row = "Y", TestFive@row = "Y", TestSix@row = "Y"), "100", ""))))))

I would like the formula to trigger on a Y on in any order.


Thanks

Cris

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    If all rows are next to each other, you can use

    =MAX(COUNTIFS(TestOne@row:TestSix@row, @cell = "Y") * 18, 100)


    If they are not next to each other, you will need to "add" your IFs together instead of nesting them.

    =MAX(IF(TestOne@row = "Y", 18, 0) + IF(TestTwo@row = "Y", 18, 0) + IF(TestThree@row = "Y", 18, 0) + .................., 100)

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    If all rows are next to each other, you can use

    =MAX(COUNTIFS(TestOne@row:TestSix@row, @cell = "Y") * 18, 100)


    If they are not next to each other, you will need to "add" your IFs together instead of nesting them.

    =MAX(IF(TestOne@row = "Y", 18, 0) + IF(TestTwo@row = "Y", 18, 0) + IF(TestThree@row = "Y", 18, 0) + .................., 100)

  • AnalyticOwl
    AnalyticOwl ✭✭✭

    Hello Cris!

    The problem with the nested if formula is that it is a linear condition, so when one true condition is triggered jumps to the end.

    If you combine the nested if with a counter it could work better I tried these and work for that scenario check the formula above to see if can help you.

    =IF(COUNTIF(TestOne@row:TestSix@row, "Y") = 1, 18, IF(COUNTIF(TestOne@row:TestSix@row, "Y") = 2, 36, IF(COUNTIF(TestOne@row:TestSix@row, "Y") = 3, 54, IF(COUNTIF(TestOne@row:TestSix@row, "Y") = 4, 72, IF(COUNTIF(TestOne@row:TestSix@row, "Y") = 5, 90, IF(COUNTIF(TestOne@row:TestSix@row, "Y") = 6, 100, 0))))))

    Good vibes

    Jhair

  • The formula works Paul. So, can I get these to show up with the % sign? I tried the % symbol under tools but it provides a thousands for example 18 complete would show up as 1800%.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Divide by 100 then apply the % formatting.

    =formula / 100

  • Thanks Paul. This worked.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!