Making a graph to track attendance

[hisB]

Hi! I hope someone can come up with a suggestion for me!
Right now I've got a spreadsheet, and each tab has a week, with names under column A and columns B-F are Monday-Friday. As of now, we have filled in for each cell YES, NO, or LATE. Is there a way you can think of that would easily help me track this, both on a weekly basis (each tab) and one master graph for the whole spreadsheet?
Thanks!!!

 

[Akashwani]

Hi,

Welcome to MrExcel.

I don't know if this is the best solutionway of going about it, but it's one.

Add a sheet called First before sheet Week1.
Add a sheet called Last after sheet Week52.

On Sheet Week1 (to Week52) I would have something like this in H1:I3.....

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Name	Monday	Tuesday	Wednesday	Thursday	Friday	*	Yes	14
2	Bill	Yes	Late	Yes	No	No	*	No	17
3	Bob	Yes	Late	Yes	No	No	*	Late	9
4	Tom	Yes	Late	Yes	No	No	*	*	*
5	Tim	Yes	Late	No	Late	No	*	*	*
6	Dan	No	Late	Yes	No	No	*	*	*
7	Jo	Yes	Late	Yes	No	No	*	*	*
8	Joe	Yes	Late	Yes	No	No	*	*	*
9	Sam	Yes	Late	Yes	No	No	*	*	*
Week1

On your Chart sheet I guess you will need something like this......

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Week	Yes	No	Late	*	Running	Totals	*	Indirect Criteria
2	Week1	14	17	9	*	Yes	43	*	I1
3	Week2	16	14	10	*	No	46	*	I2
4	Week3	13	15	12	*	Late	31	*	I3
Chart

On the Chart sheet the formulas in B2:D4 are returning the values from Week1 I1, Week1 I2, Week1 I3 etc etc. The formulas in G2:G4 are summing all sheets between First & Last that have a value in I1, I2, I3. You can then build your Charts from this data.

This video shows how to create a dynamic chart....
http://www.youtube.com/user/ExcelIsFun#p/search/32/7le-m8YRP6M

Take a look here at a (VBA free) sample file of the above....

hisB.xls


This isn't intended as a complete solution to your situation, it's just a small collection of simple ideas.

Good luck with your project.

Ak

 

[hisB]

Thank you! That is a big help and gives me something to work off of. Thing is... this way is really tallying the average attendance as a whole. I was hoping to be able to have some sorta graph that shows that Bill has perfect attendance, Sam is never there, and Susan clearly has bad PMS because she always misses one day a month. Know what I'm saying?

 

[Akashwani]

Hi,

So are you saying you want a graph to show the results for every Name every week/on going?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Week	Yes	No	Late	*	Running	Totals	*	Indirect Criteria	*	Name	Yes	No	Late
2	Week1	No	5	2	*	Yes	42	*	I1	*	Bill	0	15	0
3	Week2	No	5	2	*	No	54	*	I2	*	Bob	5	4	6
4	Week3	No	5	0	*	Late	24	*	I3	*	Tom	3	10	2
5	*	*	*	*	*	*	*	*	*	*	Tim	15	0	0
6	*	*	*	*	*	*	*	*	*	*	Dan	2	6	7
7	*	*	*	*	*	*	*	*	*	*	Jo	6	6	3
8	*	*	*	*	*	*	*	*	*	*	Joe	5	7	3
9	*	*	*	*	*	*	*	*	*	*	Sam	6	6	3
Chart

To get a running total for each name in the above sample, your Week1 sheet would need to be something like this.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Monday	Tuesday	Wednesday	Thursday	Friday	*	Yes	No	Late	*	Yes	16
2	Bill	No	No	No	No	No	*	0	5	0	*	No	18
3	Bob	Yes	Late	Yes	No	No	*	2	2	1	*	Late	6
4	Tom	Yes	Late	Yes	No	No	*	2	2	1	*	*	*
5	Tim	Yes	Yes	Yes	Yes	Yes	*	5	0	0	*	*	*
6	Dan	No	Late	Yes	No	No	*	1	3	1	*	*	*
7	Jo	Yes	Late	Yes	No	No	*	2	2	1	*	*	*
8	Joe	Yes	Late	Yes	No	No	*	2	2	1	*	*	*
9	Sam	Yes	Late	Yes	No	No	*	2	2	1	*	*	*
Week1

Take a look at the sample file.....
hisB.xls

I'm sorry but this is this best I can suggest.
You will have to see if anyone else has any alternatives for you.

Good luck.

Ak

 

[hisB]

You are AWESOME!!