# Calculate average score with missing values

I have an Excel-sheet with student grades, ranging from 0 to 5. If a student didn't submit a solution for the test he/she gets a score of 'NA'.

My sheet looks like the following:

student test 1 test 2 test 3 test 4 test 5 average
student 1 3 2 NA 4 2
student 2 2,5 NA 3,5 3 2,5
student 3 3,5 4 NA NA 4

My question is: how can I replace the 'NA' by a score of 0 but only in the formula for calculating the average (so not the cell-contents)?

• You can use AVERAGE function and you should not get an error. Are you getting an error? What version of Excel are you using? May 17 at 12:41
• Should N/A count towards the average? But it sounds like the IFNA() function will do what you’re after May 17 at 12:42
• @SQLTemp Yes, N/A counts as 0
– Sam
May 17 at 12:43
• @ReddyLutonadio: I use Office 2019. I dont recieve an error, but the result is incorrect
– Sam
May 17 at 12:47
• @SQLTemp How can I combine the IFNA() and AVERAGE() functions please?
– Sam
May 17 at 12:51

``````=AVERAGEA(B2:F2)