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?

 

[T. Valko]

Thank you. I'll try that in the morning.

Is there a reason you didn't make the row references absolute as well?

I don't see any need to make the row ref absolute.

As far as I know, you're only copying the formula across a row so we need to make the columns absolute.

 

[AndyTampa]

I wanted to make them absolute in case someone came along and decided to add a row on the page where the formula is.

It's done and the formula worked like a charm. I still don't understand it, but I'm absolutely thrilled it's done.

Thank you so much for your help.

 

[T. Valko]

The formula worked like a charm. I still don't understand it, but I'm absolutely thrilled it's done.

Thank you so much for your help.

You're welcome. Thanks for the feedback!

 

[AndyTampa]

This has been working perfectly for me and I have looked into the link above to get a grasp on the SUMPRODUCT function. I believe I understand it.

Now that link gave me the idea that I could use AVERAGEIFS, but the criteria are not working.

I tried =AVERAGEIFS('Tab1'!C9:AG9,'Tab1'!C9:AG9,">0",UPPER(TEXT('Tab1'!C1:AG1,"ddd")),C2) but it didn't work. If I eliminate the 2nd criteria and range, it will work across all days. Somehow the text calculated on the date does not match the text in the single referenced cell.

I've been searching for a cause, but can't find anything so far. Is it allowable to use functions around a range as in the UPPER(TEXT('Tab1'!C1:AG1,"ddd")) above?

For those who would ask why I want to change it, I don't right now. I'm trying to educate myself.

 

[T. Valko]

This has been working perfectly for me and I have looked into the link above to get a grasp on the SUMPRODUCT function. I believe I understand it.

Now that link gave me the idea that I could use AVERAGEIFS, but the criteria are not working.

I tried =AVERAGEIFS('Tab1'!C9:AG9,'Tab1'!C9:AG9,">0",UPPER(TEXT('Tab1'!C1:AG1,"ddd")),C2) but it didn't work. If I eliminate the 2nd criteria and range, it will work across all days. Somehow the text calculated on the date does not match the text in the single referenced cell.

I've been searching for a cause, but can't find anything so far. Is it allowable to use functions around a range as in the UPPER(TEXT('Tab1'!C1:AG1,"ddd")) above?

For those who would ask why I want to change it, I don't right now. I'm trying to educate myself.

No, you can't "manipulate" an array like that in any of the "IF(S)" functions.

You probably don't need the UPPER function, either.

You would have to use an array formula** to do this.

=AVERAGE(IF('Tab1'!C9:AG9>0,IF(TEXT('Tab1'!C1:AG1,"ddd")=C2,'Tab1'!C9:AG9)))

** 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.

We have to "manipulate" the array Tab1!C1:AG1 with the TEXT function to test for the weekday. So, we can't use any of the "IF(S)" functions. We need the array formula.

 

[AndyTampa]

I guess that's why the SUMPRODUCT is so special. Thanks. I'm staying away from entering any array formulas on these work worksheets.

I was applying the logic used in the SUMPRODUCT page referenced above to the ????IFS commands. The SUMIFS and COUNTIFS were mentioned but I must have missed something subtle among all the other stuff. I had tried to use a range formula that would give me a true/false result too, but that also failed. I'll go back and re-read the page. Thanks again.

 

[T. Valko]

I guess that's why the SUMPRODUCT is so special. Thanks. I'm staying away from entering any array formulas on these work worksheets.

I was applying the logic used in the SUMPRODUCT page referenced above to the ????IFS commands. The SUMIFS and COUNTIFS were mentioned but I must have missed something subtle among all the other stuff. I had tried to use a range formula that would give me a true/false result too, but that also failed. I'll go back and re-read the page. Thanks again.

If you want a non-array entered formula:

=SUMPRODUCT(--(Tab1!C9:AG9>0),--(TEXT(Tab1!C1:AG1,"ddd")=C2),Tab1!C9:AG9)/SUMPRODUCT(--(Tab1!C9:AG9>0),--(TEXT(Tab1!C1:AG1,"ddd")=C2))

 

[AndyTampa]

If you want a non-array entered formula:

=SUMPRODUCT(--(Tab1!C9:AG9>0),--(TEXT(Tab1!C1:AG1,"ddd")=C2),Tab1!C9:AG9)/SUMPRODUCT(--(Tab1!C9:AG9>0),--(TEXT(Tab1!C1:AG1,"ddd")=C2))

Right. That's the one I said was working perfectly that you gave me back in August. I was just learning how the SUMPRODUCT function worked when I read about the ???IFS functions. I thought the principle would be about the same and couldn't figure out why it wasn't working. You've answered that though. Thanks again.

 

[T. Valko]

Right. That's the one I said was working perfectly that you gave me back in August. I was just learning how the SUMPRODUCT function worked when I read about the ???IFS functions. I thought the principle would be about the same and couldn't figure out why it wasn't working. You've answered that though. Thanks again.

Good deal. Thanks for the feedback!

 

[Aladin Akyurek]

I guess that's why the SUMPRODUCT is so special.

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

I'm staying away from entering any array formulas on these work worksheets...

Why?