Finding The Hypotenuse Of From Two Pairs Of X,Y Coordinates

Feb 4, 2010

I have a map with several waypoints and would like to find the distance between any of these two waypoints. Sounds simple, but I have having an issue with the later parts of the excel file. Let me explain further.

There are over 100 waypoints on this map we have. Each waypoint has an, seemingly arbitrary, X-coordinate and Y-Coordinate. However, if you divide the coordinates by [X], they turn into miles. It should go without saying that finding the distance between two waypoints is rather simple mathematically, but I having an issue figuring out how to pull two random waypoints and having the distance automatically calculated.

So far, I just have column A with the waypoint name and with column B and C having the X and Y coordinates respectively. Is there anyway I can create some sort of call function that when two waypoint values are entered it would automatically calculate the distances?

ADVERTISEMENT

Finding Count Of Pairs From Lotto Data?

Oct 22, 2011

Is there any way to find pairs of numbers from lotto data ,starting from the highest to the lowest for the full 24 pairs? It does not matter if excel formula or Vb code.T

here small example;

DATARESULT20 27 28 34 43 44PairsCount13 14 18 32 34 4927, 34207 12 27 34 41 465, 47213 24 25 26 37 40 29, 38206 16 22 26 30 3426,37209 15 17 29 30 3915,33104 15 17 26 37 419,22115 19 25 36 44 4510 16 29 34 37 4711 15 32 33 37 3902 03 06 41 44 45 23 28 29 37 38 4205 07 09 10 22 4704 29 31 36 38 4804 17 20 26 29 3605 26 43 46 47 4906 07 29 32 41 46 11 27 46 47 48 4902 03 18 30 34 3503 19 32 40 47 4805 30 31 33 35 45

View 9 Replies View Related

Distance Between Coordinates?

Dec 8, 2013

I have created a sheet that shows distance between coordinates. Also working on something to put them in order of the closest point. I used a fixed point to find the nearest coordinate, but since i'm using a fixed point it doesnt go the order I want it to. I have attached the excel file I am working on.

View 11 Replies View Related

Numbering For Coordinates

Oct 24, 2007

how to get a single cell (C2) and (D2) to make the numbering format go from (## ## ##) to (######).

The Excel spread sheet is a coordinate converter, designed to take Degree's minuets seconds and convert it to Decimal Degrees, the formula is set up and work Great, but every time I copy and paste the coordinate to the excel spread sheet, i have to manuelly erase the spaces between the numbers so the formula can work properly. How can i get the cell to automatically delete the space between the numbers to save me time.(I.e 29 35 42.34325 -to-> 293542.34325)

View 10 Replies View Related

XYZ Coordinates Graph 3D

Aug 22, 2007

I have the following data:

X Axis - Years (2004, 2005 ,...)
Y Axis - Quantity (imported, exported, produaced)
Z- Axiz - Name of the products

I want to make a graph with teh X, y,z axis in excel. When i use the 3D graphs in excel, it is not taking the z vales connected to teh cell.

View 9 Replies View Related

Convert Lat/longs Coordinates

Jan 7, 2009

I'm trying to create a spreadsheet that will convert coordinates from DDMMSS.00 to DDMM.000. I found another thread that discusses this same issue, but I was unable to make it work for me. I'm attaching a sample spreadsheet that basically shows what I'm trying to do.

View 9 Replies View Related

Line Of Best Fit And More Using Cells As Coordinates

Aug 6, 2014

I have an image which i have reduced to a mathematical representation of light intensities. We can consider each cell in my spreadsheet as a pixel, the value within the cell corresponding to how much light that pixel it took in during an exposure. There is a trend along the pixels that i would like to isolate as exactly as possible: a diagonal line, not quite straight from the bottom left corner to the top right.

What I would like to do, if possible, is to treat each cell in my spreadsheet as a point on a coordinate plane, and to use some type of formula or macro to give me a line of best fit using the positions of the highest valued cells as the data points in my extrapolation.

The tool I'm looking for would take data organized like this:

1 1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 200 1 1 1
1 1 1 1 1 1 200 1 1 1 1
1 1 1 1 1 200 1 1 1 1 1
1 1 1 1 200 1 1 1 1 1 1
1 1 1 200 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1 1

And give me an equation of the form y=1x + b (describing where to find the maximum values among the cells), although my actually data will need to be fit to a higher order polynomial.

Once I can do this, I will also need a way to take perpendicular sums automatically along the line of best fit at distance of two pixels in both directions and a width of one pixel. You can imagine drawing a rectangular box perpendicular to the line of maximum values and adding the proportions of each cell enclosed by that box. This means that for the example above, i would get 209 for all five points of interest: the maximum value plus the two values of one in the upper left perpendicular direction, the two values of one in the lower right perpendicular direction, half the values of the two cells neighboring the exactly perpendicular cells and a quarter of the outermost cells touched by our imaginary pixel box.

The following image may be useful in clarifying what I'm looking for. The red bar represents the imaginary box containing perpendicular pixel sums, where the green beam is the line of best fit derived from my maximum values.

Attachment 337419

Also, here is a snippet of my actual data to experiment with in solving these strange and exotic problems

Attachment 337420

View 7 Replies View Related

Extract AutoCAD XYZ Coordinates

Jun 26, 2009

I am trying to extract (and separate into 3 columns) the numbers only, positive or negative portion of (XYZ) Coordinates copied from AutoCAD and pasted into column A
X = 96179.9699 Y = 61973.9793 Z = 1368.0025
In column B,C and D, I have tried to use Find Mid and other means

View 4 Replies View Related

Calculating Distance Between Coordinates

Jan 16, 2008

i use a Trimble Total Station Survey equipment, This is used for suveying and measuring distances between points on jobs. These points can be exported onto the pc as a text file and imported onto excel, the file has a northing and easting to locate the point these are comma separated.

I would like to know if excell is capable of working out a distance betwen two of these such points by using the two pairs of northing and easting coordinates. The maximum distance between two points is no more than 50 meters. If for example when imported onto excel point 1's coordinates are locted in cells A1 and B1 and points 2's coordinates are locted in cells A2 and B2.

View 9 Replies View Related

Calculate Angle From X, Y Coordinates

Dec 21, 2006

I have a userform, on it is a very large command button with a picture of a map loaded on the button. The button is 500 width(X) by 500 height (Y), so the center of the button would be at the location 250 (X) by 250 (Y). The button is coded for mousemove to get the X,Y cooridates and when you _Click the button a textbox gives the distance to the point(mousemove) from the center of the button. All that is good, but now I'd like to find the degree of the point referenced to a compass, ie, if the point is at 250 (X) and (Y) is less than 250 then the point would be due north at 0/360 degrees and if the point (Y) is greater than 250, it would be south of the center of the button. I hope this make sense. It's been a long time since trig. I've been searching but can't seem to find the correct formula to figure this out.

Here's the other problem, I'm doing this in powerpoint so I'm not sure if cos or tan functions are loaded in this program. I am posting this in the xl forum because I figure that's where most of the people who could figure this out are. If this is in the incorrect forum I can try to move it,

View 9 Replies View Related

Fill-in As Pairs

Apr 20, 2009

I'm looking for an easy way to fill-in 1600 cells, in Col. "A", with pairs of sequential numbers [from 1 to 800] - for example:

1
1
2
2
3
3
4
4
5
5
6
6

I managed to achieve something close with: =INT(ROW()/2.05)+1 but this is not accurate enough.

View 2 Replies View Related

3D Chart / Graph With Spherical Coordinates

Mar 1, 2008

Is it possible to create a chart using spherical coordinates (radius, theta, phi) in Excel? I want to create something similar to this. I would even be willing to write add-in code to create the graph from scratch, but I don't know if that's possible. Is it possible to create a new chart type without using the other chart types as a base?

View 5 Replies View Related

Count Geographic Coordinates Within A Range

May 12, 2009

I have 2 columns of longitude and latitude coordinates. I want to count coordinates within certain ranges. A bit like using the Frequency function but for 2 sets of values and 2 bins arrays. This would mean I could create a table with latitute bins as the row headers and longitude bins as the column headings. Would anyone know how to do this?

View 9 Replies View Related

Isolate XY Coordinates In Separate Cells

Sep 17, 2013

I have a cell in F2 that looks like this...

513 BLUE RIDGE
Kansas City, MO
(39.104810141000485, -94.47983043799968)

I need to isolate the coordinates in separate cells. IE "39.10481014000485" in cell J2 & "-94.47983043999968" in cell K2. The problem is that some of the cells don't have 14 numbers behind the decimal, and my formula will sometimes grab either the comma ( "," ) or the end parenthesis ( ")" ). Is there a formula I can use to isolate the two coordinate plots?

The good thing is every cell is formatted exactly like this. I had been using a formula that looks like this.

=MID(F2,FIND("(",F2)+1,18)

I think it's close. I just cant figure out how to tell the formula to stop at the first comma, and to stop at the end parenthesis for the second set of coordinates.

View 5 Replies View Related

Add Shape & Textbox Values For Coordinates

Jan 1, 2007

i am trying to enter the EndX coordinates (The third number: 500) by entering a number in a forms textbox

ActiveSheet.Shapes.AddLine(0, 100, 500, 100).Select

how can i break the code up to enter the coordinates via textbox's

View 6 Replies View Related

Calculate Relative Vector Coordinates

Oct 1, 2007

I am running a fairly complex simulation at work and have quite a few different functions in it. For the simulation to work properly, i need to have the Solver add-in and the Analysis ToolPak installed. Everytime that i try and use the simulation on a different computer (or if anyone else tries to run it) the add-ins must be enabled again. Is there a way to set up a macro to enable them when either i open the simulation (first preference) or when a button is pressed (2nd preference).

View 2 Replies View Related

Calculate Shortest Distance From Coordinates

Dec 5, 2007

I want to use a User Defined name look up its corresponding X/Y Coordinates , reference a list organized by categories, then go through each name in the list finding each's X/Y Coord Calculate distance.

SQRT((X-X)*(X-X)+(Y-Y)*(Y-Y))

where X and Y are Coordinates

& Return the name of the smallest Value.

View 9 Replies View Related

Matching Pairs Of Data

Dec 30, 2008

I'm not sure if it's possible to do this, but I have three lists of data. One is a complete list (for example, the numbers 1-25).
The next list is a subset of the complete list (e.g., 1,3,5,7,9). Attached to these (the subset list) is another list (let's say letters, so A goes with 1, B goes with 3, etc). I want to physically move the paired entries from Lists 2 & 3 so that List 2 matches up with List

1. Let's see if I can represent this visually:

I have:

1|1|A
2|3|B
3|5|C
4|7|D
5|9|E
6|
7|
8|
...
25|

I want:


1|1|A
2|
3|3|B
4|
5|5|C
6|
7|7|D
8|
...
25|

View 3 Replies View Related

Any Way To Store Pairs Of Data

Jun 9, 2014

I want to store a list of strings in an array with their position so that I can sort them alphabetically and then be able to put them back in their original order again e.g.

John 1
Charlie 2
Paul 3
Andy 4
etc,

So when they are alphabetized (is that a word?) they become

Andy 4
Charlie 2
John 1
Paul 3

My plan was to store them in an array, but I can't think how to do it. In python, I would just make a list of tuples, what is the best way to do this in VBA?

View 7 Replies View Related

Incrementing Pairs Of Data?

Oct 4, 2013

I need to complete what would seem a fairly simple incrementing task but I'm not getting the results that I'm looking for. I don't know much about macros so would prefer not having to use one but I'm willing to try if it's not too complicated.

I'm using Excel 2010 on a PC. For several projects, I need to increment cells that contain text and numbers. The column contains data in a cell (A1), followed by a blank cell (A2), then cell A3 contains new data followed by a blank in A4, etc. What I need to do is 1) copy the exact data in A1 to A2 and then 2) increment the number used in A1/A2 by 1 in A3, copy that into A4, and the pattern repeats down the column.

Here is an example of what I have in column A:
Test_01
[blank cell]
Test_02
[blank cell]
Test_03
[blank cell]
etc.

This is what I want as a result.
Test_01
Test_01
Test_02
Test_02
Test_03
Test_03
etc.

Do I need to save the data in the column as text, general, or numbers? Is there some function that would make incrementing the data possible as I've outlined above?

View 8 Replies View Related

Extract Even/odd Pairs From A Number

Jun 8, 2008

I would like to know if its possible to extract just the even/odd pairs from a number.

Exp. I have the number 123 in cell A1, and would like to extract the even/odd pair, which in this case would be 13, the cell A2. I would like to be able to do this without the use of "Isodd" or "iseven" functions.

View 9 Replies View Related

Show 3digits Under Pairs

Mar 16, 2009

Need formula? We have a pair of numbers in D1,E1,and F1..
We want only the numbers that have 12 from column A and B

66 from column A and B

18 from column A and B only

A B C D E F
1

12 66 18

456 123
129 667 481

667 234
123 366 881 .........

View 9 Replies View Related

Check For Pairs Occurrences

Jul 19, 2006

I am looking for a code which will check and display the Maximum Occurrences of pairs of 2 Characters.

In the attached file I tried to write some code but it seems to be wrong.

For your convinience, I entered the reuslts in a table (hopefully not mistaken).
The code should display a MsgBox saying that "The pair 'ab' was found 6 times in A1.

The string, in A1, can be any string in any length.

View 9 Replies View Related

Count Pairs In Range

Oct 20, 2007

I am trying to analyse a set of variables in my dataset.

The variables I have are numbers which are organised into a single column.

I want to count the number of times different variable pairings occur within my data.

An example dataset of mine is: 1,2,4,2,1,3,5,3,2,1

As the dataset contains 5 variables (1,2,3,4,5) the potential combinations are: 1,1; 1,2; 1,3; 2,1; etc.. with 25 in total.

I want Excel to move through this column starting with the first two cells and step down 1 cell each time and then tell me the number of times each of the combination occurs.

View 3 Replies View Related

Extracting Row And Column Header Coordinates From Value Search?

Mar 6, 2014

If the could be longer I would have included "when multiple cells can contain the same value"

I have a very large dataset that I would like to apply the following searches to, but I will include a smaller sample that gets at the point.

For example; in the following sheet:
Cat Dog Lizard
Red 1 2 3
Yellow 4 5 6
Green 7 8 1

If I search for "2", I would like to get back "Red" and "Dog". I was able (by looking at other posts) come up with a series of formula that allow me to obtain the sheet coordinates and convert them into column and row headers (using SUMPRODUCT Row/Column); however, when multiple cells contain the same value, the output becomes the sum of that coordinate and is no longer a reference to a specific header, so a search for "1" would not give me "Red" and "Cat". Ideally, if I searched for "1" I would get back "Red" & "Cat" as well as "Green" & "Lizard", but I would be satisfied with a formula that even just gave me back one of the two header pairs.

JordanSample Workbook.xlsx

View 5 Replies View Related

Group Of Decimal Coordinates - How To Pool Those Which Are Close One Another

Jul 5, 2014

How to set a formula to calculate distances (by using a function I provided) among all the observations (each one with a latitude and longitude value expressed in decimals) in an excel sheet. Here it is the previous thread.

I slightly modified the solution MarvinP suggested to me by using my own function to calculate distances among coordinates.

Attached you can find the excel with this. You will see that on the right side of the sheet, in yellow background, there is a matrix whose non-blank cells each one reporting a case of close coordinates (e.g.: 4-5 means that observations 4 and 5 are less than 15 Km apart one from another). Cells of the left-side matrix contains the function used to calculate distances among each observation with any other. Matrices are 116X116.

However, I am now jammed because I don't find an easy way to pool the observations found to be close each other (I have used a distance threshold of 15 Km - you can see it in the formula inside each cell of the "yellow" matrix").

The ideal way to establish new coordinates values for observations close one another would be by using coordinates which are at an intermediate position, however, this is not my priority, it would be OK also using one arbitrary value for all of them, i.e. picking up one pair of coordinates of one of the close observations and attribute this to the other. I have seen that, by excluding nonsensical similarities (e.g. 1-1, 2-2, etc.), there are groups of close observations of size 2 (e.g. 4-5) up to 10 (nine observations very close to a given one).

Attached File : CoordinatesPooling.xlsx‎

View 4 Replies View Related

Smart Generation Of Coordinates For Excel Shapes VBA

Sep 5, 2013

I'm using Excel to create a flowchart from a configuration sheet. I'm not manually entering the coordinates. Instead, I draw the start step in a set location. The next step(s) that's connected to the first step is placed directly below the start step. if there's more than one step connected to the start step, it's placed at the same y-coordinate but shifted along the x-axis.

The problem I'm running into here is this: say I have three steps connected to the start step and three further steps connected to the first of the three connected to the start step, how can I keep track of this and shift the other two steps connected to the start step along the x-axis so they're not positioned above the four steps below the first step (and as such, in the same location as other steps)? I'm not sure if my description is particularly clear or not so I've attached a drawing that will hopefully clear things up.

Is there any way of giving Excel shapes a "weight" so that other shapes won't line up on top of them?

View 3 Replies View Related

Macro To Solve For Cartesian Coordinates On An Ellipse

Feb 25, 2009

Does anyone have a macro they have created or know of that can solve the X and Y coordinates on an ellipse? Would you be willing to share here or send it to me in a personal email?

View 9 Replies View Related

Getting Max Value Of A Point Inside A Square Defined With X,y Coordinates

Apr 18, 2006

I have a square which is defined with x,y coordinates (roughly 2000 points), and for each point I have a value. And I have to divide this square in to 9 equal sub grids, and find the max value in each sub grid. I have all the coordinates for the sub grids. But I cant get the max value of the specified cells.

I am thinking something like this:

if 0<x>10 and 0<y<10 then it is sub grid 1 and I want to find the max value there
and if 0<x>10 and 10<y<20 then it is sub grid 2 and so one..

View 9 Replies View Related

ActiveCell.Address - Discarding Fixed Coordinates

Jul 17, 2006

I'm attempting to write a piece of code that builds a formula dependent on the location of the cell it is being created in. The relevant line of code currently reads as follows:

ActiveCell.Offset(0, 5).Value = "=" & ActiveCell.Offset(0, -5).Address & " & " & ActiveCell.Offset(0, -6).Address

Which when activated in cell A3, will enter the formula =$A$3 & $A$2

However, I would like to know if there is a way of automatically building a formula like this, only without the absolute references. Reason being, this formula is used in a large table, which is sorted in date order automatically when the sheet is activated. Since the code generated will point at the row it was generated on, when the table is sorted, this formula will calculate the incorrect result.

View 4 Replies View Related

Data Type Of Coordinates Of 2-dimensional Array

Apr 29, 2007

I have a big array "DataArray" and want to access it:

For i = 1 To 4
variable = DataArray(SourceArray(i))
Next i

"DataArray" has two dimensions, so SourceArray has to consist of data like this:

SourceArray(1) = 1,2
sourceArray(2) = 2,4
etc

What data type does Sourcearray have to be? Integer doesnt seem to work, and DataArray doesnt like a string as coordinates. I have a workaround with two different arrays of integer for x and y coordinates, but this cannot be it.

View 4 Replies View Related

Modify Free-form Shape Coordinates

Feb 21, 2008

Right now I'm modifying a figure based on size data in an excel table.

I'm modifying the X/Y coordinates of the points of the freeform shape with VBA, however, I have to use absolute x,y coordinates.

I'd like to be able to move the shapes throughout the sheet. With fixed coordinates, this makes it difficult. I'm hoping there's an easy way to it

View 3 Replies View Related

Function To Create Currency Pairs

Jun 22, 2014

I am trying to figure out a function that creates currency pairs, for example EUR/USD etc based on a limitation.

I attach you a workbook with the details. The first step I have accomplished, I am having trouble to the second step as you will see in the sheet.

Test.xlsx

View 8 Replies View Related

Matching Two Pairs Of Cells Between Two Lists

Sep 13, 2013

I have a long master list of registered members, column C has last name, column D has join date.

Now I have a short list of last names with join dates.

I want to compare the short list with the master list to find names that are already there, by comparing the last name and join date.

View 8 Replies View Related

Counting Pairs From 4 Digit Lottery

Aug 2, 2008

I am tring to list and count how many pairs in a 4 digit lottery?

no duplicates ...

View 13 Replies View Related

Matching And Sorting Non-Identical Row Pairs

Nov 9, 2008

I have a database with 6 columns in play (there are actually other columns but they are not relevant). I'll call the columns A through F. I would like to be able to match certain counterpart rows together, do a sort placing the counterpart rows adjacent to one another, and then count how many pairs I have. (Some rows will have no counterparts.)

Here is a micro-illustration of the database:

______A______B______C______D________E_____F
R1___01-03___54____959____nsneakr___24____yes
R2___01-04___67____454____adidaht____53____yes
R3___01-10___42____344____calb3wd___11____no
R4___01-19___67____454____adidaht____53____no
R5___01-25___54____959____nsneakr___24____yes
R6___02-02___54____959____nsneakr___24____no
R7___02-14___54____959____nsneakr___24____no

I basically need to devise a formula or script that pairs together two rows that fit the following criteria:

1) The rows are identical in Columns B, C, D, and E.
2) The rows are not identical in Column F (i.e., one half of the pair should have "yes" and the other half should have "no")
3) The rows are as close together as possible according to the date sequence in Column A. For example, Row 1 should pair with Row 6, and Row 5 should pair with Row 7. Row 1 should not pair with Row 7, and Row 5 should not pair with Row 6. **This criterion seems tricky because R5 and R6 would technically fit the requirement for pairing, were it not for the fact that R1 comes earlier in the sequence.**

View 2 Replies View Related

Formula Grouping Number Pairs

Aug 14, 2007

How do I express the following as a formula for range f2:h2:

case 1
if any number is equal to 0 or 5, then 1
if any number is equal to 1 or 6, then 2
if any number is equal to 2 or 7, then 3
if any number is equal to 3 or 8, then 4
if any number is equal to 4 or 9, then 5

example:
f2:h2=3,5,9
result
415

f2:h2=0,2,7
result
133

View 9 Replies View Related

Can Work Out Unique Pairs Of Numbers

Aug 14, 2008

I need to pair up 20 people in sequence for a workshop, so I number the people and can rotate people numbered 1-10 with 11-20 easy enough, but when it gets to pairing 1-10 together in pairs and 11-20 in pairs.

Is there a formula I could use in Excel to work out the easiest pairing for me?

View 9 Replies View Related

Combination Of Pairs, Power And String

Aug 8, 2006

I have a string of n pairs and want to check various combination of that string.
Example: Pairs 58 78 15
Since I know I have 3 pairs (but it can be 2 or 4), I know the number of combination I want to test, ie 2 power 3 = 8 combinations. How can I program a code creating the various strings, ie 587815, 587851, 588715, 588751, 857815, 857851, 858715, 858751 ?

This is what I have so far (not much):

Public unique_pair 'number of pairs provided by another macro
Public mystring 'provided by another macro
Sub make_guess()

Dim number_of_combination, i
number_of_combination = 0


number_of_combination = 2 ^ unique_pair
For k = 1 To number_of_combination

'how to generate the various string ????

Next k
End Sub

View 9 Replies View Related

Distance Between Xy Coordinates Not Converting Properly With Absolute Cell Use

May 14, 2014

I have a spread sheet that I am finding distance between xy coordinates and then trying to convert that distance into miles.

VB : =If(E2+F2=0,"",SQRT((E2-B2)^2+(F2-B3)^2))/1.6093

However, the division part only works in the first cell in the column correctly and then does not work in the rest of the column cells correctly. The difference in the code is that the first cell has no "$" in it and the rest of the cells in the column have the "$" in them.

VB : =If(E2+F2=0,"",SQRT(((E2-B$2)^2)+(F2-B$3)^2))

View 5 Replies View Related

Search For Value In Table Return Separate X And Y Coordinates Of All Its Locations?

Aug 22, 2013

i have a large table and i need separate x and y coordinates(to plot on a graph) of a particular repeated value that is known to me in a large table of numbers. how can i do this?

View 4 Replies View Related

ADVERTISEMENT