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.

 

[steve the fish]

Maybe this:

=SUM(A1:L1)/SUMPRODUCT(--(A1:L1<>"N/A")*5)

 

[28creation]

Thanks for this but it doesn't quite work as I want it to.

When I have 12 5s it only gives 50% instead of 100%.

And...

When I select an N/A it changes the percentage.

 

[steve the fish]

Can you paste the data you are using to get those values?

 

[28creation]

Score
5

5

5

5

5

5

5

5

5

5

5

5

50%

 

[28creation]

Score
n/a

n/a

n/a

5

5

5

5

5

5

5

5

5

43%

 

[steve the fish]

Ok so what formula are you using and I presume they are in A1:A12?

 

[28creation]

They are in K8:K31 (2 boxes are merged, so K8 & 9 are one box).


=SUM(K8:K31)/SUMPRODUCT(--(K8:K31<>"N/A")*5)


Thanks.

 

[steve the fish]

Ok it looks like you have spaces between your cells? Maybe this then:

=SUM(A2:A24)/SUMPRODUCT(ISNUMBER(A2:A24)*5)

 

[steve the fish]

So this should work:

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