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?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Not what I was hoping to hear but that makes perfect sense. Thank you, Ejgil.
ASKER
Problem wasn't solved but an explanation of why it isn't possible was presented clearly.
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
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