Answered by:
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
Answers

Use this formula instead:
=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 ByronOne Monday, October 10, 2011 7:28 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.33333 Note that if there are no sales in a month the average is zero.
gsnu201109 
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.


Use this formula instead:
=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 ByronOne Monday, October 10, 2011 7:28 PM


