Distance formula adaptation

[aardvark23]

Hi,

I have been using a distance formula based on Excel grid references, I am now using a formula that uses customised grid references, I am wanting to adapt the formula to work using these NEW references

Old formula
=SQRT(SUMSQ(COLUMNS(INDIRECT(A3):INDIRECT(C3)) - 1, ROWS(INDIRECT(A3):INDIRECT(C3)) - 1))


Location 1 eg H8 would be in A3 Location 2 eg H10 would be in C3 result 2.0 would be displayed in cell with formula.

Using my new formula H8 is now 5,1 and H10 is 5,3

I have attached file to clarify (hopefully)

Thanks
Peter

[arthurbr]

No file attached Peter

[DonkeyOte]

A sample file would help - based on your post and prior question which utilised a 0 based index - we assume matrix is physically located C7 (0,0) onwards.

In which case replace references to:

INDIRECT(A3)

with

INDEX($C$7:$Z$100,1+REPLACE(A3,1,FIND(",",A3),""),1+LEFT(A3,FIND(",",A3)-1))

Repeat for references to C3 ... modify the INDEX range reference (ie C7:Z100 as appropriate of course).

[aardvark23]

Soz didnt realise file was too big ...uploaded smaller file :-)

[DonkeyOte]

FWIW you should (really) store the x,y co-ordinates in separate cells - that would make this much (much) simpler.

[aardvark23]

Hi again


Have I changed the formula correctly ?

OLD FORMULA


=SQRT(SUMSQ(COLUMNS(INDIRECT(A3):INDIRECT(C3)) - 1, ROWS(INDIRECT(A3):INDIRECT(C3)) - 1))



NEW FORMULA

=SQRT(SUMSQ(COLUMNS(INDEX($C$7:$AF$56,1+REPLACE(A3,1,FIND(",",A3),""),1+LEFT(A3,FIND(",",A3)-1))INDEX($C$7:$AF$56,1+REPLACE(C3,1,FIND(",",C3),""),1+LEFT(C3,FIND(",",C3)-1,ROWS(INDEX($C$7:$AF$56,1+REPLACE(A3,1,FIND(",",A3),""),1+LEFT(A3,FIND(",",A3)-1))INDEX($C$7:$AF$56,1+REPLACE(C3,1,FIND(",",C3),""),1+LEFT(C3,FIND(",",C3)-1))

This returns a 'the formula have typed contains an error' message

If it would make it easier to split the X,Y axis could I type the coordinates into a cell as eg. 12,345 and have 'text to columns' split to 12 & 345 in hidden cells then use the hidden cells to creat a simpler formula

This seems to be getting a bit (lot) confusing

[DonkeyOte]

If you're willing to split the co-ordinates do so, disregard the single cell approach as it's very cumbersome even if you correct for missing characters etc...

Assume A3 holds x,y co-ordinates (eg 5,40) - then let's assume for sake of demo. that:

Y3: holds x value for A3 - ie 5
=1+LEFT(A3,FIND(",",A3)-1)

Z3: holds y value for A3 (ie 40)
=1+REPLACE(A3,1,FIND(",",A3),"")

as before we're assuming A3 is format as Text and a 0 based co-ordinate system (C7 = 0,0)

Your references to:

INDIRECT(A3)

become

INDEX($C$7:$AF$56,Z3,Y3)

[aardvark23]

Hi,

I am gettin myself in a muddle now could you implement the formula onto the file I have attached ?


Thanks very much

[DonkeyOte]

I think perhaps we're guilty of over complicating this.

Y3: =1+LEFT(A3,FIND(",",A3)-1)
Z3: =1+REPLACE(A3,1,FIND(",",A3),"")

AB3: =1+LEFT(C3,FIND(",",C3)-1)
AC3: =1+REPLACE(C3,1,FIND(",",C3),"")

Your calculation is then

=SQRT(SUMSQ(AB3-Y3,AC3-Z3))

As you alter A3/C3 all should update accordingly.

[aardvark23]

that does it
just one thing now ...the Y, Z & AB, AC cells are not automatically changing when I input date into A3 and C3
I am having to manually go to the data toolbar > text to columns then set choose the destination again ?

[DonkeyOte]

You shouldn't need to (nor want to) use text to columns - ensure you're in Auto Calculation mode (formulas tab - Calculation Options)

[aardvark23]

that does it
just one thing now ...the Y, Z & AB, AC cells are not automatically changing when I input date into A3 and C3
I am having to manually go to the data toolbar > text to columns then set choose the destination again ?

Hi again,

Yeah It's working fine now, how can I change it to accept bigger coordinates ie, ***,*** or **,*** or ***,**
its dependant on the position of the comma

[DonkeyOte]

Yeah It's working fine now, how can I change it to accept bigger coordinates ie, ***,*** or **,*** or ***,**
its dependant on the position of the comma

No, it's not.

The formulae in Y3,Z3,AB3 & AC3 will all work irrespective of the position of the comma (assume values either side thereof are valid numbers and that cell is formatted as Text (per prior discussion)).

Post a sample.

If you're getting an error using these values in an INDEX call check your INDEX range encompasses the passed co-ordinates, ie

=INDEX(A1:B10,15,12)

would fail given there is neither a row 15 nor column 12 in our 10 x 2 matrix.

[aardvark23]

Many thanks for all your help Donkey all is working ok now