Percentage of overall attendance

[Guest]

Hi there...I am new here. I am looking for some help. I thought I knew how
to use excel with many nested ifs and such but then I tried doing this one!!
When dates are involved it becomes so confusing. I use excel office xp
professional.
I am trying to set up a sign in sheet for attendance of meetings. Then I
want a column that sums the number of times attended and the percentage of
the overall attendance at any given time. So as the months go along and time
passes I want the total of days to increase and the percentage to change
accordingly. So at anytime I could go in and see what percentage a student
attended class over the number of classes given over the year. Next to that
column I would like a column that shows the last date attended.
Any help will be GREATLY appreciated. I think dates are so confusing!
thanks so much...maureen

 

[Biff]

Hi!

Sounds fairly easy!

What exactly are you having trouble with? Need very specific details, with
emphasis on details like ranges , formulas, etc.

Biff

 

[Guest]

Gosh thanks for helping...let me see if I can explain.
I need a row with a box for each Sunday of the month. Then the student will
check the box if they are in attendance. I need a way to see what percent
of Sundays up to any given point in time they have attended. So if 10
Sundays of the year have passed and they were there 8 of them it would show
80%. But then the next Sunday rolls by and they don't attend. So the
%Attendence box automatically updates to only 8 out of 11 Sundays present.
Does that make sense? So it is a constant "real time" percentage. Is that
possible?
THEN... I need a box that shows the actual last date they were in attendence.
Any help will be greatly appreciated.
maureen

 

[Guest]

Maureen, I have the following “clumsy†way to offer – it works for me though.

Starting with a blank sheet, leave a couple of blank rows at the top for
some formulas, colum titles in row 3, data from row 4 down to say row 400.

Col A is for the dates, and I’m assuming two things here, 1) that the dates
are only put in when the meeting takes place, and 2) that they’re in order
(you’ll see why later). Col B is for the “y†or “n†to indicate if the
student attended,

The easiest one to do is the number of attendances and you can do that with
the following in cell B1: =COUNTIF(B4:B400,â€yâ€).

I think counting the number of meeting dates is a little more tricky, so I
put in an interim step. In Cell C4, (and then copied all the way down to
C400) I put the following: =ISNUMBER(A4) – if there is a date, you’ll get
TRUE, and FALSE if the cell is empty. You can then count the number of
TRUE’s, by putting the following in C1: =COUNTIF(C4:C400,â€TRUEâ€). Having now
got numbers for the number of meetings and the number of attendances, you can
do your percentage.

Finding the date of the last attendance is the trickiest, and the way I’d do
it as follows. Firstly, you need to find the relative position in the list of
dates when the student last attended, and I’d do that in two steps. In D4 I’d
put the following: =IF(B6="y",ROW(B6)) – this will just give you the row
number (not the cell reference) everywhere there’s a “y†and a FALSE for
every “nâ€. Then in cell D1, I’d put =MAX(D4400) – this will find the
largest number and therefore the last row where there’s a “yâ€.

Finally, you can use this relative position to lookup the date by using the
following:
=INDEX(A4:A400,D1). You might have to format the cell to display the date in
the correct format.

Hope this helps,

Huw.

 

[Biff]

So, is this for each individual student or all students as a group?

Not to sound arrogant or anything like that, but this should be pretty easy
to do. Have you started this project or are you still in the planning stage?

The reason I ask is that if you have something put together and are just
"stuck" I could probably give you some better suggestions if I could see the
file.

Biff

 

[Biff]

Hi!

Maureen, I have the following "clumsy" way to offer - it works for me
though.

The bottom line is: it works for me.

However, you're going through a lot of unnecessary steps to arrive at a
conclusion!

=COUNTIF(B4:B400,"y")

That will count ALL instances of "Y". Is this for ALL students as a group or
is it for each individual student?

I think counting the number of meeting dates is a little more tricky, so I
put in an interim step. In Cell C4, (and then copied all the way down to
C400) I put the following: =ISNUMBER(A4) - if there is a date, you'll get
TRUE, and FALSE if the cell is empty. You can then count the number of
TRUE's, by putting the following in C1: =COUNTIF(C4:C400,"TRUE").

This is being redundant! If you enter a date as a true Excel date and not
simply a TEXT entry that looks like a date, and with those true dates in
column A:

=SUMPRODUCT(--(ISNUMBER(A4:A400)))

No need for: =ISNUMBER(A4) or =COUNTIF(C4:C400,"TRUE")

Finding the date of the last attendance is the trickiest, and the way I'd
do
it as follows. Firstly, you need to find the relative position in the list
of
dates when the student last attended, and I'd do that in two steps. In D4
I'd
put the following: =IF(B6="y",ROW(B6)) - this will just give you the row
number (not the cell reference) everywhere there's a "y" and a FALSE for
every "n". Then in cell D1, I'd put =MAX(D4400) - this will find the
largest number and therefore the last row where there's a "y".

Finally, you can use this relative position to lookup the date by using
the
following:
=INDEX(A4:A400,D1). You might have to format the cell to display the date
in
the correct format.

You can do all of that in a single operation. Assume you want to know the
last meeting John Smith attended.

Column A are the dates. Column B are the student names and column C is the
attendance value of Y (Yes) or N (No):

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(B1:B10="John Smith",IF(C1:C10="Y",A1:A10,"")))

Format the cell as DATE.

Just some thoughts!

Biff

 

[Guest]

Hi Biff...I tried to write you to your email but it got sent back as
undeliverable. Anyway I have only tried formatting this project. What I
mean is I am still at square one. I realize this is probably very easy and I
am just not seeing it or making it seem more complicated than it should.
The percentage of attendance is for each individual student attendance. I
need to be able to look at the report and see what each student's percent of
attendance is based on youth events that are scheduled and then what date
they last attended.
I have tried reports that use date formatting in access as well and I always
seem to get it wrong. I guess I have tons to learn.
Thanks for any help you can be.
maureen