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

04/29/21
Accepted

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!


Best Answers

  • Mark CronkMark Cronk ✭✭✭✭✭
    edited 04/30/21 Accepted Answer

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark CronkMark Cronk ✭✭✭✭✭
    Accepted Answer

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark CronkMark Cronk ✭✭✭✭✭

    Hi @Silvana Luna,

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

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • 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 CronkMark Cronk ✭✭✭✭✭

    Hello,

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • 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 CronkMark Cronk ✭✭✭✭✭
    edited 04/30/21 Accepted Answer

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

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

  • Mark CronkMark Cronk ✭✭✭✭✭
    Accepted Answer

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Sign In or Register to comment.