Calculating month to date

[Johan]

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?

 

[Max]

.. 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(),"mmmyy"))*(A2:A100<=TODAY()),B2:B100))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik
---

 

[ShaneDevenshire]

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.

 

[Roger Govier]

Hi Johan

One way
=SUMPRODUCT(($A$2:$A$32<=TODAY())*($B$2:$B$32))