Average a series of vales, but sometimes certain values may be not applicable

Hello

I am having a bit of trouble with a new spreadsheet.

I have a sheet I use for work where I "mark" work that is being done. The work is split into 2 sections, Quality and Completeness. Each is assigned 50%.

Within each part, there are sub sections, for example 10 parts at 10 marks each. Now a mark of anywhere between 1 and 10 can be given for these. But sometimes a section may not be applicable, therefore I want to mark the cell as just "na", how would I build a formulae to do this, to then distribute evenly the marks across only 9 of the sub sections? Or less if several sub sections are not applicable.

The two parts – Quality and Completeness are then put together to get an overall score of 100%.

Thought it may help if I give a better example.


Marks awarded 10 5 15 10 20 5 5 10 10 10 Total 100
“Item to be marked”


So the sub sections are worth 10, 5, 15 etc. A mark can be awarded of between 1 and 10 for example where 10 is the maximum. But say the last three sub sections are “na”

So I have this:

Marks awarded 10 5 15 10 20 5 5 10 10 10 Total 100
“Item to be marked” 5 5 12 9 15 5 5 na na na

So effectively only 7 sub sections are used. What formula would be used to then still be the score out of 100, but disregarding the “na” sections an therefore increasing the weighting appropriately of the remaining 7 sub sections?


Many many thanks for anybody’s help in advance?