Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Month End Projection formula?
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 total the amount at the bottom of each department for what has been entered to date, and then I have a daily average, then EOM Proj -- Tasha |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Month End Projection formula?
Hi
When you have Analysis Toolpack activated, then you can use EOMONTH function. Otherwise =DATE(YEAR(A1),MONTH(A1)+n,0) where n is an integer, and n=1 gives the last day of month for date in A1 Arvi Laanemets "sueshe" wrote in message ... 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 total the amount at the bottom of each department for what has been entered to date, and then I have a daily average, then EOM Proj -- Tasha |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Month End Projection formula?
Thanks for the reply, and hoping not to sound too stupid, I installed the
Analysis Toolpack, but not sure what to do with it now? Sorry, I don't understand what to do with the formula you gave me either, not sure where it applies to what I'm trying to get? I have two fields now that I use to calculate the month to date days and number of days in the month. This is an example for the 9th of November for the day before at dayend: Month To Date Days: 8 Days in Month: 30 Days Department Totals 1 1 2 2 3 2 4 0 5 1 6 4 7 2 8 3 total 15 daily avg 1 EOM Proj 22 =dly avg * days in month - mtd days Hope this makes more sense. -- Tasha "Arvi Laanemets" wrote: Hi When you have Analysis Toolpack activated, then you can use EOMONTH function. Otherwise =DATE(YEAR(A1),MONTH(A1)+n,0) where n is an integer, and n=1 gives the last day of month for date in A1 Arvi Laanemets "sueshe" wrote in message ... 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 total the amount at the bottom of each department for what has been entered to date, and then I have a daily average, then EOM Proj -- Tasha |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Month End Projection formula?
Hi
At 1st glance, enter the formula into field, where days in month are displayed. And to calculate last day of month (and the number of remaining days too), you must have some date on your worksheet before. It's difficult for me to give any more detailed instructions, because your sheet layout remains in general a mystery for me. What are Month To Date Days? Where can I find some info, for which month are data meant to (year and month, or even a date representing 1st of the month)? Cell reference for some example, please! "sueshe" wrote in message ... Thanks for the reply, and hoping not to sound too stupid, I installed the Analysis Toolpack, but not sure what to do with it now? Sorry, I don't understand what to do with the formula you gave me either, not sure where it applies to what I'm trying to get? I have two fields now that I use to calculate the month to date days and number of days in the month. This is an example for the 9th of November for the day before at dayend: Month To Date Days: 8 Days in Month: 30 Days Department Totals 1 1 2 2 3 2 4 0 5 1 6 4 7 2 8 3 total 15 daily avg 1 EOM Proj 22 =dly avg * days in month - mtd days Hope this makes more sense. -- Tasha "Arvi Laanemets" wrote: Hi When you have Analysis Toolpack activated, then you can use EOMONTH function. Otherwise =DATE(YEAR(A1),MONTH(A1)+n,0) where n is an integer, and n=1 gives the last day of month for date in A1 Arvi Laanemets "sueshe" wrote in message ... 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 total the amount at the bottom of each department for what has been entered to date, and then I have a daily average, then EOM Proj -- Tasha |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Month End Projection formula?
"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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Month End Projection formula?
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Month End Projection formula?
sueshe wrote:
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() ). What you posted here is sufficient. But sorry, I should have read your previous posting in more detail. Your mistake is obvious there. I will explain in a response to your previous posting. FYI, I do not believe you need the INT() function here. TODAY() returns an integer serial number, not fractional time. That is in contrast to the NOW() function, which does return fractional time. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Month End Projection formula?
sueshe wrote:
Month To Date Days: 8 Days in Month: 30 [....] total 15 daily avg 1 EOM Proj 22 =dly avg * days in month - mtd days As I guessed, your error is that when you compute the daily average, you are truncating the result -- perhaps INT(). This evident by the fact that you compute 22 for the remainder of the month (1*30 - 8). If you had used the exact average, you would have computed 48 or 48.25 (1.875*30 - 8). (Which is still wrong!) Note: In your later posting, you do not show the use of INT() in this computation. If that is true, then perhaps you have formatted the cells to show zero decimal places, and you selected the option to calculate Precision As Displayed (Tools Options Calculation). This is not advisable in your situation, IMHO. Alternatively, you could change the format for the Average cell in order to show some decimal places. Or leave the Average cell as is "for show", but recompute the exact average in the EOM Proj cell. But you have another mistake: you are subtracting "mtd DAYS" (8) instead of "mtd TOTAL" (15). If you had used the latter with your truncated results, you would have computed 15 (1*30 - 15). (Which is still wrong!) The correct computation for EOM Proj is: (15 / 8) * 30 - 15. That is 41.25. You can choose whether to truncate (INT), round (ROUND) or round up (ROUNDUP) the final result -- but not the intermediate average -- depending on which makes the most sense for your process. There is no single right answer. I would tend to round up if the purpose is predict required resources. Better to predict more than less, IMHO. If you need help with the precise formulas, feel free to ask. But looking at your later, you seem to have a good grasp on the Excel formulation. You simply need to apply some of the corrections noted above. HTH. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Month End Projection formula?
Thanks Joe, I will work with your answer and see if that fixes my problem. I
had someone else set this sheet up and should have checked everything to make sure it was right....hind sight ya know!! :) Thanks again! -- Tasha " wrote: sueshe wrote: Month To Date Days: 8 Days in Month: 30 [....] total 15 daily avg 1 EOM Proj 22 =dly avg * days in month - mtd days As I guessed, your error is that when you compute the daily average, you are truncating the result -- perhaps INT(). This evident by the fact that you compute 22 for the remainder of the month (1*30 - 8). If you had used the exact average, you would have computed 48 or 48.25 (1.875*30 - 8). (Which is still wrong!) Note: In your later posting, you do not show the use of INT() in this computation. If that is true, then perhaps you have formatted the cells to show zero decimal places, and you selected the option to calculate Precision As Displayed (Tools Options Calculation). This is not advisable in your situation, IMHO. Alternatively, you could change the format for the Average cell in order to show some decimal places. Or leave the Average cell as is "for show", but recompute the exact average in the EOM Proj cell. But you have another mistake: you are subtracting "mtd DAYS" (8) instead of "mtd TOTAL" (15). If you had used the latter with your truncated results, you would have computed 15 (1*30 - 15). (Which is still wrong!) The correct computation for EOM Proj is: (15 / 8) * 30 - 15. That is 41.25. You can choose whether to truncate (INT), round (ROUND) or round up (ROUNDUP) the final result -- but not the intermediate average -- depending on which makes the most sense for your process. There is no single right answer. I would tend to round up if the purpose is predict required resources. Better to predict more than less, IMHO. If you need help with the precise formulas, feel free to ask. But looking at your later, you seem to have a good grasp on the Excel formulation. You simply need to apply some of the corrections noted above. HTH. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Formula for calculating 12 month rolling period | Excel Discussion (Misc queries) | |||
Weekday formula calculating to end of month | Excel Worksheet Functions | |||
Formula = Today's date + 1 month | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions |