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

[rdrjr]

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]

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?

 

[rdrjr]

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

They show a 5 digit number when changed to General

 

[Fluff]

Ok, how about

=AVERAGE(FILTER(Earnings[Total],TEXT(Earnings[Date],"ddd")="Mon"))

 

[rdrjr]

Ok, how about

=AVERAGE(FILTER(Earnings[Total],TEXT(Earnings[Date],"ddd")="Mon"))

Filter is invalid, does show FILTERXML

 

[Fluff]

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]

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]

Glad it's sorted & thanks for the feedback.

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