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

scottposey

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

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.