My Excel spreadsheet contains 500 coordinate points from a 2D space. I want to find the mode value of these 500 coordinate points. The estimation of mode value of any set of numbers is pretty simple. It's simply the highly repeated number among the set of numbers. In excel:
=MODE (A1:A10)
yields mode of data from A1 to A10. However, a coordinate point is a pair of x and y coordinate. Calculating mode value of x and y coordinate individually may cause an error because individual x coordinate might be paired with many y coordinates and vice-versa. Is there any formula in excel to obtain mode value of paired numbers such as 2D coordinate points?
One way is to use a helper column to convert the coordinate pairs to a single number and then use MODE on the helper column.
The helper column formula would be something like =A5*100000+B5 where the 100000 is a large enough number to elevate the significant digits of the first coordinate beyond the significant digits of the second coordinate.