Nested IF And Function
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: easyexpert) and the PCR (complexity rating: easyexpert). 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

There were a few typos in there, e.g. a 'row' missing and a cell reference to G2.
Try to use this:
=IF(AND(PME@row="Intermediate", PCR@row="Easy"), 0.10,IF(AND(PME@row="Intermediate", PCR@row="Intermediate"),0.20, IF(AND(PME@row="Intermediate", PCR@row="Advanced"),0.50, IF(AND(PME@row="Easy", PCR@row="Easy"), 0.25,IF(AND(PME@row="Easy", PCR@row="Intermediate"), 0.50, IF(AND(PME@row="Easy", PCR@row="Advanced"),1,IF(AND(PME@row="Advanced", PCR@row="Easy"), 0.10, IF(AND(PME@row="Advanced", PCR@row="Intermediate"), 0.15, IF(AND(PME@row="Advanced", PCR@row="Advanced"), 0.25, 0)))))))))
And format your range or maybe even the whole column to show as %.
Answers

There were a few typos in there, e.g. a 'row' missing and a cell reference to G2.
Try to use this:
=IF(AND(PME@row="Intermediate", PCR@row="Easy"), 0.10,IF(AND(PME@row="Intermediate", PCR@row="Intermediate"),0.20, IF(AND(PME@row="Intermediate", PCR@row="Advanced"),0.50, IF(AND(PME@row="Easy", PCR@row="Easy"), 0.25,IF(AND(PME@row="Easy", PCR@row="Intermediate"), 0.50, IF(AND(PME@row="Easy", PCR@row="Advanced"),1,IF(AND(PME@row="Advanced", PCR@row="Easy"), 0.10, IF(AND(PME@row="Advanced", PCR@row="Intermediate"), 0.15, IF(AND(PME@row="Advanced", PCR@row="Advanced"), 0.25, 0)))))))))
And format your range or maybe even the whole column to show as %.

That worked! Thank you!!
Help Article Resources
Categories
Check out the Formula Handbook template!