Calculating the Average with specific criteria

• Question

• Hi Guys

I have a spreadsheet that has two columns (A and B) - Column A contains Month/Year and column B contains sales. I would like a bit of vba that will look in column A to find the month/year then calculate from column B the average sales for that period. So for example:

Date     Sales

Jan 11  500.00

Jan 11  250.00

Mar 11  152.00

Apr 11 116.00

So in other words there can be a number of lines with the same date/year and the code will need to identify these and average all sales from column B associated with that particular date. I would like the output to appear in a summary table showing the total average sales for each particular month - perhaps this could be output to a new sheet?

Your help as always would be greatly appreciated.

BO

Monday, October 10, 2011 2:14 PM

=SUMPRODUCT(--(A\$1:A\$16=E1)*B\$1:B\$16)/MAX(1,SUMPRODUCT(--(A\$1:A\$16=E1)*(B\$1:B\$16<>0)))

So if Dec11 has three values:  80, 0, 28  the average will be 54 rather than 36

gsnu201109
• Marked as answer by Monday, October 10, 2011 7:28 PM
Monday, October 10, 2011 5:50 PM

All replies

• Say we have data in A1 thru B16 like:

 Jan 11 73 Jan 11 12 Jan 11 40 Jan 11 85 Mar 11 19 Mar 11 85 May 11 87 Jul 11 42 Jul 11 94 Jul 11 54 Jul 11 10 Sep 11 100 Nov 11 95 Dec 11 80 Dec 11 73 Dec 11 28

In E1 thru E12, we list all twelve months.

In F1 we enter:

=SUMPRODUCT(--(A\$1:A\$16=E1)*B\$1:B\$16)/MAX(1,COUNTIF(A\$1:A\$16,E1)) and copy down.  We see in cols E & F:

 Jan 11 52.5 Feb 11 0 Mar 11 52 Apr 11 0 May 11 87 Jun 11 0 Jul 11 50 Aug 11 0 Sep 11 100 Oct 11 0 Nov 11 95 Dec 11 60.3333

Note that if there are no sales in a month the average is zero.

gsnu201109
Monday, October 10, 2011 2:48 PM
• This is great except i do not want the zeros to affect the average - can i discount any zeros from the final average calculation? ie If july 11 has three figures: 100.00, 250.00 and 0.00 - the final figure will affect the overall average - i'm only interested in the average of actual sales.

Monday, October 10, 2011 3:58 PM
• Or if there is an N/A can i get the formula to ignore these?
Monday, October 10, 2011 4:43 PM

=SUMPRODUCT(--(A\$1:A\$16=E1)*B\$1:B\$16)/MAX(1,SUMPRODUCT(--(A\$1:A\$16=E1)*(B\$1:B\$16<>0)))

So if Dec11 has three values:  80, 0, 28  the average will be 54 rather than 36

gsnu201109
• Marked as answer by Monday, October 10, 2011 7:28 PM
Monday, October 10, 2011 5:50 PM
• It would be best to remove the #N/A's at the source.  What is the formula giving us the #N/A's ??
gsnu201109
Monday, October 10, 2011 5:53 PM
• Yes i can remove the N/As at source.

Thanks for your help with this!

Monday, October 10, 2011 7:28 PM
• How could i change the formula so that any additional rows added to the data sheet would be included ie so i don't have to keep changing the range of the cells referenced in the formula?

Tuesday, October 11, 2011 3:13 PM