weekly student status

[f-x.p]

Hi,

I have a list of students, with 3 columns:

- Name
- starting_date
- end_date

I would like to have a graph showing for every week of the year how
many students were there.

Can someone point me in the right direction to do this? Or send me a
sheet doing something equivalent.

My first guess was to build a sheet with very long formulas in each
cell, calculating with ifs the presence of each student. Maybe there
is an easier way.

Thanks a lot

François

 

[Bob Phillips]

Maybe a pivot table and a pivot chart?

Alternative

Create a list of dates to chart and then add a formula of

=SUMPRODUCT(--($B$2:$B$30<=I2),--($C$2:$C$30>=I2))

where B and C are the student dates, I is the list of dates, then chart this
list.

--
---
HTH

Bob

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



Hi,

I have a list of students, with 3 columns:

- Name
- starting_date
- end_date

I would like to have a graph showing for every week of the year how
many students were there.

Can someone point me in the right direction to do this? Or send me a
sheet doing something equivalent.

My first guess was to build a sheet with very long formulas in each
cell, calculating with ifs the presence of each student. Maybe there
is an easier way.

Thanks a lot

François

 

[f-x.p]

Hi Bob,

Thank you for your answer. I had never used the sumproduct function. A
simple google on it gave me a lot of things to try with numbers but
also, as you used it, with dates.

Very good idea,

François