Saturdays not working with AVERAGE

[S.Peto]

Hi.

I am working on a sheet to help with my business.

I am trying to find averages for days of the week.

Column A has dates.

Column F has numbers (sum of columns b:e)

then i am using this: =AVERAGE(IF(TEXT(A1:A9998,"ddd")="sun",F1:F9998)) and changing sun to mon, tue, wed, thu, fri and it works perfectly, but when I change it to sat i get a VERY low number

I tried it doing it like this too =AVERAGE(IF(WEEKDAY(A1:A9999)=7,F1:F9998)) 1, 2, 3, 4, 5, 6 work correct, but when I change to 7 it gives me #N/A

What am I missing?

[Jonmo1]

Blank cells will be treated as Saturday by the TEXT function.

Add a criteria for Not Blank

=AVERAGE(IF(TEXT(A1:A9998,"ddd")="sat",IF(A1:A9998<>"",F1:F9998)))

[Tony Valko]

Empty cells will evaluate as weekday Saturday!

Test that the cells do in fact contain dates:

=AVERAGE(IF(ISNUMBER(A1:A9998),IF(TEXT(A1:A9998,"ddd")="sat",F1:F9998)))

Still array entered.

[S.Peto]

Thank you guys!

I couldn't figure that out..

much appreciated!

[Jonmo1]

You're welcome.

[Tony Valko]

You're welcome. Thanks for the feedback!

[S.Peto]

While I have you guys here, another question.

I think the same issue is happening with months/years..

I am using: =SUMPRODUCT((MONTH(A1:A9998)=9)*(YEAR(A1:A9998)=2016)*(F1:F9998)) and I am getting an error, probably need the same not blank criteria IF(A1:A9998<>"",F1:F9998) in it somewhere, right?

[Tony Valko]

One way...

=SUMPRODUCT(--(TEXT(A1:A9998,"myyyy")="92016"),F1:F9998)

[S.Peto]

Good enough for me.

Thanks again.

Have a good week.

[Tony Valko]

You're welcome!