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?
Thanks
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):
=AVERAGEIF(IF(WEEKDAY(A2:A29;2)<6;"BD";"WE");"WE";B2:B29)
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 
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.