Offset function
 

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.

Offset function
 

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


"Dave" wrote in message
...
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.

Offset function
 

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


--
Gary''s Student - gsnu200790

Offset function
 

Dave wrote...
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?

Offset function
 

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

Offset function
 

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!
================================================== ===

"Dave" wrote in message
...
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.

Offset function
 

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.

Offset function
 

Gary''s Student wrote...
=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(..)).