Calculating Average per any Given Month in Excel

0
votes

I need to calculate the cadence that people send emails. So I have column A being email addresses, column B being the date they sent an email. I have data over a few years and needs to see on average, in any given month, how often does someone send email. So I need Excel to count the number of times an email address shows up in a given month/year and then find the average for that email address for ALL months/years.

For example: If I send four emails: Jan 2017, Jan 2017, Feb 2017, Feb 2016. I want column C to show 1.333 (the average number of emails I sent in any given month and year) every time my email address shows up.

I have tried countless formulas from this site, but none work for this exact need. Thanks!

1
excelexcel-formulaaverage
What have you tried so far? AVERAGEIF() with COUNTIF() maybe? – BruceWayne
How did you get 1.333? I don't see how that matches the example you gave. – xidgel
@xidgel 2 for jan 2017 + 1 for feb 2017 + 1 for feb 2016/ 3 months. – Scott Craner
@ScottCraner Feeling kinda stupid about now! – xidgel

1 Answers

1
votes

So if you have your two columns of date code (A) and email sender (B), then make a small table with each unique sender name from your sender column - Mine starts in E2, with the first row being column headers for readability.

Somewhere on your sheet put the date you want to be your start date, to be referenced in the averaging formula - mine is in F6. Use a format similar to your date code, mmmyyyy.

In another place create a vlookup table to match month name to number, mine starts at I2.

To average emails sent by year use =COUNTIF(B:B,E2)/(1+ YEAR(TODAY())-RIGHT($F$6,4)) To average emails per month use =COUNTIF(B:B,E2)/(12 * (YEAR(TODAY())-RIGHT($F$6,4)) + Vlookup(Left($F$6,3),$I$2:$J$13,2, False))

I'm not sure if there's a nice on the fly way to do it without the extra cells and tables but that should get you what you want. You should be able to drag the formula down the entire column.