Ask Your Question

Averageif and Weekday

asked 2019-03-01 09:29:13 +0200

steveTu gravatar image

Good morning, In calc, I have a couple of columns - one with dates and the other with values. What I want to do is to average the values where the date column equates to a weekend and similarly for a weekday. So something like AVERAGEIF($A$1:$A$10,WEEKDAY(?,2)>5,$B$1:$B$10)

...but how can I get WEEKDAY to work on the cell from the range? Or is there a better way of going about it?


edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-03-01 10:09:34 +0200

updated 2019-03-01 10:10:26 +0200

Hello @steveTu

You can use WEEKDAY function and pass there cell range address as first parameter. This is called an array formula/function. To enter such formula in a cell, you shall use Shift+Ctrl+Enter(or Shift+Cmd+Enter on MAC). Then formula could look like (assuming A2:A29 are dates, B2:B29 values, workweek starts from Monday):


But, for this purpose, it is also easy to use Pivot Table functionality. I have added a sample file with both options. Here and here you cam find my samples about creating Pivot Table, also feel free to search and ask :)

edit flag offensive delete link more

answered 2019-03-01 11:08:47 +0200

steveTu gravatar image

That's grand - thank you. I had a problem with shift-ctrl-enter (Linux) - it didn't make it an array function - but there's a checkbox on the 'fx' pop up window that worked fine.

Thanks again.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-03-01 09:29:13 +0200

Seen: 31 times

Last updated: Mar 01