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)


"Luke M" wrote:

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(Sta rt_1,ROW(A1)-1,2))-(OFFSET(Start_1,ROW(A1),2))),2)+POWER(((OFFSET(Sta rt_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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Frank Van Eygen" wrote:

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?