Average of Percentage on a Sumary Page

[hodkd]

I have a spread sheet for the 12 months that gives an average of a percentage as each day of the month is added. I then have this average showing on a summary page for each of the months as a "paste link" and the blank cells show as "#DIV/O!" until a number is entered in the indvidual months. I want an average of those percentages as they are entered, but the formula "=AverageD1:D12" only works when all 12 months are entered. Is there some way I can get an average for the months as they are entered on the summary page?

[psumvp]

There are quite a few ways to go about doing this... The best way would likely be to alter your formulas to not show #div/0 when there is no data. You can use the IF function paired with ISERROR to change these results to either "" or 0.

The other way to do this would be to alter your array to not include all 12 values. You can do this by using the offset formula to limit the array to cells that have values. Here are two examples, one counting the non-blank characters and one finding the first instance of #DIV/0 and returing all cells before that.

=AVERAGE(OFFSET(D1,0,0,COUNTA(D1:D12),1))

=AVERAGE(OFFSET(D1,0,0,MATCH("#DIV/0",D1:D12)-1,1))


If you are still stuck, please post a file with an example