Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating month to date
Hi,
I am making a report of my companys results for a month. It is one column where the days revenue is put in every day, and in the cells for the days that are in the future I have the forecasted revenue. At the bottom I have a calculation that gives me the forecasted revenue for the month, but I would like to have a month to date actual. I would like a formula that is howing the average of all the days up until todays date, and that ignores the days that have not occurred? Is there a formula that can be connected to a calendar and only show the actual dates that have occured in the month? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating month to date
.. a formula that is showing the average of all the days up until
todays date, and that ignores the days that have not occurred? Here's one .. Assuming real dates running in A2 down, corresponding figs in B2 down array-entered, ie press CTRL+SHIFT+ENTER to confirm: =AVERAGE(IF((TEXT(A2:A100,"mmmyy")=TEXT(TODAY(),"m mmyy"))*(A2:A100<=TODAY()),B2:B100)) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,000 Files:362 Subscribers:62 xdemechanik --- "Johan" wrote: I am making a report of my companys results for a month. It is one column where the days revenue is put in every day, and in the cells for the days that are in the future I have the forecasted revenue. At the bottom I have a calculation that gives me the forecasted revenue for the month, but I would like to have a month to date actual. I would like a formula that is showing the average of all the days up until todays date, and that ignores the days that have not occurred? Is there a formula that can be connected to a calendar and only show the actual dates that have occured in the month? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating month to date
Hi,
Suppose the dates are in A1:A31 and the values in B1:B31 then =SUM(OFFSET(B1,,,MATCH(TODAY(),A1:A48))) will return the month to date numbers, no array. -- Thanks, Shane Devenshire "Johan" wrote: Hi, I am making a report of my companys results for a month. It is one column where the days revenue is put in every day, and in the cells for the days that are in the future I have the forecasted revenue. At the bottom I have a calculation that gives me the forecasted revenue for the month, but I would like to have a month to date actual. I would like a formula that is howing the average of all the days up until todays date, and that ignores the days that have not occurred? Is there a formula that can be connected to a calendar and only show the actual dates that have occured in the month? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating month to date
Hi Johan
One way =SUMPRODUCT(($A$2:$A$32<=TODAY())*($B$2:$B$32)) -- Regards Roger Govier "Johan" wrote in message ... Hi, I am making a report of my companys results for a month. It is one column where the days revenue is put in every day, and in the cells for the days that are in the future I have the forecasted revenue. At the bottom I have a calculation that gives me the forecasted revenue for the month, but I would like to have a month to date actual. I would like a formula that is howing the average of all the days up until todays date, and that ignores the days that have not occurred? Is there a formula that can be connected to a calendar and only show the actual dates that have occured in the month? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date arithmetic: adding 1 month to prior end of month date | Excel Worksheet Functions | |||
Sort month/date/year data using month and date only | Excel Discussion (Misc queries) | |||
calculating month of present date | Excel Discussion (Misc queries) | |||
Calculating Month To Date Revenue | Excel Worksheet Functions | |||
Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions |