Hello experts,

I have a job satisfaction dataset/spreadsheet that I am working with. I have a number of cells with values of 0 (zero) which indicate questions that were not answered by the participant. Ultimately, I need to convert those zeros to the average of all other answers from that user. I have created a new column that averages their answers but without going one-by-one and manually plugging the answer into any missing cells for that user, I don't know of a faster way. So ultimately, each user has an average score (column AVERAGE) based on 36 questions. I need to take the value from the AVERAGE column and auto-populate any zeros for that user (because each user will have a different AVERAGE score). Is there a way to do this using a formula?
K DinsmoreAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
I guess the average is for the not zero answers.
See sample, with 3 columns, Name, Answer and Used answer, where the formula for the used answer is.
IF(B2>0,B2,AVERAGEIFS($B$2:$B$17,$A$2:$A$17,A2,$B$2:$B$17,"<>0"))
Meaning: If there is an answer (B>0), use that, else use the average for the answered questions.

If it does not match your setup, please upload a sample workbook.
Average-answers.xlsx
0
K DinsmoreAuthor Commented:
Hi LVL,
Thank you! However, I don't think this is quite what I'm looking for but very close (I think!). I've attached a sample of the data I am working with. Each row has an average calculated "AVERAGE". Any cells with a value of 0 should be replaced with the AVERAGE from that same row. For example: The zero (0) in cell O9 would become 2.7 as indicated in cell AO9 (The range of values I am concerned with are E2:AN2) -Make sense?
JSAVERAGE-SAMPLE.xlsx
0
Ejgil HedegaardCommented:
That is not possible, for 2 reasons.
The average is calculated based on the cells in the row, so the cell is part of the average calculation, and can not have a formula pointing to the average. That creates a circular reference with unpredictable results.
Second reason is that changing the cell value, also change the average.

You need another range to do the calculation, it could be right to the answers.
After the calculation the range could be copied, and the values inserted in the answer range, and the calculation range deleted.

A VBA solution could read the average in the row and replace the zero values, but if it only once, use the above method.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
K DinsmoreAuthor Commented:
Not what I was hoping to hear but that makes perfect sense. Thank you, Ejgil.
0
K DinsmoreAuthor Commented:
Problem wasn't solved but an explanation of why it isn't possible was presented clearly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.