Average current 3month based on TODAY()

[v!ctor]

Hello,
I am trying to calculate the average of current 3month based on todays month.

image.png

As you can see in the attached image I want
e.g for today to calculate in red cell the average of months APRIL,MAY,JUNE and when the month is e.g August to calulate the average of months JULY,AUGUST,SEPTEMBER.

hope to be understandable

thank you in advanve

[dilipandey]

Hi V!ctor,


Would you mind posting excel workbook to enable us to give a quick try... please upload. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[v!ctor]

thanks for reply

Attached file

[Cutter]

Try

=AVERAGE(CHOOSE(INT((MONTH(TODAY())-1)/3)+1,D3:F3,G3:I3,J3:L3,M3:O3))

[dilipandey]

Hi Victor,

See the attached file where I have used the dynamic name "data" as :-
=OFFSET(INDIRECT(VLOOKUP(Φύλλο1!$A$1,Φύλλο1!$Q:$R,2,0)),ROW(Φύλλο1!$C$4)-3,0)

Sample book.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[v!ctor]

Thank you very much....it works perfect!!!
I have one more question about this problem.
in the same worksheet I want to add a column to calculate the higher value between ''column C'' and todays month.

thanks one more time for your help!!

[v!ctor]

after this....
=AVERAGE(CHOOSE(INT((MONTH(TODAY())-1)/3)+1,D3:F3,G3:I3,J3:L3,M3:O3))

I tried this:
=IF(C3>MONTH(TODAY());C3;MONTH(TODAY()))
but I get wrong results

[Cutter]

Try
=MAX(C3;INDEX(D3:O3;1;MONTH($A$1)))