Averaging by day of the week from a lengthy list of dates

[RudeClown]

Good evening,

I’m havingsome difficulty figuring out a way to average by day of the week, the otherhelp I’ve found is returning a #VALUE error. I was hoping somebody here might be able to give me a hand. Here’s how the pertinent data is laid out:
“List” Sheet
Monthly, we will copy a list of e-mails that came in duringthe previous month from Outlook, and paste them to this sheet. The dates of each email fall into the range C1:C5001. Obviously, there will be multiple entries perday, with each entry being 1 e-mail. Ihave a helper column on this sheet (H1:H5001) that returns the numerical day ofthe week for each entry. G1:G5001 is ahelper column which extracts the day of the month from C, and J2 is a helperwhich extracts just the year from C2. I2is a helper that extracts the month from C2 (these will only ever be done one calendarmonth at a time).

“Analysis” Sheet
A3:A33 is a column to write the date from the month that isentered on the other sheet, with one row for each date. For example, the formula in A3 =

=IF(List!$C$2>0,IFERROR(DATEVALUE(CONCATENATE(List!$I$2,"/1/",List!$J$2)),""),"")
This formula displays “9/1/2018” when the email on “List”are from September, and the column is formatted in the date layout MM/DD/YYYY. I added “DATEVALUE” to turn the result into adate number format from text.

B3:B33 is a Countif formula to count the total number ofe-mails per day, for example:
=IF(List!C2>0,(COUNTIF(List!$G$1:$G$5001,1)),"")
This returns the correct answer.

I’ve tried the below two formulas (they’re found on “Analysis”sheet) using some help pages I’ve read through (both are suppose t average he number of e-mails received on a Mon), but both return #VALUE errors.

=SUMPRODUCT((WEEKDAY(A3:A33)=2)*B3:B33)/SUMPRODUCT((WEEKDAY(A3:A33)=2)*1)
=AVERAGE(IF(WEEKDAY(A3:A33)=2,B3:B33))
I thought using DATEVALUE in the A3:A33 formulas would coverany issues with the format of the data, but I am still getting the error. My goal is to be able to say “In the month of______, we got an avg of xx E-mails on Mondays, xx on Tuesdays,” etc. Any thoughts on how I may be able to accomplishthis? Seems like it should be easy, butI am missing something………

 

[Michael M]

Don't have Excel at the moment, but check to make sure Monday is = 2 in your region
The International standard id for Monday is =1, but US, Canada & Japan use 2

 

[RudeClown]

I'm in the US, and I reviewed the list of numerical codes returned in List!G1:G5001 to make sure they were coming back as expected, and they are correct. If that was wrong though, I expect the only issue that would cause would be giving me an incorrect count, right?

 

[Michael M]

Correct.
Are they actual dates or text to look like dates, if they are actual dates they will be formatted right aligned, whereas text will be left aligned.

 

[Phuoc]

=AVERAGE(IF(WEEKDAY(A3:A33)=2,B3:B33))

Change to:
=AVERAGE(IF(TEXT(A3:A33,"ddd")="Mon",B3:B33))
C+S+E

 

[RudeClown]

Thank Phuoc for taking a look, I appreciate it! I put in the formula you noted, but it is unfortunately returning a #VALUE error as well.

 

[Phuoc]

but it is unfortunately returning a #VALUE error as well.

This is an array formula,enter with Ctrl+Shift+Enter.

 

[RudeClown]

I entered it both ways, and I still got the #VALUE error. I have no experience with array formulas though, so I'm the first to admit I don't know what I'm doing with them. When I hit CTRL+SHIFT+ENTER, it's not adding the {} around the formula, so I assume it's not accepting it as an array? I am doing this at home through the online version of Excel off OneDrive, are there any restrictions to using arrays that you know of via Excel online? I've never had success with them on projects past either. I can try it at work on full-fledged Excel to see if I get a different result, and I'll note what I get here. My data in Analysis!A3:A33 is right aligned, so I assume it's a true date format? Although, I may have changed the alignment when I did that column, so I don't know if I can trust that as an indicator.

 

[SpillerBD]

Rude, You don't indicate which version of Excel you are using. If you are using O365 then you should have PowerPivot.
Watch MikeGirvin's MSPTDA 15 in the later half of that he demonstrates your request using the faster and better DataModel using DAX.
He also makes the comparison between formula use and PivotTable use.