# Help with Averaging a total amount based on the day of the week

#### rdrjr

##### New Member
I have a table that I enter the date such as 01-01-21 and the amount in a total column. I need to average the total amount for each day of the week. I have tried using the following formula but it returns the #Value error.

=AVERAGE(IF(Earnings[DATE]=2,Earnings[TOTAL],"")) 'my thinking is this would average all totals for the Mondays.

#### rdrjr

##### New Member
Yes, but what do you see if you change the format to General?
They show a 5 digit number whenever I change them to general
Yes, but what do you see if you change the format to General?

#### Attachments

• Screenshot_2.png
36.9 KB · Views: 2

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### rdrjr

##### New Member
Yes, but what do you see if you change the format to General?

They show a 5 digit number when changed to General

#### Attachments

• Screenshot_2.png
36.9 KB · Views: 1

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=AVERAGE(FILTER(Earnings[Total],TEXT(Earnings[Date],"ddd")="Mon"))``

#### rdrjr

##### New Member
Excel Formula:
``=AVERAGE(FILTER(Earnings[Total],TEXT(Earnings[Date],"ddd")="Mon"))``

Filter is invalid, does show FILTERXML

#### Attachments

• Screenshot_3.png
14.8 KB · Views: 3

#### Fluff

##### MrExcel MVP, Moderator

In that case you don't have the latest updates, so the formula in post#2 will need to be confirmed with Ctrl Shift Enter, rather than just Enter.

#### rdrjr

##### New Member
In that case you don't have the latest updates, so the formula in post#2 will need to be confirmed with Ctrl Shift Enter, rather than just Enter.

I am not sure why it is not up to date since I thought I was using the online version. However you are correct and using CTRL + SHIFT + ENTER worked. Thank you for your help.

#### Fluff

##### MrExcel MVP, Moderator
Glad it's sorted & thanks for the feedback.

Judging by your screenshots, you are using the desktop app & not Excel online.

Replies
1
Views
57
Replies
1
Views
84
Replies
3
Views
239
Replies
3
Views
102
Replies
3
Views
45

1,141,309
Messages
5,705,672
Members
421,404
Latest member
Mikecollo

### 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.

### Which adblocker are you using?

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

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