Excel Question

[Jason Betts]

Hi guys, I have another excel question. I have a list of dates running down the B column, and I want a formula for cells at the end of each column after B that averages the totals for each value in that column that occurs on a certain weekday. For example, values in column C count how many trips were made that day. I want a formula that checks rows 2 to 201 of column C, and if the date in that row's corresponding B cell is, say, Monday, then add it to a list of all C values that took place on a Monday, then average them. That way I can see at the bottom how many trips occurred on an average Monday. 

 

Something like:

C2:C201 If B(current cell)=Monday add value to string1, add all values in string1 and divide by number of values.

 

I know my coding there is pretty bad, but you get the idea. A formula that checks if the value in a given cell was placed on a given day of the week based on dates from an adjacent cell. I Googled this before I wrote the post and I found methods of obtaining a day of the week from a stored date, and I know there is a way to average a column with that C2:C201 thing but I don't understand Excel well enough to put together a working formula that does everything cleanly in a single cell. 

[Jason Betts]

Googling around I found this formula:

=AVERAGE(IF(WEEKDAY(B2:B201)=2,C2:C201))

 

But it doesn't seem to work. 

[LogicalDrm]

4 hours ago, Jason Betts said:

Googling around I found this formula:

=AVERAGE(IF(WEEKDAY(B2:B201)=2,C2:C201))

 

But it doesn't seem to work. 

I think you need to move operators around. For something like =IF(WEEKDAY(B2:B201)=2,AVERAGE(C2:C201)). I'm not sure. But looking how IF function works, that sounds more like working solution.

[AlwaysACylon]

How fixed is your data structure?

Is it acceptable to, for example, have the column B set as "Custom", so that it has the day name in the cell as well as the date?

Example:

Saturday 01 Oct 16

Sunday 02 Oct 16

Monday 03 Oct 16

Tuesday 04 Oct 16

Wednesday 05 Oct 16

Thursday 06 Oct 16

Friday 07 Oct 16

Saturday 08 Oct 16

Sunday 09 Oct 16

Monday 10 Oct 16

Tuesday 11 Oct 16

Wednesday 12 Oct 16

Thursday 13 Oct 16

Friday 14 Oct 16

Saturday 15 Oct 16

[Jason Betts]

16 hours ago, LoGiCalDrm said:

I think you need to move operators around. For something like =IF(WEEKDAY(B2:B201)=2,AVERAGE(C2:C201)). I'm not sure. But looking how IF function works, that sounds more like working solution.

That also does not work. I'm not sure why.

[Jason Betts]

14 hours ago, AlwaysACylon said:

How fixed is your data structure?

Is it acceptable to, for example, have the column B set as "Custom", so that it has the day name in the cell as well as the date?

Example:

Saturday 01 Oct 16

Sunday 02 Oct 16

Monday 03 Oct 16

Tuesday 04 Oct 16

Wednesday 05 Oct 16

Thursday 06 Oct 16

Friday 07 Oct 16

Saturday 08 Oct 16

Sunday 09 Oct 16

Monday 10 Oct 16

Tuesday 11 Oct 16

Wednesday 12 Oct 16

Thursday 13 Oct 16

Friday 14 Oct 16

Saturday 15 Oct 16

I could do that but I'd really rather not if I can avoid it.

[Jason Betts]

18 hours ago, LoGiCalDrm said:

I think you need to move operators around. For something like =IF(WEEKDAY(B2:B201)=2,AVERAGE(C2:C201)). I'm not sure. But looking how IF function works, that sounds more like working solution.

I found that this formula works:

=AVERAGE(IF(WEEKDAY(B2:B201)=2,C2:C201))

 

I have to press shift + ctrl + enter instead of just enter.

 

What I don't understand is how to tell it which day I want to use. Somewhere in there it says which day, but I don't know what part is saying what day of the week it's looking for.

[LogicalDrm]

1 hour ago, Jason Betts said:

What I don't understand is how to tell it which day I want to use. Somewhere in there it says which day, but I don't know what part is saying what day of the week it's looking for.

Its number 1-7 for days. 1 being Monday, 2 being Tuesday and so on.