calculating the distance between cells in excel (randomize moving cells)

[rosasellen12]

Suppose I want to compute the distance between a 3by4 cells in a square grid in excel. The distance between two cells is 1m.
Each grid cell is a number between 1 and 12 but randomized so every number can be anywhere.

For instance: (example in the bottom-left side of the grid):

1___2___3___4

5___6___7___8

9__10__11__12


distance between cells 1 and 5 is 1
distance between cells 1 and 6 is 2
distance between cells 1 and 12 is 5

the grid can also be this and other randomized forms:
1___7___9___12

8___2___6___11

10__8___3___4

Is it possible to have the formula to be able to calculate all of the distance between 2 cells????


Thanks for any help in advance!

 

[steve the fish]

This is a bit long winded but i cant think of another way offhand.

=ABS(AGGREGATE(14,4,($B$15:$E$17=$A2)*(ROW($B$15:$E$17)),1)-AGGREGATE(14,4,($B$15:$E$17=B$1)*(ROW($B$15:$E$17)),1))+ABS(AGGREGATE(14,4,($B$15:$E$17=$A2)*(COLUMN($B$15:$E$17)),1)-AGGREGATE(14,4,($B$15:$E$17=B$1)*(COLUMN($B$15:$E$17)),1))

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

calculating the distance between cells in excel (randomize moving cells)

Suppose I want to compute the distance between a 3by4 cells in a square grid in excel. The distance between two cells is 1m. Each grid cell is a number between 1 and 12 but randomized so every numb...

stackoverflow.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[rosasellen12]

This is a bit long winded but i cant think of another way offhand.

=ABS(AGGREGATE(14,4,($B$15:$E$17=$A2)*(ROW($B$15:$E$17)),1)-AGGREGATE(14,4,($B$15:$E$17=B$1)*(ROW($B$15:$E$17)),1))+ABS(AGGREGATE(14,4,($B$15:$E$17=$A2)*(COLUMN($B$15:$E$17)),1)-AGGREGATE(14,4,($B$15:$E$17=B$1)*(COLUMN($B$15:$E$17)),1))

wow, The formula seems to work also when the cell numbers are in random

I'm not sure how aggregate works, but I'll check it out! thanks