how to average the last five cells

D

dafnarei

New Member
Joined
May 23, 2011
Messages
9
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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.
 
Upvote 0
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:
Code: 
=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.
 
Upvote 0
dafnarei said:
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
Click to expand...

Care to specify the current range for a raw material and indicate where exactly do you want the average?
 
Upvote 0
dafnarei said:
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
Click to expand...
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)?
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
dafnarei said:
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
Click to expand...

Does a material name repeat in B3:B59?

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

Dank u
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Big, Multi-Step Macro for Efficiently Cutting Big Pieces Into Small Pieces
Replies
1
Views
509
Solon Aquila
S
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
418
STARL0RD
S
N
  • Question Question
save table data to query
Replies
1
Views
196
GraH
GraH
C
  • Solved
VBA to find current month and year - Hide all others
Replies
3
Views
580
kevin9999
K
N
  • Question Question
2-step Dynamic Ranges and Charts
Replies
0
Views
1K
Nick Loughton
N

Forum statistics

Threads
1,224,534
Messages
6,179,390
Members
452,909
Latest member
VickiS

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top