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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 216 Industry Talk
 456 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!