I have a table with two columns: column A has dates, column B has data. I want to compute the average of the data in B that corresponds to only week (work) days. How can I write it as a formula?

-

migrated from superuser.comAug 7 '12 at 6:32

This question came from our site for computer enthusiasts and power users.

You can use the WEEKDAY functionality to determine the day of the week, and then compute the Average of all days that are not Sunday (1) or Saturday (7).

``````WEEKDAY(number, type)
``````

Returns the day of the week for the given number (date value). The day is returned as an integer based on the type. Type determines the type of calculation: type = 1 (default), the weekdays are counted starting from Sunday (Monday = 2), type = 2, the weekdays are counted starting from Monday (Monday = 1), type = 3, the weekdays are counted starting from Monday (Monday = 0).

The you would need a collection of SumIF statements for each week day 2-6, and a count of them to determine an average.

-
Can you please elaborate? Is it sth like this: =SUMIF(B1:B10, WEEKDAY(A1:A10)>2 & WEEKDAY(A1:A10)<6, 1) ? – EmreA Aug 1 '12 at 23:20
It would probably be easier to add another column with the Weekday parameter, and then us that in your sumif and countif. I made a column C as =WEEKDAY(A1), Assuming the date is in Column A and the data in B: Your query would look like this: =(SUMIF(C1:C40,2,B1:B40)+SUMIF(C1:C40,3,B1:B40)+SUMIF(C1:C40,4,B1:B40)+SUMIF(C1:‌​C40,5,B1:B40)+SUMIF(C1:C40,6,B1:B40))/(CountIF(C1:C40,2)+(CountIF(C1:C40,3)+(Coun‌​tIF(C1:C40,4)+(CountIF(C1:C40,5)+(CountIF(C1:C40,6)))))) – bquaresma Aug 1 '12 at 23:45
Sorry for how messy it is – bquaresma Aug 1 '12 at 23:47
bquaresma: How do I modify the query so that I only take the work days within the last 1 month? – EmreA Aug 7 '12 at 3:04