• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

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?
0
DesignCityStudio
Asked:
DesignCityStudio
  • 3
  • 2
1 Solution
 
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
 
DesignCityStudioAuthor 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
 
DesignCityStudioAuthor Commented:
Not what I was hoping to hear but that makes perfect sense. Thank you, Ejgil.
0
 
DesignCityStudioAuthor Commented:
Problem wasn't solved but an explanation of why it isn't possible was presented clearly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now