Lotus "Up / Down" functionality

[peppervenice]

Is there an Excel command that is equivalent to the Lotus 123 Down (or Up) command. In Lotus you could place up or down X number of cells into a formula. For example, if you want to average a daily entry for the last 10 days and you enter a new row each day to enter that days data. It has been many, many years since my Lotus days but the formula was something like

+average (current cell [where that was the new data entered for the new day], Up (10)), [or maybe it was Up 9, I don't recall]. With this, every time you entered a new row it would calculate the average for the latest 10 rows.

Thanks.

 

[DRSteele]

Welcome to the forum.

Maybe function OFFSET. See here.

 

[peppervenice]

Welcome to the forum.

Maybe function OFFSET. See here.

thanks. Looks like it may work. I think i tried that maybe 15 years or so ago. Don't recall if it worked, but I think it will.

 

[Fluff]

Hi & welcome to MrExcel.
Another option

+Fluff v2.xlsm
	A	B	C
1
2	1		10.5
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17
18
Main

Cell Formulas
Range	Formula
A2:A16	A2	=SEQUENCE(15)
C2	C2	=AVERAGE(FILTER(A2:A100,ROW(A2:A100)>COUNT(A2:A100)-9))
Dynamic array formulas.

 

[Fluff]

Forgot to mention, ignore the formula in A2, that's just to get some data.

 

[peppervenice]

Is there an Excel command that is equivalent to the Lotus 123 Down (or Up) command. In Lotus you could place up or down X number of cells into a formula. For example, if you want to average a daily entry for the last 10 days and you enter a new row each day to enter that days data. It has been many, many years since my Lotus days but the formula was something like

+average (current cell [where that was the new data entered for the new day], Up (10)), [or maybe it was Up 9, I don't recall]. With this, every time you entered a new row it would calculate the average for the latest 10 rows.

Thanks.

Ill try that as well. Thank you.