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

rdrjr

New Member
Joined
Sep 28, 2019
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 28, 2019
Messages
29
Office Version
  1. 365
Platform
  1. Windows

Attachments

  • Screenshot_2.png
    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
Joined
Sep 28, 2019
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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
    Screenshot_2.png
    36.9 KB · Views: 1

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Excel Formula:
=AVERAGE(FILTER(Earnings[Total],TEXT(Earnings[Date],"ddd")="Mon"))
 

rdrjr

New Member
Joined
Sep 28, 2019
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Excel Formula:
=AVERAGE(FILTER(Earnings[Total],TEXT(Earnings[Date],"ddd")="Mon"))

Filter is invalid, does show FILTERXML
 

Attachments

  • Screenshot_3.png
    Screenshot_3.png
    14.8 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Sep 28, 2019
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
Glad it's sorted & thanks for the feedback.

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

Forum statistics

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