Conditional Average????

[jdowski]

Hi Excel Experts,
I am creating a worksheet where an MS Query brings in a table of records on one tab and in another tab I am using Sumproduct to summerize the table based on region and month, (Regions are the headings, Months the rows).
I have inserted blank rows after March, June, September, in order to get quarterly averages. However, the average function doesn't return an accurate number for the 4th quarter because we don't have data yet for November & December. Instead it's just taking the October results and dividing by 3.
First I thought of using some type of count statement in conjunction with the average function but since there's a formula in every cell it wasn't reflecting accuarately. Now I am thinking of some sort of if statement with the average function that would take into account months with no data and adjust accordingly???
The whole point is to have this spreadsheet be completely automated so that all one would have to do is to run the MS Query every month and the Summary tab would update automatically without end user input.

Here is one of my sumproduct formulas that I'm using:
=SUMPRODUCT((FullMonth=$A14)*(Region=$B$1),GLBal)
FullMonth & Region are dynamic named ranges in the source data table and the cell references are the corresponding row & column headings.

Thanks !!

 

[Aladin Akyurek]

On 2002-11-13 16:35, jdowski wrote:
Aladin,
I am trying out your formula and the count part of it does not seem to be working properly. It is not subtracting out the previous summary rows out of the count. For instance, the 12 month average row is showing as 15 when it should be 10. The count for the 9 month average row is showing as 11 when is should be 9. Can you shed some light on this??

Joe,

Can you put the formulas in A5, A9, A13, and A17 instead of in corresponding B-cells? If possible, even in C5, C9, C13, and C17? In C, it would look nicer. You must then not put any numbers in corresponding B-cells.

If you can adopt A or C, the formulas will NOT include the already computed averages in the current cumulative average.


Aladin