3

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?

3

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

from: https://support.google.com/docs/bin/static.py?hl=en&topic=25273&page=table.cs&tab=1240285

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

| improve this answer | |
  • 1
    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)+(CountIF(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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.