Tell me more ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question

migrated from superuser.com Aug 7 '12 at 6:32

1 Answer

up vote 2 down vote accepted

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.

share|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)+(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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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