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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad we were able to help!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,621
Office Version
  1. 365
Platform
  1. Windows
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)
 

alexfooty

Board Regular
Joined
Dec 30, 2018
Messages
97
Office Version
  1. 2016
Platform
  1. Windows
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,621
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,117
Messages
5,768,213
Members
425,459
Latest member
Danniey

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
Top