Average Length of Text String in Excel Array

excel

Solution

As I mentioned in comments above, you can use your formula:

=AVERAGE(LEN(A1:A1000))

with array entry (CTRL+SHIFT+ENTER).

If you don't like to take into account empty cells, use:

=AVERAGE(IF(A1:A1000<>"",LEN(A1:A1000)))

also with array entry.

Problem

Using the LEN() function it seems simple enough to check the lengths of multiple cells and then take the average. While this works well for a small number of cells (e.g. AVERAGE(LEN(A1),LEN(A2))) it doesn't scale very well. I was wondering if there was a way (maybe using an array formula) to have Excel take the average (or apply any numeric formula really) of multiple cells at once. For example, I was wondering if something like AVERAGE(LEN(A1:A1000)) would be possible. Thanks!

Original source