Nested IF And Function

Chloe Rose

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)))))))))

Werner Gerstacker

Hi @Chloe Rose - 8PCC

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 %.

Werner Gerstacker

Hi @Chloe Rose - 8PCC

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 %.

Chloe Rose

That worked! Thank you!!