relative offset to a named cell


F

Frank Van Eygen

In cell A1 I have a cell named "Start_1", in cell A10 a cell named "End_1"
In columns B, C and D are numbers
In cell E1 a SQRT-calculation involving cells B1 to D2:
=SQRT(POWER((B1-B2);2)+POWER((C1-C2);2)+POWER((D1-D2);2))
In cell E1 a SQRT-calculation involving cells B2 to D3:
=SQRT(POWER((B2-B3);2)+POWER((C2-C3);2)+POWER((D2-D3);2))
and so on, untill E10 (where cell A10 named "End_1" is)
I would like to autofill column E using the named cells as a starting point
with OFFSET or an other function.
Until now I tride in cell E1:
=SQRT(POWER(((OFFSET(Start_1;0;1))-(OFFSET(Start_1;1;1)));2)+POWER(((OFFSET(Start_1;0;2))-(OFFSET(Start_1;1;2)));2)+POWER(((OFFSET(Start_1;0;3))-(OFFSET(Start_1;1;3)));2))
In cell E2:
=SQRT(POWER(((OFFSET(Start_1;1;1))-(OFFSET(Start_1;2;1)));2)+POWER(((OFFSET(Start_1;1;2))-(OFFSET(Start_1;2;2)));2)+POWER(((OFFSET(Start_1;1;3))-(OFFSET(Start_1;2;3)));2))
My question is how can I use the autofill function in column E so that I
don't have to change the rows and cols arguments manually in each OFFSET
function in the formula?
Is there a way to use a row-reference relative to a named cell in an OFFSET
function?
 
Ad

Advertisements

L

Luke M

I'm not sure why you're using OFFSET, as XL will automatically adjust your
formulas as you desired with your starting equation. But, since you ask...

=SQRT(POWER(((OFFSET(Start_1,ROW(A1)-1,1))-(OFFSET(Start_1,ROW(A1),1))),2)+POWER(((OFFSET(Start_1,ROW(A1)-1,2))-(OFFSET(Start_1,ROW(A1),2))),2)+POWER(((OFFSET(Start_1,ROW(A1)-1,3))-(OFFSET(Start_1,ROW(A1),3))),2))

Copying this down should result in the desired output. But again, your first
formula does the same thing, and is faster in calculation speed.
 
S

Shane Devenshire

Hi,

Using offset will result in far slower calculation speeds. Your question
would make more sense if you wanted to use range names or cell addresses with
INDIRECT to determine the start and end point for the calculations. As it is
you are making a simple solution in to a complex one.

Maybe you should tell us not how you want to change the formula by why.
AutoFill works just fine with your formula in its original form.
 
Ad

Advertisements

F

Frank Van Eygen

Thank you Luke M for the help.
The "But, since you ask..." is especially appreciated.

The idea is to write a macro for this formula (among a lot more) but make it
also adjustable “on-sheet†once the code has run.
By using “…ROW(A1)-1 and ROW(A1)†as you suggested it is possible for me to
recalculate the sheet by simply changing the Start_1 and End_1 positions and
auto-filling and dragging a little.

The basic function of the workbook is to calculate smooth 3D movement of a
welding robot and make adjustments (using Excel) between two “calibratedâ€
robot positions (Start_1 and End_1)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top