Could someone explain formula (Lookup/frequency/mmult)?

Rolle88

New Member
Joined
Nov 18, 2015
Messages
5
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Rolle88

New Member
Joined
Nov 18, 2015
Messages
5
Or the things I'm really interested in is this part:
MMULT((B$1:C$10-E1:F1)^2,{1;1}))
as I can't figure out what {1;1} does in the formula
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi
Welcome to the board

When you calculate the distance between 2 points you add the squares of the difference in X and Y.

(B$1:C$10-E1:F1)^2

Gives you those squares of the differences in terms of a nx2 matrix, like

2,5
3,1
5,8
... etc

You use the matrix multiplication to add each row, so that you get

7
4
13
... etc

that are the squares of the distances.

You'll then find the minimum of those values.

 

Rolle88

New Member
Joined
Nov 18, 2015
Messages
5
Okey thanks, that helps a bit
But what's the point of {1;1}?

Feeling a bit think right now, as I do understand the Sqrt((x1-x2)^2*(y1-y2)^2) for normally calculating distances, but that {1;1} is just confusing right now, as I can't find any other example where the mmult formula is used like this
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

If you remember the rules of matrix multiplication you know that the matrices can only be multiplied if the number of columns of the first matrix is equal to the number of rows of the second matrix.
The {1;1} is a vertical array that is multiplied by the nx2 matrix and adds the elements of each row.
 
Last edited:

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Remark: If you don't remember how to work with matrices you can also just add the values:

=LOOKUP(1,1/FREQUENCY(0,(B$1:B$10-E1)^2*(C$1:C$10-F1)^2),A$1:A$10)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top