Ask Your Question
1

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?

Thanks

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

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

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

edit flag offensive delete link more
0

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

Stats

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

Seen: 64 times

Last updated: Mar 01