Two dimensional formula problem

  • Thread starter Thread starter burhan
  • Start date Start date
B

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
 
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
 
burhan said:
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
Click to expand...

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)
 
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
 
In A10 enter
=OFFSET($A$3,ROW()-10,0)*OFFSET($A$3,-1,ROW()-9)
and copy down. Is that what you want?
HTH
 
thanks for your guidance

Burhan


Paul said:
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)
Click to expand...
 
Back
Top