formula/cutting cells problems.

[chris_ninety1]

Apologies if this is the wrong forum or it's not descriptive enough o
what have you, but anyway, let's see if anyone can tell me what to d
here.

So I've got a series of numbers in cells B1-K1 and cell L1 is set t
take the average of these cells. That's all fine and good. Now, I wan
to move the data in B1-J1 over to C1-K1 and put a new figure in B1
whilst L1 gives me a new average but when I move the data, L1 no
changes, wanting to take the average of C1-K1. Suffice to say I don'
want it to change. Sure, I could change it back, but I have 1200 row
to do and not all of them contain data, so it's all rather fiddly an
would take me too long to change all of them back. Any help

 

[Kevin H. Stecyk]

chris_ninety1 wrote...

Apologies if this is the wrong forum or it's not descriptive enough or
what have you, but anyway, let's see if anyone can tell me what to do
here.

So I've got a series of numbers in cells B1-K1 and cell L1 is set to
take the average of these cells. That's all fine and good. Now, I want
to move the data in B1-J1 over to C1-K1 and put a new figure in B1,
whilst L1 gives me a new average but when I move the data, L1 now
changes, wanting to take the average of C1-K1. Suffice to say I don't
want it to change. Sure, I could change it back, but I have 1200 rows
to do and not all of them contain data, so it's all rather fiddly and
would take me too long to change all of them back. Any help?

Hi Chris,

One way...

=average(indirect("b1:k1"))

Now when you move B1:K1, your average still averages the same *fixed* range
of B1:K1.

Hope this helps.

Regards,
Kevin

 

[chris_ninety1]

Bingo. Cheers very much.

 

[Kevin H. Stecyk]

chris_ninety1 wrote...

Bingo. Cheers very much.

Thanks for letting us know it worked.

Regards,
Kevin