Percentage complete based on Y or N for six categories.



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.



Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!