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.

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=AVERAGE(IF(WEEKDAY(Earnings[Date],1)=2,Earnings[Total],""))

 

[rdrjr]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=AVERAGE(IF(WEEKDAY(Earnings[Date],1)=2,Earnings[Total],""))

Sorry about that, I haven't posted in quite a while. I have updated it now, I use 365. I have changed the formula but it still returns the #Value error.

 

[Fluff]

Is the Date column a formula that sometimes returns ""

 

[rdrjr]

Is the Date column a formula that sometimes returns ""

No, the date column is entered by me each day. Though it would save time if it were auto filled by a formula. I just enter it to see at a glance what I have done and what I have not.

 

[Fluff]

In that case are you sure your dates are actual dates?
If you format the Date column as General are all the values 5 figure numbers?

 

[rdrjr]

In that case are you sure your dates are actual dates?
If you format the Date column as General are all the values 5 figure numbers?

They are date format

 

[rdrjr]

I got it working now but I had to add 2 new columns and completely change the formula to do so. I was hoping to fix the original formula to simplify.

 

[rdrjr]

I got it working now but I had to add 2 new columns and completely change the formula to do so. I was hoping to fix the original formula to simplify.

I would still appreciate the help to simplify. Having to add 2 new columns is not my preferred method

 

[Fluff]

They are date format

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