Offset function

[Dave]

Hi,
I have the formula in A2:
=OFFSET(A2,-1,0)*12.36%
This multiplies A1 by 12.36.
How do I change this formula so that it can be input into any cell, and
always multiply the cell above it by 12.36%?
In other words, when using the OFFSET function, how do I make the Reference
argument equal the cell that the formula is in?
Thanks in advance,
Dave.

 

[Ron Coderre]

If you copy the formula in A2 and paste it anywhere else,
the OFFSET function will reference automatically reference
the cell containing the formula.

Example:
A2: =OFFSET(A2,-1,0)*12.36%

Copy A2...paste to cell B10...and...
B10: =OFFSET(B10,-1,0)*12.36%

Does that help?
Post back if you have more questions.

Regards,

Ron
Microsoft MVP - Excel

 

[Gary''s Student]

=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0)*12.36%

 

[Harlan Grove]

I have the formula in A2:
=OFFSET(A2,-1,0)*12.36%

....

There's less than no benefit to this vs the much simpler

=A1*12.36%

How do I change this formula so that it can be input into any cell, and
always multiply the cell above it by 12.36%?

If you copy A2 and paste into any other cell, that other cell's
formula would also multiply the value of the cell above it by 12.36%.
If you want the exact same FORMULA, then switch to R1C1 style
addressing and use the formula

=R[-1]C*12.36%

Do you have some reason for using OFFSET rather than referring to
cells directly?

 

[Dave]

Hi Gary,
Thanks. I'll have to look at the ADDRESS function. I've not used it before.
Regards - Dave.

 

[RagDyeR]

How about using a "named formula"?

Click in A2, then, from the Menu Bar:
<Insert> <Name> <Define>

At the top, in the "Names In Workbook" box, type in
above

At the bottom, in the "Refers To" box, change whatever's there to:
=A1*0.1236

Then <OK>

Now, in *any* cell below a value you wish to multiply, simply enter,
=above

And you'll get your answer.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi,
I have the formula in A2:
=OFFSET(A2,-1,0)*12.36%
This multiplies A1 by 12.36.
How do I change this formula so that it can be input into any cell, and
always multiply the cell above it by 12.36%?
In other words, when using the OFFSET function, how do I make the Reference
argument equal the cell that the formula is in?
Thanks in advance,
Dave.

 

[Dave]

Hi Ron,
Yeah, I realised that, but it was a sort of academic question, to see if it
was possible. Thanks for replying.
Regards - Dave.

 

[Harlan Grove]

=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,0)*12.36%

OFFSET(INDIRECT(ADDRESS(...)))?!

If you're going to waste cycles using volatile functions, at least use
as few as possible.

=INDIRECT(ADDRESS(ROW()-1,COLUMN()+0))*12.36%

or

=OFFSET($A$1,(ROW()-1)-1,(COLUMN()-1)+0)*12.36% [or simplify to
mystify]

or better still

=INDIRECT("R[" & -1 & "]C[" & 0 & "]", 0)*12.36%

There's NEVER a good reason to use INDIRECT(ADDRESS(..)), and fewer
good reasons to use OFFSET(INDIRECT(..)).