Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    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… )
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    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
    Attached Files Attached Files
    Last edited by Maudibe; 2014-06-30 at 10:53.

  3. #3
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts
    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. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,180
    Thanks
    47
    Thanked 983 Times in 913 Posts
    At it again Maud? I don't know how you find the time.

    cheers, Paul

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    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. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Trish,

    The missing stats have been added.

    shelter3.png

    Please let me know if any additional stats need to be added or adjustments made.

    Maud

    Update: File temp removed for additional coding
    Last edited by Maudibe; 2014-07-03 at 19:25.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •