Accumulating Attendance Base upon Age Group

[Neon520]

Okay, now I know how to calculate a person age using datedif, but I need to
know how to accumulate the attendance of a person.

I've set up 30 columns for a month and blank cell represent attending, "A"
as absent unexcused and "AE" as excused absent. Previously, I calculate the
attendance by taking 30 minus the appearance of A, and AE, ( I used countif
function to add up A and AE). But now that I have to categorize each
person's attendance by their age group, how can I ACCUMULATE each person
attendance?

WHAT IF, for example, one person birth date fall into the middle of the
month and need to move to the next age group, can Excel handle this type of
variable situation?

Is there anyone interested in solving this problem?

Thanks a millions for any clue?

GU
P.S. I'm really frustrated!!!!

 

[Bob Phillips]

=SUMPRODUCT((age_group_range=test_age_group)*(absence_range<>"A")*(absence_range<>"AE"))



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Neon520]

Hi Bob,

Can you give a little more detail on your function? I don't get what do you
mean in the function... What is test_age_group?

Probably I'm too vague, let me give more detail on the age group part:
Let's say there are 3 age group, 0-18 years, 19-35, and 36+. And each of
these age group columns is the sum of the attendance of each person. And
let's say the perfect attendance is 30 days for April, if Johny has perfect
attendance but he turn 19 on the April 16th, then he should have 15
attendance under 0-18 age group and 15 attendance under 19-35 age group.

I hope this will clear up some uncertain point.
Thanks,
GU

 

[Bob Phillips]

I think you need to give an example of your data and expected results.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Neon520]

Okay, Bob, here's what I got.

Column A is the NAME.
Column B-AE is the 30 across the month.
Column AF is 0-18 age group.
Column AG is 19-35 age group.
Column AH is 36+ age group.
Column AI is the birth date of each record.

Row 1 is the column heading.
Across B-AE, I validate the data with drop-down list with different letters
representing different attendance status, ie. A for absent, AE for Absent
Excused, blank cell for regular attendance. (There are more status, but let's
just deal with these for now)
Let's say for example, row #2 is the first record with the person named
Johny. What I did before (without dividing up different age group) I use 30
(which the days of the month) MINUS the letter (A, AE) that are found in
B2-AE2, using COUNTIF function.

But now that I have to deal with the AGE GROUP. I need to find a away to
accumulate/count each person attendance according to their age group.
In Johnny case, if he turn 19 at some point in the month, let's just say the
16 of the month, from 1st-15th of the month would belong to 0-18 age group,
and 16th-30th of that same month would belong to 19-35 age group. This is to
be done because of the funding reason, it doesn't matter if it is the same
person, his/her attendance will need to be split once the age change to a
different age group.

I know how to calculate a person age at any one point of time, but I can't
figure out a way to route the attendance of each day to the right age group,
because of the variable situation that one person might turn to the upper age
group at any one point of time, and I need to have my spreadsheet handled
this type of situation.

I am more than happy to provide more info, if this is still not clear.

Thanks for your help.
GU

 

[Bob Phillips]

Okay, a few changes to your spreadsheet.

Change AF1 to 18, AG1 to 35 and AH1 to 99

Then in AE2 add

=SUMPRODUCT((DATEDIF($AI2,DATE(YEAR(TODAY()),MONTH(TODAY()),$B$1:$AE$1),"Y")<=AF$1)*($B$2:$AE2<>"A"))

and in AF2 add

=SUMPRODUCT((DATEDIF($AI2,DATE(YEAR(TODAY()),MONTH(TODAY()),$B$1:$AE$1),"Y")<=AF$1)*($B$2:$AE2<>"A"))-SUM($AF2:AF2)

copy AF2 over to AG2

BTW, what happened to the 31st?


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)