Percentage Calculation

[DJFANDANGO]

Hi Again Guys...

I have a list of 'stats', from a list of names, but... theres always a 'but'...

I have 52 rows for names, but sometimes only 45 or so are populated, how do I get the percentage to ignore a blank cell where a name should be ($A:$A)?

John	1
Mike	1
Debbie	1
[BLANK]
[BLANK]
Graham	1
Susan	1
Participation	100%

<tbody>
</tbody>

Any help woud be greatly appreciated
Steve

 

[DSCfromCFA]

Re: Percentge Calculation Help

G'day Steve,

A few details to help us answer your question:

Under what circumstances does the "1" appear in the column you are using for %? I.e added manually or calculated?
What cells are used to calulate the percentage. I.e % of what? I'm assuming that ratio between the 1's and the rows with a name.
Are you able to add a helper column? May not be needed but handy to know if we can.

Cheers

shane

 

[DJFANDANGO]

Re: Percentge Calculation Help

G'day Shane,

The 1 represents 'the 1 item per week that they are supposed to do (as a minimum)' if they do 4 items thats also cool, but the 100% number comes from all personnel completing at least 1 item per week.

So for example, I have a list of 10 names but with gaps between them (this cannot be changed) so in all, there are 15 rows of cells.

Inside the percentage cell is this formula '=COUNTIFS(B1:B45,">=1")/COUNTA(B1:B45)'

I'd like excel to calculate the percentage of persons based on how many are on the list, (if 10 names participate it's 100%, if 5 participate 50% and so on)

Hope this clears up the scenario

Steve

 

[lrobbo314]

Re: Percentge Calculation Help

How about

=COUNTA(B1:B45)/ROWS(B1:B45)

 

[DJFANDANGO]

Re: Percentge Calculation Help

Hi Shane,

That code works and gives me 100% regardless if A1:A50 is blank or not.

If the cell is blank I dont need it to count in the percentage

If the cell has name I need it to count in the percentage

So the table below shows 2 blank cells (not counted, and A2 (mike) name on list, but not done any task) so the percentage I want to achieve now is 80%

John	1
Mike
Debbie	1
[BLANK]
[BLANK]
Graham	1
Susan	1
	80%

<tbody>
</tbody>

Whaddya reckon?
Cheers
Steve