Hello!
I did a lot of work using excel a while back, but I got stuck doing some basic stuff for a long time which caused most of my knowledge to go down the drain as I wasn't using it... And I'm now trying to refresh my memory on how to use some of the more advanced formulas, and I came across one that I simply can't wrap my head around.
It's a formula that calculates the shortest distance between two matrix's with coordinates.
=LOOKUP(1,1/FREQUENCY(0,MMULT((B$1:C$10-E1:F1)^2,{1;1})),A$1:A$10)
I know that the range B:C contains the different positions that is being matched against E1:F1, and that A1:A10 is the name/value that is returned.
But I can't for the life of me now remember why I used this one, and how it actually works. I found the same formula on a websearch (which is probably where I found it in the first place)
Could someone break it down for me what the different parts does? as I would like to know how this thing actually works.
The source of the formula: worksheet function - Find the nearest set of coordinates in Excel - Stack Overflow
I did a lot of work using excel a while back, but I got stuck doing some basic stuff for a long time which caused most of my knowledge to go down the drain as I wasn't using it... And I'm now trying to refresh my memory on how to use some of the more advanced formulas, and I came across one that I simply can't wrap my head around.
It's a formula that calculates the shortest distance between two matrix's with coordinates.
=LOOKUP(1,1/FREQUENCY(0,MMULT((B$1:C$10-E1:F1)^2,{1;1})),A$1:A$10)
I know that the range B:C contains the different positions that is being matched against E1:F1, and that A1:A10 is the name/value that is returned.
But I can't for the life of me now remember why I used this one, and how it actually works. I found the same formula on a websearch (which is probably where I found it in the first place)
Could someone break it down for me what the different parts does? as I would like to know how this thing actually works.
The source of the formula: worksheet function - Find the nearest set of coordinates in Excel - Stack Overflow