AVERAGEIF question

[Tazzbjs]

I want to average all cells in a row based on the day of the week in another row and I only want it to include cells greater then 0. Essentially for the average returned in R2, the range is A2:N2, if A1:N1 is Sunday, return average for sundays and only include in the average if cell in row 2 is >0. I know this was a complicated explanation, but the best way I could tink to explain it.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Saturday[/TD]
[TD]Sunday[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[TD]Sunday[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[TD][/TD]
[TD][/TD]
[TD]DAY[/TD]
[TD]AVG[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]119[/TD]
[TD]36[/TD]
[TD]236[/TD]
[TD]246[/TD]
[TD]255[/TD]
[TD]254[/TD]
[TD]248[/TD]
[TD]116[/TD]
[TD]33[/TD]
[TD]241[/TD]
[TD]252[/TD]
[TD]265[/TD]
[TD]262[/TD]
[TD]254[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sun[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mon[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Tues[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Wed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Thur[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fri[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sat[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

Start by making sure that the strings in column Q are the same as the strings in row 1. "Sunday", "Monday" etc, or "Sun", "Mon" etc.

(Or is the difference part of the problem that you need to solve?)

Then if you have Excel 2010 (2007?) or later, enter the following into R2 and copy down:

=AVERAGEIFS($A$2:$N$2, $A$2:$N$2, ">0", $A$1:$N$1, Q2)

If AVERAGEIFS does not work for you (#NAME error), let us know.

 

[Special-K99]

So A1:N1 is text, NOT a date with a formatted cell that returns the day of the week?

Does this work?

=AVERAGEIFS(A2:N2,A1:N1,"Sunday",A2:N2,">0")

 

[Tazzbjs]

Thank-you both. The first one worked like a charm.