Collecting and Averaging survey score excluding ""N/A" as an answer

I have a roll-up sheet from a survey in another smart sheet. There are 12 questions and several Project managers that are being surveyed. I currently have 1 issue remaining. I can collect and average the survey scores for each project manager, unless the person completing the survey selected “N/A” as an option. My current code is below, but I can't figure out where to put the formula to ignore “N/A”.

=IFERROR(AVG(COLLECT({CBRE PM Client Satisfaction Survey Range 7}, {CBRE PM Client Satisfaction Survey Range 2}, [Project Manager]@row)), "”)


  • AravindGP
    AravindGP ✭✭✭✭✭

    Hi @scottposey

    Smartsheet will usually ignore any result that is not a percentage, assuming you've the answers listed as a percentage or number in the Average/Collect formula. It will help if you could share a sample data set of the responses that correspond to CBRE PM Client Satisfaction Survey Range 7.



    Reach out for any help on licenses, configuration, or training

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!