Variable range

[Gusher]

Hi,

In cell A1 I have a number which can vary, lets call it 12.

In column B I have a series of numbers from cells B1 to B200. In Column C, I want a formula to calculate the sum of a defined range between B1 and B200. So for example in cell B 100 I need a formula to calculate the sum of cells B100 to B89 i.e. 12 minus 100 as defined in cell A1. If cell A1 the formula needs to define the range B100 to 96. I need to be able to copy the formula down column B .

Many Thanks

Mark

 

[Legacy 143009]

For example B100:

 =SUM(OFFSET(B100,0,0,-1*$A$1,1))

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Gusher]

SUM(OFFSET(B100,0,0,-1*$A$1,1))

great, many thanks, worked !