Average formula with relative cell reference and position

[Buster^]

Hello! I need help making a formula that is going to do average with relative cell references and only 4 averages for the last 8 numbers. To make this a lot easier to understand I have written all the necessary information in the spreadsheet, so please look at that, as it will explain (Hopefully) all you need to know!

If there is any question please ask!
Buster^

[Richard Buttrey]

The column C formulae are of course relative so copying them down will automatically pick up the relevant column A values so I'm not quite clear what further you expect.

As you create a new formula in C12 do you expect the C4 formula to become zero. Your mention of there only being 4 values might suggest this. In which case in C4

Please Login or Register  to view this content.

and then copied down.

[Buster^]

The column C formulae are of course relative so copying them down will automatically pick up the relevant column A values so I'm not quite clear what further you expect.

As you create a new formula in C12 do you expect the C4 formula to become zero. Your mention of there only being 4 values might suggest this. In which case in C4

Please Login or Register  to view this content.

and then copied down.

Yes, when you add a value in A11, c4 should return 0 (or blank cell) and a new average in C5 (B4:B5). And there should only be 4 averages, meaning that C3,C5,C7 and C9 should be empty, but when you add a value in A11, C4,C6,C8 and C10 will be empty.

[Buster^]

Actually found a formula that did the trick:

Please Login or Register  to view this content.

In cell D2. I still get #N/A after cell d11, but I think i will be able to remove thoose

Thank you anyway for posting (I think that I should have been more clear on what I wanted the formula to do)!
Buster^