# Working out a percentage when a total amount changes.

#### 28creation

##### Board Regular
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?

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Maybe this:

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

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.

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

Score
5

5

5

5

5

5

5

5

5

5

5

5

50%

Score
n/a

n/a

n/a

5

5

5

5

5

5

5

5

5

43%

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

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.

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

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

So this should work:

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

Replies
0
Views
337
Replies
4
Views
274
Replies
0
Views
54
Replies
7
Views
160
Replies
1
Views
86

1,196,516
Messages
6,015,676
Members
441,915
Latest member
sm Hussaini

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back