1. excel stats formula

Hi!
I have been struggling with writing a fomula to track a shelter population. I need to track both on the current day and YTD - how many families are present, the breakdown of the famiies (Parent with 1 child, 2 child, etc), how many of each category (A, C,T, I) and what county they last resided in,
I started by assigning a number to each head of household (the first resident is 1) the mother is A and each child under one is I, between 1-4 is T and 5 or over is C…and for each subsequent family, I assign the next chronological number to the head of household… but I cant figure out how to write the necessary formula – any help would be appreciated! (I attached a sample of my info… )

2. Trish,

Attached is a workbook that takes your inputted data and converts it to a matrix on a second sheet. In this form, all data can be easily tracked with simple Countif statements while being more readily interpreted using the grid. Entering data on either sheet will update each other (synchronize) when you flip back and forth between them. All the formulas for the statistical data are setup including samples of recording lengths of stay (LOS) as well as average LOS. Adding additional family members at a later time will be sorted by their family ID so you do not have to insert them to keep the family together. Data validation was added to easily select the county from a list. Any other symbol for family category other than A, I, T, or C will be assigned O for Other. You may consider using a pivot table if you prefer.
HTH,
Maud

shelter2.png

3. Thanks very much - this is very cool! the only piece I am still missing is determining the composition of the families - ie: 6 families of a mother and one child, 3 famiies of a mother and two children etc.. I appreciate your help getting here, but might you know how to do the last part?

4. At it again Maud? I don't know how you find the time.

cheers, Paul

5. Paul,

This is what keeps me in the dog house (my comfort zone).

Trish,

Ah...now it is obvious why you were coding the relationship. Brilliant! Totally missed the one stat you needed. The obvious way is to append your codes to form a family code. For example, family 3 with a mother, and infant, 2 toddlers, and a child would have the code 3AITTC. It is just a matter of comparing the family codes less the family number to come up with totals. This should be an easy fix. Will work on the for you and add it to the sheet.

Maud

6. Trish,

The missing stats have been added.

shelter3.png