Determine daily admissions needed based on remaining days and goal

[dgroff]

Hi,

I currently have a spreadsheet that lists the days of the month and census for each day. I also have a total monthly goal of "resident days" (which is all daily census added for the month.)

The key part is each admission adds 1 "resident day" for all remaining days of the month. So if someone were to be admitted today (Feb 13) then it would add 16 resident days to the monthly total, but if they admitted on Feb 28 then it would only add 1 day.

I would like to see what is needed each day on average to reach the monthly goal. This will change each day based on admits and remaining days of the month. I'm realizing this is more of a math problem, but maybe there is an easier way to get these results using excel.

Here are my example numbers:
16 Days Remaining
2578 Current Resident Days
2765 Goal

187 needed to hit Goal. The average would be 11.69 daily censes increase, but that is if they were all added today.


I hope this makes sense. Thank you for any help.

 

[ER_Neha]

Hello , you would like to solve the problem using formula or vba macro?
If formula then please check below, if it works well for you

Book1
	A	B	C	D
1	Current resident days	Goal	remaining	avg
2	2578	2765	16	11.6875
3
Sheet1

Cell Formulas
Range	Formula
D2	D2	=(B2-A2)/C2

 

[Snakehips]

@dgroff Having seen this, I'm curious and slightly confused by the use of the word average.
Are you really wanting some sort of 'average'?
In the above example, 11.687 is the number of admissions required, on that day, to hit the goal.
On 28th Feb, if the same 187 short of goal then, 187 admissions would be required. Similarly
So, by my reckoning, on the 15th Feb, the 'average' number of daily admissions needed over the period 15/2 to 1/2 would be 35.91 Is that really is a useful metric for you?

Or, do you just want the simple solution above provided by @ER_Neha ?
If so then the current resident days and the goal days should be easily referenced in your data.
The 'remaining' days value in C2 above can be made dynamic by using a formula in C2 like:

=N(EOMONTH(TODAY(),0)-TODAY())

Thus the value in D2 will reflect the number of admissions required today to hit goal.

Hope that helps.