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)?

Thank you in advance!

  • 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
    – SQLTemp
    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

1 Answer 1


Use AVERAGEA function to include strings:

For example:

  • Thank you, it works like a charm!
    – Sam
    May 17 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.