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.
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.
Response by poster: 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
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
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
=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
Do you have access to a database program?
posted by jeather at 4:03 PM on August 6, 2012
This thread is closed to new comments.
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