Avarage of rolling 12 months

[nburaq]

Hi,
I have monthly data of year 2022 and year 2023. I would like to get average of last 12 months as data entered for 2023. Here is the table ;

Datum	Location1
Jan/22	43,70
Feb/22	2,24
Mar/22	28,96
Apr/22	25,57
May/22	41,19
Jun/22	37,42
Jul/22	41,88
Aug/22	37,37
Sep/22	39,31
Oct/22	38,79
Nov/22	37,91
Dec/22	40,40
Jan/23	74,69
Feb/23	59,99
Mar/23	60,69
Apr/23	60,69
May/23	52,00
Jun/23
Jul/23
Aug/23
Sep/23
Oct/23
Nov/23
Dec/23

So , as soon as I entered data for JUN/23 it will give me the average of jun/23- jul/22. My approach was like this but I could not complete my function because I could not find how to concatenate number and text to make cell ref.

=AVARAGE(E&(MIN(IF(E3:E26="";ROW(E3:E26)))-1):E&((MIN(IF(E3:E26="";ROW(E3:E26)))-11))

I will be so glad if you can help me.

Thanks again

 

[Fluff]

How about

=AVERAGE(TAKE(FILTER(E3:E26,E3:E26<>""),-12))

 

[nburaq]

How about

=AVERAGE(TAKE(FILTER(E3:E26,E3:E26<>""),-12))

Hi Fluff
Thanks for your help and quick reaction. It would be a perfect solution if data of every month was entered. For example, if Jan/23 data is not available and left empty then formula will not work as it is planned.

Ok, Sorry,I was wrong it works great I dont know how!!! amazing thanks again!!!!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[nburaq]

Should I open a new title or keep going from here but if I need to use this formula with COUNTIF(Range; ">30") How can I do that ?
Thanks again

 

[Fluff]

That would appear to be a different question & so needs a new thread. Thanks