how to create a trailing fixed range

[xxxscimitarxxx]

Im no excel pro...and Ive learned more struggling with this problem in the last few days than I was bothered to learn before...but this is bugging me

simply put (and its not as simple as described below)........

say we have three cells at ...C3,D3,E3

C3 = 1, D3 = 2 and E3 is where I want the result....so I run a SUM function to for result to appear in E3....easy! 1+2=3

Then I insert a new column to the left of column E....resulting in cell E3 moving to F3.....I copy column D and paste into column E ....the appearance now changes to sum of three cells C3=1...+D3=2...+E3=2...result in F3 SUM=5

my problem is I don't want that.....

I want the F3 result cell to show the sum of data from cells D3=2 and E3=2...I don't want SUM to include cell C3 in the calculation...I only want the two cells before the result cell

but I also don't want to have to rerun a new Sum function every time I add a column or to trim or slide the formula manually

basically the job is to run rolling AVERAGE and SUM functions where the historic data trails the result cell by a fixed amount 3 months/3 cells....12 months/12 cells

all help very appreciated.....its been fun trying to work it out but what ever I have done does not seem to work

there are several other formulas running in each column for different requirement....these copy and past from the previous month to the new month and the formulas follow the change in position....SUM and AVERAGE stay anchored to the left most cell like a tape measure pinned at one end

[GeneralDisarray]

In your description, try this instead of the SUM() function - type this into E3

=SUM(OFFSET(E3,0,-1),OFFSET(E3,0,-2))

That will do what you are describing, the E3 will become F3 after the insert, but the -1 and -2 (column offsets) will not change. The rightmost column will sum the other two.

[xxxscimitarxxx]

will give that crack in the morning ....thanks a bundle

[xxxscimitarxxx]

I couldn't wait so I ran it on my example......perfect!.....been struggling with that for four days....cant thank you enough

I had all sorts of so called pros at my works giving me a sharp intake of breath and telling me its macro time....

[GeneralDisarray]

Glad to be of help