Averaging across several rows

[Mith]

I'm trying to find the average numbers of letters per word across a series of texts. The data I have looks like this, with row 5 being the number of letters per word and rows 6-12 being the number of words with those letters in the different texts:

\1

Is there a formula I can use to do this? Any help would be very much appreciated

[NickyC]

how about using sumproduct to multiply the number of words of each length by the length, then dividing by the number of words

[Mith]

I'm a bit of an Excel illiterate, I'm afraid, I know the most basic of basic formulas, and that's about it Would that be something like =sumproduct(a5:05, c6:o6) and then divided by the overall total number of words (4,892) or by the total number of words with one letter (and then two letters and three letters and so on individually)?

[NickyC]

to get the average letters per word, you need to know the total number of letters and divide it by the total number of words in each row
to get the total number of letters, use sumproduct to multiply the number of 2 letter words by 2, the number of 3-letter words by 3 etc.
then divide by the number of words in that row

so, if your header row (row 1) includes the number of letters per word in e.g. cells c1:g1, and your row 6 includes the number of words with that many letters, and cell B6 contrains the number of words in that row type

=SUMPRODUCT(C1:G1, C6:G6)/B6

or

=SUMPRODUCT(C1:G1, C6:G6)/SUM(C6:G6)

[Mith]

Oh, I see! Thank you, that's brilliant