YTD Percentage Formula

Is there a formula that I may use to keep track of the YTD percentage of attendance of volunteer workers over the course of a year? I have approx 200 workers meeting once a week. Their names are listed in column "A" from A-4 to A-204. Starting in column "E" is the date of the weekday for attendance.
For Example: E-1 is dated Jan. 7, 2008. F-1 is dated Jan. 14, 08 etc. I place a one (1) in the corresponding cell to show that they attended. The formula I need would start in cell D-4 and be dragged down to D-204.
There also must be a way to factor in the weeks which we are to meet that must be cancelled due to weather etc. The worker should not be penalized due to our cancellation.
Thank you for your help.

=COUNTIF(A1:EZ1,1)

gives you the number of attendances

assuming you put a C in when you cancel

=COUNTIF (A1:EZ1,"c") gives you how many times you cancelled

assuming you enter a 0 for a non attendance,

=countif(A1:EZ1,0) gives you non attendances

assuming you insert a blank row between each row

IN B1 enter the formula
=if(A2="",ytdcalc,"")

ytdcalc is obviously your YTD calculation of 100* attendances (1's)/possible attendances (ie 1's plus 0's)derived from above formulae

sorry error

in A2 enter the formula

if(B1="",ytdcalc,"")

robert111,
Thanks for your reply but I'm having some trouble with this. If you will advise how to zip this file to you, I'll be happy to do so.
I'm not as up to snuff as I need to be.

If you go to Windows Explorer and right click on your file, there should be an option in the menu to either Compress or WinZip your file. Let us know if that's not the case.

ChemistB

I'm getting 6 options:
1) extract to:
2) extract to here:
3) extract to folder; my documents.....
4) email sheet
5) create self extractor (.exe)
6) configure

I have used winzip in the past to get sheets to excel forum but I must have forgotten how I did it.
I'm not seeing any option that will get the sheet from my docs to excel forum. I hate to have to be led by the hand but I'm not doing so well by myself.
If you can help, I'd greatly appreciate it.

Hmmm, at what point are you getting those 6 options? Sounds like either the file is already zipped or you've opened up WINZip. Here is a screen shot from Windows Explorer showing what to do.

ChemistB

robert111's sheet.zip

This may get it in to you. I previously had tried to send it to you from a zip file.

Hoorayyyy!!
I feel like a seasoned pro now.
what I want to do is in cell "B-3" and down, have a year to date running percentage of attendance. This will have to take into consideration meetings that were cancelled due to whatever reason in that the volunteer will not be penalized for not attending a meeting that did not happen.
Volunteers #2, 8, & 9 would be 100% attenders since they attended all the meetings that had happened.
If a volunteer misses a meeting, a "0" is placed in the appropriate cell. If the meeting is cancelled, a "C" will be entered.
I hope you can make sense of all this.
Thanks again for your help.

Well, I've gotten what I want, I think.
I experimented and tried a formula of: =countif(range,1)/counta(range) with the numeral "1" indicating attendance for each meeting, a "0" indicating an absence, and a blank cell for cancellations and meetings not yet held. Not sure how this will shake out later but it seems to be working for now. Any suggestions will be greatly appreciated.
I also have been playing with another worksheet of the identical same nature but using "tic" marks instead of the numeral "1" for showing attendance. I have no clue yet how to get a YTD running percentage on that sheet. Thank you all for your help and I'm back off to the lab to do some more spearmnt'n.

Hi Ismft,

You can use the COUNTIF to count your tic marks. Maybe using a COUNTA -COUNTIF (B4:IV4,"c") to sum total meetings.

ChemistB

Originally Posted by ChemistB

Hi Ismft,

You can use the COUNTIF to count your tic marks. Maybe using a COUNTA -COUNTIF (B4:IV4,"c") to sum total meetings.

ChemistB

Works greatly,
Thanks much.