O.K. Try this UDF. It will calculate the average of a range of cells and
exclude both blanks and zeros:

Function avrg(r As Range)
avrg = 0
Count = 0
For Each rr In r
v = rr.Value
avrg = avrg + v
If v < "" Then
If v < 0 Then
Count = Count + 1
End If
End If
Next

If Count = 0 Then Count = 1
avrg = avrg / Count
End Function


--
Gary''s Student - gsnu200732


"lbeemer" wrote:

That won't work, because I have 0s in the range. I need the average without
the days that haven't occured yet included.

"Gary''s Student" wrote:

use AVERAGE rather than SUM.

For example if you will have data in A1 thru A100, then
=SUM(A1:A100)/100 will calculate an average including blanks treated as zeros.
=AVERAGE(A1:A100) will calculate an average excluding blanks.
--
Gary''s Student - gsnu200732


"lbeemer" wrote:

I have a running total for several store's sales numbers that they report
daily. I am trying to add a page for the entire company combined and include
an average sales month-to-date. However when I enter the formula to add all
stores I get a zero value for the dates that have yet to come. Then when I
ask for the average for the whole company, I get a number that includes those
zero values. So far I have just been extending my cell range for the average
equation to include each day as it occurs and not the zeros, but I would like
to just pull the infomation from my daily reports and do the company total on
its own. Any way around this?