Two dimensional formula problem

[burhan]

A B C D

1
2 4 5 6 7
3 5
4 6
5 7
...

10 25
11 36
12 ..

I have problem with Formula of cell.
My formula is =A3*B2 at Cell A10.
I want the formula of one lower cell (A11) is =A4*C2 ,

So while auto filling the cell, in left part of the formula,
column must be fix and in right, row must be fix.

But either by using $ sign, I coludn't obtain correct result.

Is there a way to do this?

Thanks

Burhan

 

[Harald Staff]

Hi Burhan

Guess you was pretty close at a time. Put a dollar sign directly in front of
each element that shall NOT change, here A and 2 :
=$A3*B$2

 

[Paul]

A B C D

1
2 4 5 6 7
3 5
4 6
5 7
..

10 25
11 36
12 ..

I have problem with Formula of cell.
My formula is =A3*B2 at Cell A10.
I want the formula of one lower cell (A11) is =A4*C2 ,

So while auto filling the cell, in left part of the formula,
column must be fix and in right, row must be fix.

But either by using $ sign, I coludn't obtain correct result.

Is there a way to do this?

Thanks

Burhan

You cannot achieve what you describe just by using absolute or relative
references. The reason is simple: When you drag down, only the row variable
can change; when you drag across, only the column variable can change. What
you are trying to do is to get the column variable to change as you drag
down.

Instead, you must construct a suitable formula. One possibility for the
formula in A10 is:
=A3*OFFSET($A$2,0,ROW()-9)
This will go wrong if you insert/delete rows that cause cell A10 to 'move'.
So, a better formula would be:
=A3*OFFSET($A$2,0,ROW()-ROW($A$10)+1)

 

[burhan]

I think not TRUE RESULT.

I drag the cell (A10) by handling lower-right corner (autofill), to below
(A11).
the formula of cell A11 must be =$A4*C$2 (value:36). But the result you said
=$A4*B$2 (value:30)

column not changed. it's still B.

Burhan

 

[JMay]

In A10 enter
=OFFSET($A$3,ROW()-10,0)*OFFSET($A$3,-1,ROW()-9)
and copy down. Is that what you want?
HTH

 

[burhan]

thanks for your guidance

Burhan

You cannot achieve what you describe just by using absolute or relative
references. The reason is simple: When you drag down, only the row variable
can change; when you drag across, only the column variable can change. What
you are trying to do is to get the column variable to change as you drag
down.

Instead, you must construct a suitable formula. One possibility for the
formula in A10 is:
=A3*OFFSET($A$2,0,ROW()-9)
This will go wrong if you insert/delete rows that cause cell A10 to 'move'.
So, a better formula would be:
=A3*OFFSET($A$2,0,ROW()-ROW($A$10)+1)

 

[burhan]

thanks for your guidance

Burhan

In A10 enter
=OFFSET($A$3,ROW()-10,0)*OFFSET($A$3,-1,ROW()-9)
and copy down. Is that what you want?
HTH

 

[Harald Staff]

Sorry, misunderstood your post.