Join 3,512 readers in helping fund MetaFilter (Hide)

Tags:

Work Pivot Table Magic...
August 6, 2012 1:20 PM   Subscribe

Excel Pivot Table Help - My google-fu and excel-fu have failed me, I'm working on a tracking pivot table that will display total number of attendants in a class (broke out in gender) for each day based on a start and end date.

The data will be entered in this format:

Name Start End Date Class Gender
Bob 5/5/12 6/4/12 Math male
Larry 5/5/12 6/4/12 Math male
Sue 6/1/12 6/8/12 History female
Jesee 6/1/12 6/8/12 History male
Erin 6/1/12 6/8/12 Geography female
Cat 5/5/12 6/8/12 English female
Dave 6/1/12 6/4/12 Science male
Liza 5/5/12 6/4/12 Science female
Sally 5/5/12 6/4/12 Science female
etc...

With the pivot table breakout out based on gender showing total number in each class for days in the range.

Jan Feb
1 2 3 4 5 6 7 … 1 2 3 4 5….
Male
Math
History
Science
Total
Female
Math
History
Science
Total

Total

Any help would be appreciated.
posted by aggienfo to Computers & Internet (5 answers total)
 
Column: Date

Row:
Sex
Class

You're going to need a column with either X or 1 in it after the Class, then the Value would be count.

Make sense?
posted by Ruthless Bunny at 1:29 PM on August 6, 2012


The catch for me is with the date, it's not a discrete numer, but a range with start and end as the entered data. How would I break that out?
posted by aggienfo at 2:07 PM on August 6, 2012


You can just subtract one day from another to get the number of days. I'm guessing, though, that you're going to want to filter out weekends from that, and for that, you'll need to make a separate table that lists the day of the weekday name for each day, and then subtract that from those totals you get- you can do something like a countif() to see how many weekend days fall in those date ranges.
posted by thewumpusisdead at 2:17 PM on August 6, 2012


The formula I use is DATEDIF

=DATEDIF(A1,A2,"d").

the "d" in the formula is for Days. If you want months it's "m". It doesn't take weekends into account though.
posted by Ruthless Bunny at 2:34 PM on August 6, 2012


This sounds a lot more database-y to me than spreadsheet-y. If the dates in the sheet aren't likely to change a lot, you can add a bunch of extra columns, one for each day you are checking, and then just put a 1 or a 0 in each (by formula) depending on whether the student attended that day.

Do you have access to a database program?
posted by jeather at 4:03 PM on August 6, 2012


« Older Best way to straighten my hair...   |  Is it possible/simple/affordab... Newer »
This thread is closed to new comments.