 | Home > Off Topic > Creating a mileage graph/sheet thing in excel? |
 
|
|
|
| OsloBlue Member Since: 14 Jul 2018 Location: Essex Posts: 822   
|
Yeah sorry its kind of hard to do without using macros unless you want to enter data manually.
Th problem is in excel you can only really sort data by Alphabetical, alpha-numerical or smallest to longest. The problem is the distance in cell A1 to destination A2 would not consistently be smaller or larger than the distance from say Cell A1 to A2 as would cell C2 to A2 Basically no pattern to data. What you could do is make a table so Column A | column B | column C Location A | Location B | Distance Jims location | OBs location | Distance So on so forth but you would need to manually list all the variables... Then go to Insert tab > insert Pivot chart and it should Group All start locations on one axis and All finish locations and list the distances between the two... I'm on IG: https://www.instagram.com/osloblue42/ Current: TD5 '110 "Lucinda" Thread here: https://www.defender2.net/forum/topic62562.html  |
||
6th Jun 2019 7:56pm |
|
| LandRoverAnorak Member Since: 17 Jul 2011 Location: Surrey Posts: 11240 
|
Given how it's meant to be used, the most obvious structure is to list the places alphabetically, so they're easier to find. The actual distances between places are all discreet facts that don't relate to each other, so it doesn't matter where they're positioned. Exactly like the example you've posted. Darren
110 USW BUILD THREAD - EXPEDITION TRAILER - 200tdi 90 BUILD THREAD - SANKEY TRAILER - IG@landroveranorak "You came in that thing? You're braver than I thought!" - Princess Leia |
||
|
6th Jun 2019 8:01pm |
|
| diesel_jim Member Since: 13 Oct 2008 Location: hiding Posts: 6031 
|
Thanks both; yes makes sense that they're not related to each other (dstance wise), so i've just alphabetecised my list and will manually add the distances.
There's only 36 places at the moment so that'll take me a while... time for a beer I think. Thanks again |
||
|
6th Jun 2019 8:05pm |
|
| OsloBlue Member Since: 14 Jul 2018 Location: Essex Posts: 822
|
36 Places is 36² pieces of data you need
1296 entries... I'm on IG: https://www.instagram.com/osloblue42/ Current: TD5 '110 "Lucinda" Thread here: https://www.defender2.net/forum/topic62562.html |
||
|
6th Jun 2019 8:08pm |
|
| diesel_jim Member Since: 13 Oct 2008 Location: hiding Posts: 6031
|
1260 I reckon.... as I'll need to ignore 36 of them (or set as 0).... same places on the vertical & horizontal will have no distance.
Still enough mind!  |
||
|
6th Jun 2019 8:22pm |
|
| LandRoverAnorak Member Since: 17 Jul 2011 Location: Surrey Posts: 11240
|
The data set is triangular, like the example. Therefore it's only ((36(36+1))/2)-36 = 630 entries. (Can you tell I'm bored?) Darren 110 USW BUILD THREAD - EXPEDITION TRAILER - 200tdi 90 BUILD THREAD - SANKEY TRAILER - IG@landroveranorak "You came in that thing? You're braver than I thought!" - Princess Leia |
||
|
6th Jun 2019 9:18pm |
|
| diesel_jim Member Since: 13 Oct 2008 Location: hiding Posts: 6031
|
Cool, I was only 600 out.....
this rate I'll be finished by 0200.... |
||
|
6th Jun 2019 9:45pm |
|
| lohr500 Member Since: 14 Sep 2014 Location: Skipton Posts: 1283 
|
If you can build the data table in three columns so it shows Place A , Place B and Mileage, then you could use the vlookup function in conjunction with creating a unique identifier string (Place A & Place B) to populate the correct mileages for each position in the triangular grid.
If you PM me the basic spreadsheet with the data (in any order) , I'll have a go at setting it up for you and send it back with the formulae in place 
There may be a smarter way with Access/Macros/SQL but that's beyond my ability. (And even my idea may not work but I'm happy to give it a go!!!) |
||
|
7th Jun 2019 12:55pm |
|
| windy81 Member Since: 14 Mar 2018 Location: North Wales Posts: 311  
|
Only way to do that is manually input all the data as the routes are never the same depending where you're going.
A labour of love that would be. Macros and v lookups are all very clever but they never inspire me with confidence when i need to rely on the data. |
||
|
7th Jun 2019 4:28pm |
|
| diesel_jim Member Since: 13 Oct 2008 Location: hiding Posts: 6031
|
Thanks all.
I've started manually doing it via waypoints on memory map. I'm about half way through it now. If anyones interested, myself and a couple of mates are into radios. be it CB, PMR 0.5w and I've got some business spec 5w (with licence, thanks...) VHF's, so when we're out laning or at work, we'll text each other and see who's near a high point, and have a chat, test ranges etc. So the chart was to measure high points. Plenty around the plain and Devizes, and Swindon, Lambourn and down to Beacon hill south of Newbury Bit sad I suppose, but other people kick a ball around with 8 or 9 other sweaty blokes, or knock a small ball around a field full of holes so.... |
||
|
7th Jun 2019 8:40pm |
|
| LandRoverAnorak Member Since: 17 Jul 2011 Location: Surrey Posts: 11240
|
Not sad at all. Quite interesting, actually. Darren
110 USW BUILD THREAD - EXPEDITION TRAILER - 200tdi 90 BUILD THREAD - SANKEY TRAILER - IG@landroveranorak "You came in that thing? You're braver than I thought!" - Princess Leia |
||
|
7th Jun 2019 10:59pm |
|
| lohr500 Member Since: 14 Sep 2014 Location: Skipton Posts: 1283
|
Hi diesel_jim,
I've played around in Excel and built out a spreadsheet that will populate the mileage data into the distance chart grid for you. This will save you manually keying in each cell into the grid. It will still need a base table with three columns (which is where the hard work will be! ): Location , Location, Distance. Also, if you add further locations/distances into the base table in the future, then extending the distance chart grid will be relatively easy. Just a case of adding the full list of locations to the X & Y axis and the vlookup formulae will grab the correct distance value for each combination of locations
As per earlier post, I'll send you my email address so you can send me the spreadsheet once you have some data in it. I'll then end it back with my additions. |
||
|
8th Jun 2019 12:41pm |
|
| Sturdaa Member Since: 14 Jan 2015 Location: West Calder Posts: 23 
|
I’m fairly sure the vlookup function can be used to do this - I’d need to play around to verify though.
|
||
|
9th Jun 2019 8:35am |
|
| lohr500 Member Since: 14 Sep 2014 Location: Skipton Posts: 1283
|
Yep, I have a model built using the vlookup function. I just need the input data to try it in anger
|
||
|
9th Jun 2019 8:59am |
|
|
|
| All times are GMT + 1 Hour |
< Previous Topic | Next Topic > |
Posting Rules
|
Site Copyright © 2006-2024 Futuranet Ltd & Martin Lewis
