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?