Nested IF And Function

Chloe Rose
Chloe Rose ✭✭
edited 06/11/20 in Formulas and Functions

I'm trying to create a nested IF Formula to do the following: I want to calculate a capacity score or percentage based on the columns PME (experience rating: easy-expert) and the PCR (complexity rating: easy-expert). My thought a really long nested IF statement where it would go through the following permutations: For example, if the PME is easy/green & the PCR is blue/ intermediate the Capacity score is 50%. IF the PME is easy/green and the PCR is easy/green the capacity score is 25% and if the PME= Advanced/expert and the PCR=intermediate it would be 15%. There will be about 9 different combinations that can return a maximum of 4 different percentages.

PME= Project Manager Experience Level

PCR= Project Complexity Rating

I have been able to make it work successfully in Google Sheets but when I follow the same format in Smartsheet it always returns an unparsable error.

Formula I currently have:

=IF(AND(PME@row="Intermediate", PCR@row="Easy"), 10%,IF(AND(PME@="Intermediate", PCR@row="Intermediate"),20%, IF(AND(PME@row="Intermediate", PCR@row="Advanced"),50%, IF(AND(PME@row="Easy", PCR@row="Easy"), 25%,IF(AND(PME@row="Easy", PCR@row="Intermediate"), 50%, IF(AND(PME@row="Easy", G2="Advanced"),100%,IF(AND(PME@row="Advanced", PCR@row="Easy"), 10%, IF(AND(PME@row="Advanced", PCR@row="Intermediate"), 15%, IF(AND(PME@row="Advanced", PCR@row="Advanced"), 25%, 0)))))))))


Best Answer


  • That worked! Thank you!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!