Average by Day of the Week

alexfooty

Board Regular
Joined
Dec 30, 2018
Messages
97
Office Version
  1. 2016
Platform
  1. Windows
Hi
In the following I want to know the average for each day of the week based on the dates in Column B and the values in column C - but ignoring any blank cells
The actual columns are much longer than this and run into several months - this is just an example.
A formula would be greatly appreciated. Thank you
Image 1.png
 
You are welcome.
Glad we were able to help!
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Jasonb75
Would your formula work if the months were in separate columns as follows
Yes ,if you changed the formula to something like this, where I2 refers to the cell with 'Mon' in the results table.
Excel Formula:
=AVERAGEIFS($C$2:$G$15,$A$2:$E$15,I2)
 
Upvote 0
Jasonb75
That formula works brilliantly, thank you. I've set it up with several columns per month over 6 months and everything works faultlessly.
I may not be good at explaining things but this Forum never fails me. Thank you to you all.
 
Upvote 0
I may not be good at explaining things
That is quite common on the forum. It helps us that you read the questions that we asked and provided the details we needed in order to come up with a solution for you :)
Many people give us a bad explanation then expect us to read their minds to find the information we need, unfortunately for them most of us don't have crystal balls to consult.
That formula works brilliantly
Something I should point out, the formula doesn't identify the columns, only the content. It looks for the criteria, then averages anything that is 2 columns to the right (based on the offset between the 2 ranges). Using your last screen capture as an example, if you entered a weekday name into column D then the date serial in column F for the same row would be included in the average as well.

While this shouldn't be a problem with what you have shown us, if you used the same method in another sheet where the criteria could be found in columns that should not be included then you will likely see incorrect results.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top