#1
|
|||
|
|||
How to measure data about a population of clients over a year range
I would like to ask for your help on an Excel/data tracking problem/question that I have. I hope you will be able to clarify this for me. I know you are busy, but this is something that I’ve been looking at with my manager, and I need help to clarify it.
Here is the problem. I have a caseload of approximately 45 clients. That number goes up and down periodically as I close some clients and get assigned new ones to work with. Part of my job is that I’m supposed to measure four “critical indicators”. They are: 1. Whether the client had a face to face meeting with me every month, 2. Whether the client had physical exam for each month, 3. Whether the client had a dental appointment each month, and 4. Whether the client kept their scheduled psychiatric appointment. The idea is to reach a certain percentage each month and for the year. There is a spreadsheet to measure these items which I’ve attached. It does not include any client identifiable information. I believe that they method of measuring these objectives is flawed, and I’m trying to gain an understanding of how to correctly formulate a spreadsheet that can measure these items if possible. My position is that because my client list changes from month to month during the year, that the current method fails to account for those clients. For example: if I have a client that is on my caseload in July that had a physical exam, but then they close in August. Then if I get a new client in October, that had a physical exam in September…that client would not be due for another physical exam until the following September which is after the fiscal year ends. I know that typically I have three or four clients report that they had a physical exam and dental exam each month, but month to month those clients change. Over the course of a year I have more clients than I started with in July, and the population changes, so it is not just the total number of clients that is different during the year. I do not know how to correctly formulate the spreadsheet so that it can measure these items. I’d like to see if you can help me with this if possible. I would greatly appreciate any guidance you can give me in this matter. Even if you can explain whether measurement of this type is possible, and some guidance on how to set up a formula for the cells so I can use it that would be helpful. Thanks very much for your time, JT |
#2
|
||||
|
||||
There really aren't enough data in your workbook for a meaningful analysis. For that, you'd need to capture, for each client, when they 'joined' & 'departed', plus what dates they were due to undertake each activity (or extrapolate when applicable) and whether they actually did so. That way, you could, for example, establish what the July caseload for annual physicals, so you could compare the number undertaken against that - it's pointless comparing an individual month against an annual goal when you don't know how many were due in the month.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to create a slice using a Measure | Anthonys | Excel | 0 | 02-09-2016 02:48 PM |
How to search for a specific year from a table with year range? | Wii | Excel | 0 | 05-05-2015 12:40 PM |
How to calculate a rolling year-to-date percentage by quarter as the year progresses | sleake | Excel Programming | 2 | 04-23-2015 11:51 AM |
Networkdays per fiscal year in a 6 year range | jabnm | Excel | 1 | 10-16-2014 11:51 AM |
Creative Ways for a year-to-year comparison??? | ridonkulous5 | Excel | 1 | 03-23-2011 04:49 PM |