Error on copying a formula from one row to the next row

Silvana Luna

I am using the Survey Intake - Employee Pulse Check template package to create an NPS survey sheet, report and dashboard. I've updated my questions on the template sheet and I am now editing the "helper columns" to reflect the changed questions. I created a formula to count my responses on the first row, but when I try to copy the same formula on the other rows I get an error "#INVALID OPERATION" I even tried to use the "Convert to Column Formula" but I get the same error message. Has anybody experienced something like this? How can I troubleshoot this error? - Thank you!

Mark Cronk

Hi Silvana,

Try this version:

=IF([2. Please rate the PMO’s quality of service]@row ="1. Poor", 1, IF([2. Please rate the PMO’s quality of service]@row ="2. Fair", 2, IF([2. Please rate the PMO’s quality of service]@row = "3. Good", 3, IF([2. Please rate the PMO’s quality of service]@row = "4. Very Good", 4, IF([2. Please rate the PMO’s quality of service]@row ="5. Excellent", 5)))))

Given the way you've built the responses you could try:

=IF(ISTEXT([2. Please rate the PMO’s quality of service]@row), left([2. Please rate the PMO’s quality of service]@row,1),"")

Either work?

Mark

Mark Cronk

Glad you found a solution. Thank you for contributing to the Community.

Mark

Mark Cronk

Hi @Silvana Luna,

Remove the "=" before your IF statements after the 1st =IF.

Work?

Mark

Silvana Luna

It did not help. I removed the = after the first IF and didn't work, then I removed it from all the IF (except the first one) and it didn't work either. Any other ideas?

Mark Cronk

Hello,

Please paste your formula into a response and I'll take a deeper look. Well figure this out.

Mark

Silvana Luna

Thank you Mark! Appreciate the interest and dedication. Here you go:

=IF([2. Please rate the PMO’s quality of service]@row "1. Poor", 1, IF([2. Please rate the PMO’s quality of service]@row "2. Fair", 2, IF([2. Please rate the PMO’s quality of service]@row "3. Good", 3, IF([2. Please rate the PMO’s quality of service]@row "4. Very Good", 4, IF([2. Please rate the PMO’s quality of service]@row "5. Excellent", 5)))))

Mark Cronk

Hi Silvana,

Try this version:

=IF([2. Please rate the PMO’s quality of service]@row ="1. Poor", 1, IF([2. Please rate the PMO’s quality of service]@row ="2. Fair", 2, IF([2. Please rate the PMO’s quality of service]@row = "3. Good", 3, IF([2. Please rate the PMO’s quality of service]@row = "4. Very Good", 4, IF([2. Please rate the PMO’s quality of service]@row ="5. Excellent", 5)))))

Given the way you've built the responses you could try:

=IF(ISTEXT([2. Please rate the PMO’s quality of service]@row), left([2. Please rate the PMO’s quality of service]@row,1),"")

Either work?

Mark

Silvana Luna

Mark you are a genius! it worked perfectly. You just made my week, thank you so much!

Mark Cronk

Glad you found a solution. Thank you for contributing to the Community.

Mark