Need an array formula to calculate average across a column, excluding zeroes

1
votes

I have this dataset in an Excel table: enter image description here
I want to calculate the average number of times a product was bought across all customers, where the customer has bought it at least once and I want to store the result in another column, in column D.

So for example... 2 Customers have bought "Cheddar Mature", one of them bought it 3 times and one of them bought it 5 times (highlighted above). Hence the value I want to calculate is (3+5)/2 = 4, and I want that value to be stored in cells D3, D4, D5, D6 & D7.

I know that I need an array formula to figure this out but I can't get my array formula correct. Here's what I have got so far:

{=AVERAGE(IF([Product]=[@Product],[Number Of Times Bought],0))}

Here is the result that gives me....not what I want:

enter image description here

As you can see the answer in those cells is 0.615384615 which (I've worked out) has been derived by dividing by the total number of rows in the table i.e. (3 + 5) / 13 = 0.615384615.

So, I need my array formula to only divide by the number of rows which have a non-zero value for sales of "Cheddar Mature". Can anyone figure that out?

To help, I have made the workbook available on my OneDrive for anyone that wants to try and solve this: https://1drv.ms/x/s!AjcmU60daA9VqqgSJkQ_ewOGPqkAMA

1
excelarray-formulas

1 Answers

2
votes
=AVERAGEIFS([Number Of Times Bought],[Product],[@Product],[Number Of Times Bought],">0")

normally entered.