Currently Being ModeratedFeb 26, 2013 5:59 AM (in response to Gwyn Plem)
Welcome back, Gwen,
The key to this question is similar to the subject of your last one - Countif.
Here's an example of what you might do:
I'll give you he expressions in the Column D Header Cells first:
D1: =COUNTIF(D, TRUE )
D2: =COUNTIFS($B, "F", D, TRUE)
D3: =COUNTIFS($B, "M", D, TRUE)
D4: =IFERROR(AVERAGEIF(D, TRUE, $C), "-")
Select that group of cells and Fill Across to the Right.
If you want other statistics, you can study the expressions and modify them to get your preferences.
N6 is the first of a set of Countif statements that counts the number of checked boxes for each student. It is Conditionally formatted to set the color to orange if the student isn't assigned yet, green if assigned and red if assigned more than once.
Currently Being ModeratedFeb 26, 2013 7:03 AM (in response to Gwyn Plem)
This may be a good start:
- Create a table titled "Student List" where you have three (or more) columns titled "ID", "Name" and "Gender"
- Create a table titled "Teacher List" where you have Five (or more) columns titled "ID", Name", "Total", "Girls" and "Boys"
- Create a table titled "Master List" where you have six (or more) columns titled "Student ID", "Already Included", "Student Name", "Gender", "Teacher ID", and "Teacher Name"
For the table "Student List" you have to fill in this information.
For the table ""Teacher List you have the fill in the first two columns.
For the table "Master List" enter the formulas as follows (Master list is rught-most table):
B2=IF(IFERROR(MATCH(A2,$A$1:A1,0), -1)>=0, "ALREADY ENTERED", "")
C2=IF(ISBLANK(A2), "", VLOOKUP(A2,Student List :: A:C, 2, 0))
D2=IF(ISBLANK(A2), "",VLOOKUP(A2,Student List :: A:C, 3, 0))
F2=IF(ISBLANK(E2), "",VLOOKUP(E2, Teacher List :: A:B, 2, 0))
You fill in columns A and E
select cells B2 thru D2 and fill down as needed
select F2 and fill down as needed
For the table "Teacher List":
D2=COUNTIFS(Master List :: D, "=FEMALE", Master List :: E, "="&A2)
E2=COUNTIFS(Master List :: D, "=MALE", Master List :: E, "="&A2)
select C2 thry E2 and fill down as needed
To shade the Teacher table I suggest two shader tables (on as wide as columns A, B and C of the Teacher list, and the other as wide as columns D and E.
The shader table on the left (mostly red) is titled "Teach Shader" and is set up like:
A2=COUNTA(Student List :: A)/COUNTA(Teacher List :: A)-Teacher List :: C2
select A2 and fill down as needed (it should have the same number of rows as the yeacher list
now select A2 thru A11 and open the conditional format rules and set up like:
The student shader is on the right (mostly green) and is set up like:
A2=ABS(Teacher List :: D2-Teacher List :: E2)
select A2 and fill down as needed. Apply similar shading as in the teacher shader.
now select both shader tables and send to back. Selec the teacher List and make the fill color "none":
now select the two shader tables and slide them under the "Teacher List" and align.
It should look like this when complete:
Currently Being ModeratedFeb 26, 2013 7:28 AM (in response to Gwyn Plem)
I realized on rereading my suggestion that I never got around to giving you the formula for the E column, the number of assignment entries for each student. That column is just there to remind you of any neglected students and to alarm you if you make a double-assignment. The formula in N6 is:
Fill Down to your last student's row.
I know you could have figured out that one on your own!
Here's the conditional format for the E cells:
Good luck with your latest project.
Currently Being ModeratedFeb 27, 2013 2:21 AM (in response to Jerrold Green1)
Jerry, Thank you again for your response. Apologies for asking a follow-up question - seeing as your reply was so detailed - I hope you do not mind. You are correct also - all my troubles stem from COUNTIF - I cannot seem to fathom this expression!
I am trying to work out the age average etc - and have adapted (dare I say this?!) your reply somewhat beacuse I wanted your 'style' of age. I had the pupils DOB in Column C, so I added a column D with the expression =DATEDIF(DOB,TODAY(),"Y")&"y, "&DATEDIF(DOB,TODAY(),"YM")&"m"
It does return a value such as 10y, 2m - great - this is somewhat similar to yours - but not quite the same - as yours was a decimal value - is this the basis for my troubles?......
Beacuse the Average Age express =IFERROR(AVERAGEIF(T1, TRUE, $Age), "-") merely gives me a '-'
Could you please tell me what I have done wrong? Thank you again.
Currently Being ModeratedFeb 27, 2013 4:22 AM (in response to Gwyn Plem)
Please send me the file in your screen shot, and I'll send it back to you in working condition, and I'll post the explanation of what I did here. I have a solution in mind, but it will be much easier if I don't have to recreate your layout.
You can't average your format for age, it's a string, not a duration value, and that's the rub. I will leave your display as is, and add a hidden column with a duration that is average-friendly.
Currently Being ModeratedFeb 27, 2013 4:31 AM (in response to Jerrold Green1)
Thank you so much Jerry!
I was unsure how to add a file here so here is a link to my dropbox
Is this ok?
Currently Being ModeratedFeb 27, 2013 8:58 AM (in response to Gwyn Plem)
You can grab the revised document here.
I added a column to calculate the age of each student in days, Duration. This averaged with no problem.
The expression for Age (Days) is:
=TODAY()-C where C is your DOB column.
The average age calculation for Teacher 1 is:
=DATEDIF(TODAY()-AVERAGEIF(F, TRUE, $D), TODAY(), "Y")&"y "&DATEDIF(TODAY()-AVERAGEIF(F, TRUE, $D), TODAY(), "YM")&"m"
If you could tolerate the average age display in simple Weeks or Days, it would be a much shorter expression, but if you don't mind the overhead, it's not a problem.
Currently Being ModeratedFeb 27, 2013 12:10 PM (in response to Jerrold Green1)
Thank you Jerry. Works absolutely great. I appreciate it a great deal.
I am using it now - but a thought has struck me when I noticed an error in my class organization. I realize that you have given a lot of time but I will ask quickly - (but PLEASE ignore if you want!!) - how difficult is it to calculate the age range in a class. One of my organizational criteria's is that the age range should be no more than 8 months in some classrooms. I can just about figure out the range between two fixed dates - but it is beyond me when there is a range of ages!
Anyway - hopefully not being too forward in asking another question - and possibly deserves it's own thread?
Currently Being ModeratedFeb 27, 2013 2:44 PM (in response to Gwyn Plem)
I added a column in the Main table for a Class-Student identifier. I collected the ages by class (teacher) in an auxiliary table using a lookup of the Class-Student ID.
Here's the modified Main table:
The Column E expression is:
=RIGHT("0"&MATCH(TRUE, G6:O6), 2)&"-"&RIGHT("0"&COUNTIF(OFFSET($F$5, 0,MATCH(TRUE, G6:O6), ROW()-4),TRUE), 2)
The Age Range expression for Teacher 1 is:
Ages by Class :: B44 a simple reference to the result in the Aux table.
The Aux Table "Ages by Class" looks by this:
The expressions in the Teacher 1 columns are as follows.
Age in days:
=IFERROR(INDEX(Main, MATCH(RIGHT("0"&COLUMN()-1, 2)&"-"&RIGHT("0"&ROW()-1, 2), Main :: $E,0), 4), "")
You can pick up a copy here.
As I wrote it, the document will handle up to 99 classes and up to 99 students per class.
Currently Being ModeratedFeb 28, 2013 5:22 AM (in response to Gwyn Plem)
It's a pleasure to work with you on your well-written problem statements that have obviously practical application. I'll leave the documents on the Dropbox site for about a week for anyone who whant to see the solution in full context. After that I will remove them to recover the storage space.