On 4 Nov, 13:57, kamal wrote:
Can anyone tell me how to do this

Suppose I have put some no.s in cell A1, A2, A3, A4, A5 & B5. Now the cell
A6 is the sum of no.s from A1 to A4 divided by A5. Now, I want cell B6, to
have same formulae as A6, i.e., it should have sum of no.s in cells A1 to A4,
but it should be divided by B5. If I copy paste from cell A6 to B6, the sum
is of cells B1 to B4 though divided by B5, now manually changing it is not
possible. Plz help, If it is not possible for cell B6 to be divided by B5,
atleast can it be the sum of cells A1 to A4.

Cell A6 should contain:

=SUM($A1:$A4)/A5

Now, when you copy and paste this to cell B6 it's formula will become:

=SUM($A1:$A4)/B5

This is because $A1 means absolute reference whereas A1 (in your case)
means relative reference.

Whenever you copy a cell to another, Excel modifies relative
references (e.g. A1:A10) to allow for the movement to the new
position. For example, if you copied the formula from cell A12 to cell
E12, the new formula would be E1:E10.

Absolute references (e.g. $A1 or $A$1) will not be altered and will
always refer to the original cells..

John.