Hi Alansidman,

Thank you for the response. The formula would work if it was a simple calculation. I have attached another file, have a look at row 30, if cell d30 is changed to 0 it will give the true mean for the questionnaire. The reason is some of the questions are scored in reverse order. Likert scale is 1-4 the answer is reversed to 4-1. Look to the formula, it is reversing the answer by calculating cell D30 like so (5-D30). If I remove the 999 and replace it with a blank I get an error. I cannot change the 999 to a blank because some answers have been left blank. If I replace the 999 with a zero I get the true average for the row. I'd like to stick with the 999 because they represent an answer outside the Likert 1-4, which I added.

Thank you for your help,

Attached Files

999calc2.xlsx (10.6 KB, 12 views)