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)?


[TABLE="width: 500"]
<tbody>[TR]
[TD]John[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]Debbie[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD][BLANK][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][BLANK][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Graham[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Participation[/TD]
[TD="align: center"]100%[/TD]
[/TR]
</tbody>[/TABLE]

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%


[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]John
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD]Mike
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Debbie
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD][BLANK]
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][BLANK]
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Graham
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD]Susan
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]80%
[/TD]
[/TR]
</tbody>[/TABLE]
Whaddya reckon?
Cheers
Steve