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

 

[Robert Mika]

If names of month are in column A and data start from C1:


=AVERAGE(OFFSET(C1,,COUNT(C1:AA1)-5,,5))
Copy down

Adjust "AA1" portion to your needs.

 

[Sahak]

Hi Dafna,

Try this.
Let assume your materials listed from A2 to down (A2, A3, A4 ….) & your months start from C2 to right (C2, D2, E2 ……), then in range B2 copy this formula:

=IF(C2="",0,IF(COUNTA(C2:OFFSET(C2,0,200))>5,AVERAGE(OFFSET(C2,,COUNT(C2:OFFSET(C2,0,200))-5,,5)),AVERAGE(C2:OFFSET(C2,0,5))))

Copy formula down.

 

[Aladin Akyurek]

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

Care to specify the current range for a raw material and indicate where exactly do you want the average?

 

[T. Valko]

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

Will there always be at least 5 numbers in a row to average? If not, then what should happen?

Are the numbers entered in a contiguous range (no empty cells between entries)?

 

[dafnarei]

each month i add another collum that sumeraize the month for each matirial always have,
i keep the resukt of the last two years. all the cells are full with a number (0-n)

 

[dafnarei]

month names are F2:AT2, AT is this April
names of the material B3:B59
and the quantity of each month material is between F3:AT59

thanks

 

[dafnarei]

names of month are F2:AT2, name of material B3:B59.
for example, this month i need to average AP:AT for all the row from 3-59
and next month i will need to average AQ:AU.

thank you

 

[Aladin Akyurek]

names of month are F2:AT2, name of material B3:B59.
for example, this month i need to average AP:AT for all the row from 3-59
and next month i will need to average AQ:AU.

thank you

Does a material name repeat in B3:B59?

Can we put the average in A3?

 

[dafnarei]

in each row from B3:B59 it is a different name of material without repetitions.
we can use A3 for the average.

Dank u