Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-13-2016, 06:50 AM
jet4396 jet4396 is offline How to measure data about a population of clients over a year range Windows 8 How to measure data about a population of clients over a year range Office 2013
Novice
How to measure data about a population of clients over a year range
 
Join Date: Sep 2016
Posts: 1
jet4396 is on a distinguished road
Default 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
Attached Files
File Type: xlsx John Thomas Critical Indicator Report 2016-2017.xlsx (15.4 KB, 8 views)
Reply With Quote
  #2  
Old 09-22-2016, 01:35 AM
macropod's Avatar
macropod macropod is offline How to measure data about a population of clients over a year range Windows 7 64bit How to measure data about a population of clients over a year range Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


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
How to measure data about a population of clients over a year range 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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:57 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft