Calculate average from last 20 values entered without VBA

Question

I need to calculate average from the last 20 values entered in column K.

I already learned I can find the last entered value by using this:

=address(max(match(1e99; K:K)); 11; 4; 1)

Which is gonna be my average end, and I can find my average start with this:

=address(max(match(1e99; K:K))-20; 11; 4; 1)

My question now is, how can I use both adresses to create an average formula? This is spreadsheet is getting new values everyday, so cell address will be diferente each day.

For example, those two formulas will produce: K424 and K404 respectively.

So I need to create a average formula which work like:

=average(K404:K424)

By using those prior formulas results. I dont have the option to type numbers I get from those formulas.

I tried this, but didnt work:

=average((address(match(1e99; K:K))-20; 11; 4; 1)):(address(match(1e99; K:K)); 11; 4; 1)))

If I try: =average((address(match(1e99; K:K))-20; 11; 4; 1))&":"&(address(match(1e99; K:K)); 11; 4; 1)))

I get#VALUE

Answer 1

You can stitch two INDEX functions together with a colon for a proper cell range without having to use the volatile INDIRECT¹ on strings returned by the ADDRESS function.

=average(index(K:K; match(1e99; K:K)-19):index(K:K; match(1e99; K:K)))

That will average the last 20 rows in column K.

---

¹ _{Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, OFFSET, TODAY, NOW, RAND and RANDBETWEEN. Some sub-functions of the CELL and INFO worksheet functions will make them volatile as well.}

Answer 2

you're looking for =average( ... )

Answer 3

Try this -- when building the nested formula you need to maintain the string literal for the colon.

The results of both address function should return a string, like "K404" and "K424" (or possibly "$K$404" and "$K$424"). You need to maintain the colon as part of a string, as well, like so and use the Indirect function on the concatenated address string:

=average(Indirect((address(match(1e99; K:K)); 11; 4; 1))&":"&(address(match(1e99; K:K))-20; 11; 4; 1))))

Answer 4

This is another formula for the same thing

{=SUM(IF(ROW(G:G)>MATCH(1E+99,G:G)-20,G:G,0))/20}

it's an array formula, so you have to use CTRL-SHIFT-ENTER