-1

In column A, I have a list of months in the format of YY-MM.

In column B, I have a list of unique users that visited a site page during each month

(Nov 2016- Nov 2018)

Sample chart

I'd like to know, on average, which month has generated the most traffic.

What formula can I use?

Thank you so much.

5
  • 2
    You're looking to MATCH the MAX value of column B, the use that INDEX to return the corresponding date – cybernetic.nomad Dec 7 '18 at 15:34
  • Thank you. I tried to create a formula but I feel lost. Can you kindly suggest a formula? Thank you! – user229302 Dec 7 '18 at 16:46
  • Probably a homework question. – harrymc Dec 7 '18 at 17:11
  • better question is do you have to add all the Novembers together and divide by the number of novembers used and compare that to the result for April through the same process and figure which of the 12 month is the max average? – Forward Ed Dec 7 '18 at 21:22
  • if you do =ISTEXT(A1) on a cell with a date in it, does it give you a value of TRUE or FALSE? – Forward Ed Dec 7 '18 at 21:24
0

To expand on my comment:

=MAX(B:B)

Will give you the MAX value in column B, then

=MATCH(MAX(B:B),B:B)

Will give you the row of that MAX, and:

=INDEX(A:A,MATCH(MAX(B:B),B:B))

Will return the value in column A corresponding in that row

0

Another formula that might be your friend is AVERAGEIF or AVERAGEIFS.

My approach uses SUMPRODUCT which will perform array like calculations without being an array.

1) Build a table averages

In my example I listed out the month number for each month regardless of year in E2 to E13. In the adjacent column use the following formula in F2 and copy down to F13.

=IFERROR(SUMPRODUCT((--RIGHT($A$2:$A$11,2)=E2)*$B$2:$B$11)/SUMPRODUCT(--(--RIGHT($A$2:$A$11,2)=E2)),"")

The IFERROR function is used to deal with the divide by 0 error that will occur if there is no value for a given month. If you prefer to see 0 instead of a blank, change the "" at the end to 0.

The above formula is based on the assumption that your dates are actually strings. If they are actually dates then use the following:

=IFERROR(SUMPRODUCT((MONTH($A$2:$A$11)=E2)*$B$2:$B$11)/SUMPRODUCT(--(MONTH($A$2:$A$11)=E2),"")

2) Find the maximum Average

In the example below I used the following formula in I2:

=MAX($F$2:$F$13)

3) Find the corresponding month

In J2 I used the following formula to look up the position of the max value in the list. If their are repeat / tied maximums, it will return the first month.

=MATCH(I2,$F$2:$F$13,0)

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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