Question about an equation

[Jambruins]

Is there a way to do the following? I have a column with 30 numbers i
it. So cells A1 to A30 have a number in them. In a couple of days
will be adding a number to cell A31 and after a few more days a numbe
to A32 and so on. Is there a way to add just the latest 10 number
(i.e. cells A21 through A30 and then after I add cell A31 it would ad
A22 through A30 and take out A21). Thank

 

[Bob Phillips]

This should do it

=SUM(LARGE(A1:A1000,{1,2,3,4,5,6,7,8,9,10}))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Sorry, answered a question you didn't ask. Try this instead

=SUM(INDIRECT("A"&MAX(1,COUNTA(A:A)-9)&":A"&COUNTA(A:A)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

This should do it

=SUM(LARGE(A1:A1000,{1,2,3,4,5,6,7,8,9,10}))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
try the following if you want to add the last 30 rows of column A
(works only if your column does not contain blanks):
=SUM(OFFSET(A1,MAX(COUNTA(A:A)-30,0),0,30))

HTH
Frank

 

[Jambruins]

thanks, it worked great