Averaging Weekday Info

[AventAClue]

Hi All,
I'm new at this and looking for some assistance if anyone can help, please.
I've found some info on averaging and tried it but haven't been able to get it working properly.
Using "Weekday" in Excel has me a little stumped. I've tried formatting the cells to date and going from there but still can't seem to get it right, so hopefully can someone please set me straight?

I have a spreadsheet which has a row of data that I wish to average, for info given on weekdays only.
The cell range length grows as each week has it's data entered (up to 3 months worth), so it needs to be able to average only the cells that have valid info in it. (ie not a zero)

Cells B1 through to CW1 contain Mon, Tue, Wed, Thu, Fri, Sat, Sun, Mon, Tue etc (Mon in cell B1, Tue in C1, Wed D1,etc, over and over again for each day of the week. (Should I format these cells any particular format?)

Cells B11 through to CW11 contain the result of summing the cells above it. (so it has a formula in it summing the above cells, and therefore displays a zero if the data above hasn't been entered)
It is these results that I wish to get the average of (so only if it's not a zero, and only if it's a weekday).


ie:
Cols B C D E F G H I J K
1 MON TUE WED THU FRI SAT SUN MON TUE...and so on
2 124 126 148 171 115 122 145 126 139........ and so on
3 105 124 136 140 106 141 121 119 145
4 136 120 118 105 142 138 135 141 126
...
...
9 143 150 129 119 131 129 126 141 139
10
11 508 520 531 535 494 530 527 527 549 0 0 0 ....

Hoping that is understandable, and someone can help please

Thanks to all who can offer assistance
AventaClue

 

[AlphaFrog]

=AVERAGE(IF((B1:CW1<>"SAT")*(B1:CW1<>"SUN")*(B11:CW11>0),B11:CW11,""))

This is an array formula. When you enter in the formula, use Ctrl+Shift+Enter. Excel will automatically surround the formula with {braces}.

 

[AventAClue]

Thankyou very much AlphaFrog !!

That works great.

When you say it is an array formula, what does that actually mean?
I did notice if I simply hit enter it gave a different result. What is it actually doing that makes it different?

 

[AlphaFrog]

You're welcome.

Array Formulas
http://www.cpearson.com/excel/ArrayFormulas.aspx

 

[AventAClue]

Thanks once again AlphaFrog

I'll go have a read of that. It looks quite informative.

Thanks all
Avenoidea.