Excel Weighted Average

Question

I have a worksheet that contains 407 product codes for rows and a rolling 28 day calendar for the columns. Each cell within that table is a SUMIFS formula that looks up the product code and the date and returns the number of orders for that code on that day.

(Link to example picture) https://i.stack.imgur.com/DCEjv.jpg

I am trying to calculate a weighted average for the values within each product code. Meaning I'd like a cell to look at the last 28 day period and calculate a weighted average by finding the weight of each value automatically.

As an example, looking at code AC100, you'll notice there are a lot of days that there are 0's for. I'd like a cell to calculate the weighted average by looking up how many days were 0 and applying the appropriate weight to that value and so on and so on.

I think you do not look for weighted average. Weighted average requires two inputs. Do you want to take average where cell value does not equal zero? — Erdem Akkas
By appropriate weight, I mean if a product has 20 out of 28 days with 10 sales and 4 out of 28 with 15 sales and the remaining 4 days had 30 sales, the formula would see there are 3 unique values for sales and apply the appropriate weight for the average. i.e. the formula for this would be 10*(20/28)+15*(4/28)+30*(4/28). And I'd like to include zero as an option as well in the average. — D. Morley
What you are looking for is a simple average, there is no weighting. Do the math of the situation you just laid out. Copy this into Excel and it will produce exactly what you are looking for =AVERAGE(10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,15,15,15,15,30,30,30,30,). — Kyle

Tom Sharpe Tom Sharpe · Accepted Answer · 2017-04-12T20:29:53

Surely it's just the sum (which is unaffected by zeroes) divided by either the number of values or the number of non-zero values, and OP wants it separately for each row?

=SUM(B2:S2)/COUNT(B2:S2)

or

=SUM(B2:S2)/COUNTIF(B2:S2,">0")

Alternatively

=AVERAGE(B2:S2)

or

=AVERAGEIF(B2:S2,">0")

if you have a fairly recent version of Excel.

Excel Weighted Average

3 Answers