Working out a percentage when a total amount changes.

[28creation]

Hi all,

I'm producing a spreadsheet for work & need a percentage worked out...

I have 12 boxes with drop down options of 1-5, with the percentage box at the bottom working out the result based on a possible total of 60. All 5s = 60, so 100%.

This was straightforward, but I've now had to include N/A so the dropdown reads:

N/A
1
2
3
4
5

What can I do with the percentage box so it tells the user the correct result based on a possible new total, ie for every N/A that's selected it brings the total down by 5.

For example, 6x 5 = 30, 6x N/A, so still 100%.

Any thoughts?

Thanks in advance.

 

[28creation]

That is fantastic, thank you very much!

That was a real headache, really appreciate your help & quick responses.

Thank you!

 

[28creation]

Can you add an error remover too please?

As with future ones they'll be blank initially so we get #DIV/0!

Thanks.

 

[steve the fish]

=IF(COUNT(K8:K30)=0,"",SUM(K8:K30)/SUMPRODUCT(ISNUMBER(K8:K30)*5))

or

=IFERROR(SUM(K8:K30)/SUMPRODUCT(ISNUMBER(K8:K30)*5),"")