Need help calculating a percentage.

  • Thread starter Thread starter Roy Bunch
  • Start date Start date
R

Roy Bunch

Ok guys, what I am looking for a certain formula.. here is the sample o
the spreadsheat

.........a..................b.................c............d..........

1 John Doe.....10/06/04....10/08/04
2 Frank Doe....9/9/04........9/10/04.....9/12/04
3 Jim Doe........10/1/04..................................
4
5


Basically what I want it to do is count how many "people" I have in th
first colum come up with a percentage complete. I.E if based on th
current example I have 3 people and I have done 6 of 9 tasks and I a
66% complete.

I want it flexible enough so if I throw in another name on line 4 the
it automatically calculates a new percentage.. I.E I would be 6 of 1
tasks done or 50% complete unless I fill in more data for colums B,C
and D.


Anyone suggest a formula for me? Thanks in advance
 
You could count the columns in the row with data and divide
=COUNT(3:3)/9
to get your percentage for each name (jim doe)
 
Roy,

What do the dates signify? Your description doesn't seem to correlate with
the example.
How, using your example data, is the number of tasks determined, and the
count of completed tasks?

If each person has three tasks (you don't say -- it's a guess), then you
could use:

=COUNT(B:D)/(COUNTA(A:A)*3)

When you're trying to put tabular data in a post (your example sheet), it's
a good idea to use a non-proportional font, like Courier, in your
newsreader. Other folks use a non-proportional font too, and the characters
will then line up.
 
Thanks Earl, I was not aware there was a better way to show an example.


I will try to just explain the task and maybe that will make sense.

I have to complete 6 tasks per month per employee of mine. To signif
I complete a task I put the date I complete in one of 6 columns fo
that employee.

My boss likes to see my progress for the month in a percentage format.

So basically I need a formula that checks for the number of employee
in the first column, and to check to see if there is any data in the
columns to the right of the employee.

I.E if I have two employees listed and one has 3 of 6 tasks complet
and the other has 2 of 6 then I would be 5/16 done for the month.

I have figured out a bit of a way I have done it but its extreaml
messy. I basically do a =IF(CELL="",0,1) for each employee.. then eac
seperate column and divide the sum of the employee total by the sum o
the 6 data columns total/6. It takes 300 cells to do this at th
bottom of the sheet and I am sure there is an easier way.
 
Roy,

With two employees, and 6 tasks per employee, and 5 (total) completed, it
seems to me that the result would be 5/12. You give 5/16. Typo? If the
former, try:

=COUNT(B2:G65000)/(COUNTA(A2:A65000)*6)
 
Thanks Earl, that worked like a champ! (And yes that was a typo)

Earl said:
*Roy,

With two employees, and 6 tasks per employee, and 5 (total
completed, it
seems to me that the result would be 5/12. You give 5/16. Typo? I
the
former, try:

=COUNT(B2:G65000)/(COUNTA(A2:A65000)*6)

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

message
Thanks Earl, I was not aware there was a better way to show a example.


I will try to just explain the task and maybe that will mak sense.

I have to complete 6 tasks per month per employee of mine. T signify
I complete a task I put the date I complete in one of 6 column for
that employee.

My boss likes to see my progress for the month in a percentag format.

So basically I need a formula that checks for the number o employees
in the first column, and to check to see if there is any data i the 6
columns to the right of the employee.

I.E if I have two employees listed and one has 3 of 6 task complete
and the other has 2 of 6 then I would be 5/16 done for the month.

I have figured out a bit of a way I have done it but its extreamly
messy. I basically do a =IF(CELL="",0,1) for each employee.. the each
seperate column and divide the sum of the employee total by the su of
the 6 data columns total/6. It takes 300 cells to do this at the
bottom of the sheet and I am sure there is an easier way.

Click to expand...
Click to expand...
 
Back
Top