Need non-array solution to calculate averages per day of the week.

[AndyTampa]

I have a spreadsheet that I'll be updating at work. I won't be the only one updating it. There is a chart in that spreadsheet that calculates an average of all of a set of values for Monday, Tuesday, etc.

Let's say our data is on sheet DATA.

Cells C3:AG3 will be dates (8/1/11, 8/2/11, 8/3/11, etc.) for the entire month.
Cells C5:AG5 will be the data row.
There is no row with the actual day names and I can't add one either.

This workbook is updated every week. The chart was set up with 7 columns (MON, TUE, WED, etc.). The formulas for the averages are simple AVERAGE formulas like =AVERAGE(DATA!$C$3,DATA!$J$3,DATA!$Q$3,DATA!$X$3,DATA!$AE$3)

When they started training me to update this, I noticed that the chart itself never gets updated. Wednesday is always pulling data from the third, tenth, seventeenth, twenty-fourth, and thirty-first of every month.

I've managed to find an array formula that works, [ {=AVERAGE(IF(TEXT($C$3:$AG$3,"dddd")="Monday",$C$5:$AG$5)} ] but arrays are so easily broken when so many people will be using the workbook. One click inside a function box or double-click on a formula cell followed by an oops and enter will break the formula. I'm not allowed to change the basic format of the sheets. That includes using helper columns or cells.

Is there a way to get the AVERAGEIF or AVERAGEIFS formulas to work? Or is there another way?

I've been messing around with:

=AVERAGEIF(C3:AG3,TEXT(C3:AG3,"dddd")="Monday",C5:AG5)

as a straight formula, but I keep getting DIV/0 error. I think it's the criteria portion of the formula that's messing me up.

Any ideas?

 

[AndyTampa]

AVERAGEIFS, AVERAGE(IF(....)), or DAVERAGE are really better alternatives...

They aren't better alternatives because I'm not entering the formulas as arrays.

Why?

As stated earlier, this is a work product and the other people working on the same files are not advanced Excel users.