How to sum using a formula for each row without intermediate subtotal

Hi there!

I am trying to perform this formula for each row, and then sum it, and then divide by n*n+1/2 (triangle number) :

(E3*(6-F3) + E7*(6-F7) + E11*(6-F11) + E15*(6-F15) + E19*(6-F19)) / (COUNT(E3:E22) * (COUNT(E3:E22) + 1) / 2)

(I have only 5 cells, each one being "merged and centered" of 4 cells)

I would love a recursive way of doing this without this manual sum, and without using a subtotal for each row (otherwise it'd be trivial, obviously).

I naively tried this, but of course it won't work:

SUM(E3:E22*(6-F3:F22)) / (COUNT(E3:E22) * (COUNT(E3:E22) + 1) / 2)

By the way, is there a "n*n+1 / 2" function somewhere that would make the second part of my formula shorter?

I am a total beginner in Excel, I am just trying to help a friend. Please forgive me if there is something obvious that I missed

Thank you all!

Here is the data in raw format:

30%|4
85%|5
50%|2
60%|1
80%|3
61%|59%

61% is the linear average
59% is the inverted weighted average, using the naive-and-not-so-generic formula I gave earlier (except I had more columns in my original sheet/formula)

Hi 6StringJazzer!

Thank you for the kind welcome!

I am aware of my lazyness when writing the triangular number and skipping the parenthesis, please forgive me for that

To sum up: I am computing a weighted average, except my weights are inverted (1 is most important, 5 is of least importance), that's why I have a "6 - CELL" in my formula, which complicates things.
(I looked at SUMPRODUCT and it would indeed work with a classic weighted avergage, but mine is "inverted" and I am not sure how to make it work)

I don't mind sending you an example file (see attachments), but please do not spend too much time on this, it is not very important (because my naive formula already works, and also because this sheet is not very important in itself).
But if you are curious about finding a generic way to do this, then be my guest!

In any case I thank you kindly for your help!

PS: it seems I am not allowed to post a link or an attachment because I'm new
I have two columns: a rating (percentage, but it does not matter), and an inverted weight (from 1 to 5, descending order).
Then five rows of data.
And finally a sixth row allowing me to display the weighted average.

I can't see how that data matches the first formula you showed. But to get the answer with this data:

Formula:

=SUMPRODUCT($A$1:$A$5,6-$B$1:$B$5)/(COUNT($A$1:$A$5)*(COUNT($A$1:$A$5)+1)/2)

Also new members cannot include links, but you are allowed to attach a file. Please see my instructions above.

Thank you 6StringJazzer for this solution, it works!

I do wonder, though, why "6 - $B$1:$B$5" would work with SUMPRODUCT, but not with SUM? It is great that it is allowed, though!

Regarding my example data, as I explained: "I had more columns in my original sheet/formula" That's why my original post formula refers to E and F columns instead of A and B respectively (the same goes for the rows)

Thank you again for this great way of computing an inverted weighted average, 6StringJazzer! Much appreciated that you took time to look into it and help out!

PS: regarding the attachments, I did follow your instructions, but looking back at it, and after trying just now, I now know it was caused by the "at" character that I was putting in front of your pseudo (because it is common practice to do so, but again, maybe here it is a "plus" sign or something else, to address someone).

SUMPRODUCT is a little unusual in that it works like a loop. Most Excel functions, like SUM, won't do this. It is perfect for weighted averages.

SUMPRODUCT($A$1:$A$5,6-$B$1:$B$5)

This means you have defined two arrays. The first array is A1, A2, A3, A4, A5. The second array is 6-B1, 6-B2, and so forth. Then it does a vector multiplication on them, and effectively does this:

A1*(6-B1) + A2*(6-B2) +A3*(6-B3) +A4*(6-B4) +A5*(6-B5)

There are creative ways to solve all kinds of looping problems with SUMPRODUCT.

A similar way to do this is with an array formula, which has more flexibility.

Thank you for the kind explanation, and for the keywords "array formula", which I won't hesitate to look up at some later time!

It is great to learn new things every day

Thank you again!