Formula for average of cells that have blanks

[Security Dave]

I am looking for the formula that would help me get an average of cells that
have sum formulas. Some of the cells are blank due to no data being entered
in the sum cells. It is a monthly tally sheet so instead of getting the
average for May 1st thru May 13th, I am getting the average of May 1st thru
May 31st causing a lower total average.
My current formula for average is
=AVERAGE(T10:T40)
And it is figuring the average of cells that have this formula that is blank
=Sheet1!N21+Sheet1!N68
Any suggestions?

 

[Bernie Deitrick]

Dave,

If you want the average through today (the 14th) use:
=AVERAGE(OFFSET(T10:T40,0,0,DAY(TODAY()),1))

If you want the average through yesterday (the 13th) use:
=AVERAGE(OFFSET(T10:T40,0,0,DAY(TODAY()) -1,1))

HTH,
Bernie
MS Excel MVP