CODEWITHSUNDEEP
excelexcel-formula
The Ramen Within
The Ramen Within

Reputation: 67

Build 1D array / list in formula by multiplying values for use in AVERAGE()

I have an excel spreadsheet with a list of values, column A contains the grading, column B contains the number of occurrences:

A       | B
---------------
Grading | Count 
1       | 1
2       | 1
3       | 2
4       | 3
5       | 5

I would like to find the average grading based on the count but to do this I need to build a list based on these values, I.E. the above chart should translate into:

=AVERAGE(1,2,3,3,4,4,4,5,5,5,5,5).

I have managed to come to a solution through a very convoluted method of creating a new table, using IF and COUNTIF to print out an array and then AVERAGE the entire range but this is time consuming to repeat and I'm sure there is much simpler way of doing this.

Upvotes: 1

Views: 32

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

If I'm not mistaken, you can just take the sum of product of columns A and B, then divide by the sum of the Count column:

=SUMPRODUCT(A2:A6, B2:B6) / SUM(B2:B6)

enter image description here

Note that using your hand written expanded formula yielded the same results:

=AVERAGE(1,2,3,3,4,4,4,5,5,5,5,5)

Upvotes: 2

Related Questions