Help with AVERAGE

[gelaro]

I have a sales spreadsheet that has dates in rows and sales locations and income in columns. I want to get an average of a given location only for weeks where sales are reported. But I want the formula to be such that as I add weeks I don't have to change the formula. I have attached a sample sheet.

In the sample, I want B2 to be average of D2 through AA2 but only for columns in which row 1 has a date in it. I don't want to include weeks for which nothing has been collected yet (such as O2). I want the blank columns (e.g., D2, G2, H2, etc.) to be included as $0.00 in the average because those are weeks in which the money is coming in but not yet collected; I collect money every other week in most locations so the numbers are actually two-week numbers and I want a weekly average.

For instance, as of now, B2 should be an average of D2:N2 (which is $21.73). Next week, when I add the sales for 12/5/16, I want to automatically include that column in average of B2.

It seems like it should be something along the lines of this though I can't figure out the syntax: B2 =AVERAGEIF(D1:AA1,"*",D2:AA2) but that gives a #DIV/0! error.

An alternative would be to use C2 (which the total of the row 2) divided by the number of columns in row 1 that have a date in them.

Any ideas on the best way to do this?

Thanks for any help.

[Tony Valko]

This array formula** entered in B2 and copied down:

=AVERAGE(IF(D$1:AA$1<>"",D2:AA2))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[gelaro]

Thanks for that.

[Tony Valko]

You're welcome!

[Khalidngo]

Hi,
Although you've got solution, adding just another (non array)

=SUM(D2:N2)/COUNTIF(D2:N2,"<>0")

Blessing