how to average the last five cells

[dafnarei]

hi,

My excel table for number of Raw material is design the way that in each row i have different kind of material and in the collum, each coullum stand for different month.
My problem is that i need to make a formola that gives the avarage of the last 5 month of each material, and the formola will be allways updated to the last 5 months.

thank you
dafna

 

[T. Valko]

2^15 can be replaced with 9.99999999999999E+307 .

It can also be replaced by 1E100.

 

[T. Valko]

thank you all the problem solved, i used Valko's formula but Sahak's and Aladin's works as well.
now i have a new problem, i need to work with this formula but ignore the last cells AY3:AW3 in the average.

the formula i use is
=AVERAGE(OFFSET(A3,,MATCH(1E100,3:3)-1,,-5))

OK, to exclude the last 3 cells:

=AVERAGE(OFFSET(A3,,MATCH(1E100,3:3)-4,,-5))

(I keep losing my internet connection so I hope this makes it through!)

 

[Aladin Akyurek]

...now i have a new problem, i need to work with this formula but ignore the last cells AY3:AW3 in the average.
...

Are you saying that the range will never go beyond AY3 and the last 3 cells, AW3:AY3, can be omitted from the average?

 

[dafnarei]

Thank you Valko and for all of the repliers for solving the problem.

 

[T. Valko]

Thank you Valko and for all of the repliers for solving the problem.

You're welcome. Thanks for the feedback!