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 !!
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 !!