I wish I could post my sheet so you could see. Let me see if I can give more
detail. Cell F3 contains the Month to Date Days (=INT(DAY(A3)-1). A3
contains today's date(=TODAY() ). Month to Date days is today's date minus
one because we run our month end as of midnight, so are looking for
yesterday's totals. Cell M3 contains the number of days in the month, so for
November it's 30. Column A gives days of the month numbered down( for this
month 1-30 (cells A8-A38)). Column B has Total Patient Admits for each
day(cells B8-B38)(for example, 14 admits on the first, 19 admits on the 2nd,
9 admits on the 3rd, etc), then admit totals in Cell B39(=sum(B8:B38), Daily
Average is in Cell B41(=B39/F3) and EOM Projection is in cell B42
(B39*M3-F3)-this is the formula I'm having trouble with. Column C has Total
Patient Census days for each day(cells C8-C38), then census totals in Cell
C39(=sum(C8:C38), Daily Average is in Cell C41 (=C39/F3) and EOM Projection
is in cell C42(C39*M3-F3)-this is the formula I'm having trouble with.
Column D starts the department totals, I will use Xray for one example, it is
filled in with total Xray patients for each day, this totals in Cell D39,
Daily Average is in Cell D41 (=D39/F3)and EOM Projection is in cell
D42(D39*M3-F3)-this is the formula I'm having trouble with. Some of the
cells in my EOM formula are showing less than is currently the total or sum
for the month already. Hopefully this makes it a little clearer than mud :o)


--
Tasha


" wrote:

"sueshe" wrote:
I am trying to set up a formula in an Excel spreadsheet to calculate
daily, the end of month projection for total patient visits. I enter manually
a daily amount, and have the formula currently set up to take the daily
average times the number of days in the month and subtract the month
to date days to get the EOM Projection. This seems to work on most
of my departments, however some of them are showing up with a lesser
amount for the EOM Proj than what is currently already totalled for Month
To Date. I can't seem to find out why??? Any suggestions, or maybe a
better formula?

I suggest that you post the formula(s) that you are currently using.
What you describe is the correct algorithm. But the devil is in the
details.

One possible mistake might be in determining the number of days in the
month. That is, perhaps you are using to high a number.

Another possible mistake might be that you compute the daily average as
an integer (INT, ROUND or ROUNDDOWN). If that is rounded down from the
exact average, then you will undershoot the monthly estimate when you
multiply by the number of days.