0

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?

0

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.

enter image description here

  • 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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.