Separating letters from numbers formula

[nomad2022]

Hi

At my school, I was asked to assist with a formula problem that I can't solve.
We use excel for our grading sheets, so the formula to add up everything and percentages are all fine.

The problem I can't solve is: When a student is absent, We will write "A" in the cell instead of a number(mark).
That "A" needs to not affect the formula.

Is there such a formula or something I can do to help fix this?

Please let me know, Excel is not my expertise lol.

Any suggestions will be appreciated

thanks
regards
Damon

 

[Mposwal]

Not sure about Excel 2016 version but seems like Working fine in Excel 365

 

[Peter_SSs]

Welcome to the MrExcel board!

Could you give us a small set of dummy data and the expected results with XL2BB so that we can see the layout and also copy the sample to test with?