# Average by Day of the Week

#### alexfooty

##### Board Regular
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

#### Joe4

##### MrExcel MVP, Junior Admin
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
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
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
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.

Replies
8
Views
104
Replies
9
Views
84
Replies
2
Views
80
Replies
1
Views
348
Replies
1
Views
130

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.

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?

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