none
Calculating the Average with specific criteria RRS feed

  • 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

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
    Monday, October 10, 2011 5:50 PM
    Moderator

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
    Monday, October 10, 2011 2:48 PM
    Moderator
  • 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
  • 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
    Monday, October 10, 2011 5:50 PM
    Moderator
  • 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
    Moderator
  • 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