| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
calculating averages
I am sure there is a simple way!
each week I insert a number in a column, thus after 6 weeks I have A1:A6 filled in. Each week I need to take the average of the last 4 numbers. How can I calculate this automatically without having to change the range each time? from Kevin |
|
#2
|
|||
|
|||
|
One way
=AVERAGE(INDEX(A:A,MATCH(9.99999999999999E+307,A:A )-3):INDEX(A:A,MATCH(9.99999999999999E+307,A:A))) Regards, Peo Sjoblom "keving" wrote: I am sure there is a simple way! each week I insert a number in a column, thus after 6 weeks I have A1:A6 filled in. Each week I need to take the average of the last 4 numbers. How can I calculate this automatically without having to change the range each time? from Kevin |
|
#3
|
|||
|
|||
|
I think it can be done more elegantly but this works
=AVERAGE(OFFSET(A1:A1,COUNT(A1:A5000)-6,0):OFFSET(A1:A1,COUNT(A1:A5000)-1,0)) I am assuming you will not go passed A5000 best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "keving" wrote in message ... I am sure there is a simple way! each week I insert a number in a column, thus after 6 weeks I have A1:A6 filled in. Each week I need to take the average of the last 4 numbers. How can I calculate this automatically without having to change the range each time? from Kevin |
|
#4
|
|||
|
|||
|
Misread question, I averaged last 6. Change that to
=AVERAGE(OFFSET(A1:A1,COUNT(A1:A5000)-4,0):OFFSET(A1:A1,COUNT(A1:A5000)-1,0)) to average last 4 -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "Bernard Liengme" wrote in message ... I think it can be done more elegantly but this works =AVERAGE(OFFSET(A1:A1,COUNT(A1:A5000)-6,0):OFFSET(A1:A1,COUNT(A1:A5000)-1,0)) I am assuming you will not go passed A5000 best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "keving" wrote in message ... I am sure there is a simple way! each week I insert a number in a column, thus after 6 weeks I have A1:A6 filled in. Each week I need to take the average of the last 4 numbers. How can I calculate this automatically without having to change the range each time? from Kevin |
|
#5
|
|||
|
|||
|
oh my! it seems complicated.
the table below shows what I need to do, assuming there is just one column and I add to it each week.Thus, wk8 I average numbers from B5:B8 A B A B 1 wk1 2 wk1 2 2 wk2 3 wk2 3 3 wk3 4 wk3 4 4 wk4 3 wk4 3 5 wk5 2 wk5 2 6 wk6 1 wk6 1 7 wk7 4 AVERAGE(B3:B6) AVERAGE(B4:B7) "Bernard Liengme" wrote: I think it can be done more elegantly but this works =AVERAGE(OFFSET(A1:A1,COUNT(A1:A5000)-6,0):OFFSET(A1:A1,COUNT(A1:A5000)-1,0)) I am assuming you will not go passed A5000 best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "keving" wrote in message ... I am sure there is a simple way! each week I insert a number in a column, thus after 6 weeks I have A1:A6 filled in. Each week I need to take the average of the last 4 numbers. How can I calculate this automatically without having to change the range each time? from Kevin |
|
#6
|
|||
|
|||
|
Have you tried either of the answers sent to you?
-- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "keving" wrote in message ... oh my! it seems complicated. the table below shows what I need to do, assuming there is just one column and I add to it each week.Thus, wk8 I average numbers from B5:B8 A B A B 1 wk1 2 wk1 2 2 wk2 3 wk2 3 3 wk3 4 wk3 4 4 wk4 3 wk4 3 5 wk5 2 wk5 2 6 wk6 1 wk6 1 7 wk7 4 AVERAGE(B3:B6) AVERAGE(B4:B7) "Bernard Liengme" wrote: I think it can be done more elegantly but this works =AVERAGE(OFFSET(A1:A1,COUNT(A1:A5000)-6,0):OFFSET(A1:A1,COUNT(A1:A5000)-1,0)) I am assuming you will not go passed A5000 best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "keving" wrote in message ... I am sure there is a simple way! each week I insert a number in a column, thus after 6 weeks I have A1:A6 filled in. Each week I need to take the average of the last 4 numbers. How can I calculate this automatically without having to change the range each time? from Kevin |
|
#7
|
|||
|
|||
|
Thank you for the advice. I will give the solutions a go!
"Bernard Liengme" wrote: Misread question, I averaged last 6. Change that to =AVERAGE(OFFSET(A1:A1,COUNT(A1:A5000)-4,0):OFFSET(A1:A1,COUNT(A1:A5000)-1,0)) to average last 4 -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "Bernard Liengme" wrote in message ... I think it can be done more elegantly but this works =AVERAGE(OFFSET(A1:A1,COUNT(A1:A5000)-6,0):OFFSET(A1:A1,COUNT(A1:A5000)-1,0)) I am assuming you will not go passed A5000 best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "keving" wrote in message ... I am sure there is a simple way! each week I insert a number in a column, thus after 6 weeks I have A1:A6 filled in. Each week I need to take the average of the last 4 numbers. How can I calculate this automatically without having to change the range each time? from Kevin |
|
#8
|
|||
|
|||
|
Bernard, I tried your solution - thank you it seems to work, but I have to
admit to not understanding the logic that well! Any ideas where I might find an explanation of the formula? "Bernard Liengme" wrote: Misread question, I averaged last 6. Change that to =AVERAGE(OFFSET(A1:A1,COUNT(A1:A5000)-4,0):OFFSET(A1:A1,COUNT(A1:A5000)-1,0)) to average last 4 -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "Bernard Liengme" wrote in message ... I think it can be done more elegantly but this works =AVERAGE(OFFSET(A1:A1,COUNT(A1:A5000)-6,0):OFFSET(A1:A1,COUNT(A1:A5000)-1,0)) I am assuming you will not go passed A5000 best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "keving" wrote in message ... I am sure there is a simple way! each week I insert a number in a column, thus after 6 weeks I have A1:A6 filled in. Each week I need to take the average of the last 4 numbers. How can I calculate this automatically without having to change the range each time? from Kevin |
|
#9
|
|||
|
|||
|
Try this.........
In C4 put this formula and copy it down column C =AVERAGE(B1:B4) Vaya con Dios, Chuck, CABGx3 "keving" wrote in message ... I am sure there is a simple way! each week I insert a number in a column, thus after 6 weeks I have A1:A6 filled in. Each week I need to take the average of the last 4 numbers. How can I calculate this automatically without having to change the range each time? from Kevin |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formulas not calculating??? | Excel Discussion (Misc queries) | |||
| CALCULATING CELLS AS A TOTAL | Excel Discussion (Misc queries) | |||
| Calculating effective time from start/end date+time | Excel Worksheet Functions | |||
| excel template for calculating trial balances and adjusting entri. | Excel Worksheet Functions | |||
| Calculating Net Position and Average Price | Excel Worksheet Functions | |||
Linear Mode
