# Excel: In which month, has my web page generated the most traffic (from 3-year data)?

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) I'd like to know, on average, which month has generated the most traffic.

What formula can I use?

Thank you so much.

• 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

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

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)
`````` 