Solved: Calculate "Current Month Average"

[IgnBan]

I?m in the process of putting together a constant improvements chart and I need help on making a ?Current Month Average? of a daily data input table. This table will be update every day (is a name range update by a macro appending data at the end of each day)

In this table I have several columns but as an example I will use only 3;
1st column date, 2nd Score 3rd ?Current Month Average Score?.

How can as the current day data is appended it in the last empty cell of the Date column trigger a code to calculate the current month average of the ?Score? in the ?Current Month Average Score?? adjacent cell?

My idea is that when my macro appends the end of the day data to column C and D, adjacent E will populate with the current monthly average

Attached a sample workbook.

Thanks in advance for any input:thumb

[IgnBan]

I have cross posted this question at EE, here is the link
http://http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23660694.html

I will post the answer of either site as I get one.

IgnBan

[Bob Phillips]

=AVERAGE(IF(MONTH(C8)=MONTH($C$8:$C$154),$D$8:$D$154))

as an array formula, so commit with Ctrl-Shift-Enter, not just Enter

[IgnBan]

Thank you! EL EXLD... Bob when I expand the range for the formula to let say $D$8:$D$32000, will this make the worksheet slower? how does the lenght of the range afect the performance of the workbook. (this particualr one I got it to collect data with a user form.

Thanks

[Bob Phillips]

Indeed it will slow it down, 32000 rows will be significant.

The thing I don't get is why you need it every row, wht not create a table of months and just do the calculation once per month there.

[IgnBan]

My intent is to use it in a "Constant Improvement Table" that will be showing the different scores compare to the previous day. Now that you mention the Monthly , how do you create a monthly of the daily? Do i set up a manually "months" headers and then use the formula you provided?

[IgnBan]

An answers from EE


=SUM(IF(TEXT(C$7:C8,"mmm-yy")=TEXT(C8,"mmm-yy"),D$7:D8,0))/SUM(IF(TEXT(C$7:C8,"mmm-yy")=TEXT(C8,"mmm-yy"),1,0))

[Bob Phillips]

If you are going to post elsewhere, especially there, please say so at the start, and save me from wasting mty time. I don't want to bother answering cross-posted threads.

[IgnBan]

XLD I did it at the beginning, I'm just following the rules an posting the links. And you didn't waisted you time, but formula is the one I'm using. I don't usually do this, this is the fist time I cross post and I followed the rules.

Now changing the topic, If I use the same formula that you provided with a lenghtly range (32,000) in a small table where I will have only , let say two years of Months 24 cells, even if the range is that long it wont affect the performance?

[Bob Phillips]

I did waste my time because if you had posted that in the first post I would have ignored the threads. As it was you posted the cross-post the link just before I replied, whilst I was preparing my answer, and hence I missed it.

You may have followed the letter of the rule, but I just do not want to play in cross-posting games.