Generating a "running average" updating as new data is entered in the future

[barberjm]

Hello,

I would appreciate help in solving a problem regarding a sales tracking worksheet. I am using Excel 2003. I have created a sheet that intends to measure market share changes for our product sales over a 3 month period. I calculated a baseline from a company generated report and want to use weekly sales numbers for our top 20 customers and average their weekly market share as a total group. We get weekly numbers and everyone will update their top 20 customers themselves. I was hoping to be able to have a running total that shows the cumulative avergae every time a new weeks numbers are input. The problem is when I am getting a #div/0! error becuase we only have 2 weeks of data input and all other weeks are blank. I have tried "IF" formulas and get nothing. How do I get the cumulative average to ignore the cells that have no values yet because they occur in the future?

Thanks for any assistance.

p.s. I have never posted in a forumn and apologize for being to wordy, not specific enough, or if it seems that I am clueless. Thanks.

 

[mikerickson]

There are a couple of ways to do this.
If the cells in question are next to each other (filled cells then blank cells) you could use a Dynamic Named Range that expanded as the new entries are added.

Name: myDynamicNamedRange
RefersTo: =OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A$2:$A$10), 1)

and the formula in a cell =AVERAGE(myDynamicNamedRange)

Another (slower) way would be to use array formulas
=AVERAGE(IF($A$2:$A$10>0, $A$2:$A$10))
entered with Ctrl-Shift-Enter (Cmd+Return for Mac)