# Averageif and Weekday

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

edit retag close merge delete

Sort by » oldest newest most voted

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

more

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.

more