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………
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………