Keeping a formula pattern to copy to cells below

[craynerd]

Hi,

I have this sequence of formula:

1 - 4 - AVERAGE(A2:A6)
2 - 3.8 - AVERAGE(A1,A3:A6)
3 - 3.6 - AVERAGE(A1:A2,A4:A6)
4 - 3.4 - AVERAGE(A1:A3,A5:A6)
5 - 3.2 - AVERAGE(A1:A4,A6)
6 - 3 - AVERAGE(A1:A5)


1-6 is just a sequence of numbers and the cell the right, calculates the average but excluding the number in that row.

This is a simplified version of what I have and the cells go from 1 to 230 so I need to drag the formula. No matter what I attempt to do with a $ or such, excel doesn't seem to copy the correct formula sequence. I`m lothed to write the formula by hand 230 times. Can anyone suggest how I can copy and drag down the formula to auto fill and keep the sequence.

[xlnitwit]

Hi,

If you select all 6 cells and then fill down, you should get the correct result with the formulas you have posted.

[Jonmo1]

Try

=(SUM(A$1:A$6)-A1)/5

[craynerd]

That just works for that example... if you have 240 cells of data the formula are different:

I have a top row so start on row 2

=AVERAGE (A3:A241)
=AVERAGE (A2, A4:A241)
=AVERAGE (A2:A3, A5:A241)

It doesn't copy correctly.

[Jonmo1]

Just need to adjust accordingly..
=(SUM(A$2:A$241)-A2)/239

[Jonmo1]

Or, put the first one in manually

=AVERAGE(A3:A241)

Then this and fill down

=AVERAGE(A$2:A2, A4:A$241)

[craynerd]

Try

=(SUM(A$1:A$6)-A1)/5

THANK YOU! That did the job. As always, I appreciate your time.

[Jonmo1]

You're welcome.