Hello. Here is a link to a picture of my table:
http://www.flickr.com/photos/23662464@N04/66816202...
I work with children. The children can earn up to 185 points a day. I have my table set to automatically total their points for the week. I want to be able to accurately find the average of all of the children for the week. In the picture you will see "Unit Total" which it just automatically totals all of the children's points for the entire week. Then, I would enter the number of children. Cell I25 is set to divide the unit total by the number of kids.
The issue: If a child does not join the group until mid-week, or a child leaves mid-week, then they have only earned points for say... 3 days. But if I count them in the head count it will give a false average for the unit by bringing the average down since they did not earn a full week of points. It will then look like the unit did very poorly that week when in reality a child could have just left mid-week.
My thought for fixing this, and automatically calculating the average would be to have cell I25 detect whether points have been entered into each day, for each child. If yes, then (1/7) would be added to the number of patients. If nothing is entered then nothing would be added. If a child were on the unit for all 7 days then they would count as "1" because it would be (1/7), 7 times. If they only earned points for 3 days then they would be worth (3/7) toward the total number of children. This would then be divided into the total points for the unit.
Does this make sense? I am clueless as to how to get a cell to "count" each cell as (1/7) if it has >Update:
Alan - your equation is very close to what I need to happen! Thank you. However, sometimes a child is present but earns "0" points. In such a case I would want them to count toward the count. Is there a way to have it differentiate between nothing being in the cell, and a "0" being in the cell?
Answers & Comments
Verified answer
i would adjust your totals a little bit.
i am not sure what the NA column is used for ( so i will ignore it)
- heres a question. theres a difference between 0 and blank. if you plan to leave the cells where a child is not there as a blank, then thats the best way. if you fill in a 0, then it will throw your average off, because Excel will assume someone got 0 points that day, and use it to calculate average.
- change the description "Child Average" to "Unit Average"
- cell C25 gets this formula
=SUM(B5:H24)
- cell I25 gets this formula
=AVERAGE(B5:H24)
- i would Delete "Enter # of Children" and change it to "Full Time Children"
- so in F25 you use this formula
=COUNTIF(K5:K24,"Full Time")
- in the next row, put in the title "Part Time Children"
- then F26 would get this formula
=COUNTIF(K5:K24,"Part Time")
- so looking at the 2 formulas above, you obviously want to use column K for something....even if you have to hide column K
- so in K5 use this formula
=IF(COUNT(B5:H5)=7,"Full time","Part time")
it should give you a full time or a part time. thats good, copy the cell and paste it down to K24
and your counts in F25 and F26 should just work
Ok, as with most complicated problems, there's more than one solution. What I believe would produce the most reasonable results is this:
Instead of your current layout, in row 25, do this...
Unit Total - Good to simply sum the entire grid.
Kid Days Present - set up a formula like this... =SUM(IF(B2:H24>0,1,0)) When you're done typing the formula, you must hit SHIFT+CONTROL+ENTER. (This is called an array formula which needs to be activated with they key sequence.)
Now you have totals of Total Points and Total Kid Days. Simply divide :)