# Count value pairs and get average of those pairs?

I have a spreadsheet with events that happened on certain dates. I created an extra column with week numbers based on those dates.

Now I got several events per week (number). What I want to find out is how often theses events occur on average per week.

So I guess I need to count somehow the value pairs of a week number and then return the average for those. But I don't know how exactly.

Any help?

It looks like you've figured out the WEEKNUM() function, now you need COUNTIF().

COUNTIF(range,criteria) counts the number of occurrences of a particular value in a range.

In the table below, I've created random dates between 6/1/17 and 12/31/17. Column C contains the week numbers of the event dates, and Column D is a list of the week numbers for June through Dec.

The number of events for a particular week is counted using this formula, filled down from E2:

`=COUNTIF(C\$2:C\$32,D2)`

COUNTIF() looks through the range of week numbers in C2:C32 and counts the number of times the week number in Column D occurs. The average of Column E is calculated in F2.

EDIT: To exclude weeks with zero events from the average, calculate it like this:

`=SUM(E2:E32)/COUNTIF(E2:E32,">"&0)`

Hope this helps, and good luck.

• Thank you, I just tried this and it comes really close to what I need. I just need to exclude the weeks with zero events. I only want to count weeks with 1 or more events. – mr.moe Dec 11 '17 at 17:52
• See the edit for a formula to exclude zero values from the average. – Bandersnatch Dec 11 '17 at 19:24