The_Train
Established Member
- Joined
- 2 Jun 2018
- Messages
- 4,358
Wasn't sure where to post this but thought this section might be where others who use spreadsheets for their outings are hiding
I'm currently devising a 'one stop shop' database for myself and I've hit a bit of a problem with regards to the haulage aspect of it. I wasnt going to bother with haulage as it's not a priority of mine but I do record my mileage per loco/unit so thought it best to include it now save trying to add it in later. Basically I'm looking to include a 'Haulage' tab where I manually input the number of the loco/unit along with the miles accumulated on that trip (and other info but that's irrelevant to my problem - essentially a haulage log). Through a SUMIF formula this information is then allocated to that loco/unit within its own tab - e.g. if I had 3 separate instances of haulage behind 87002, the spreadsheet would work out the total mileage for those 3 outings and add that to the mileage column for 87002 I'd have in the Class 87 tab.
The problem I'm having is that I work out my mileage using the fantastic railmiles mileage engine which gives distance in miles and chains but getting a spreadsheet to understand that 1mi 69ch + 0mi 31ch = 2.20 and not 2.00 is proving difficult because spreadsheets automatically assume any decimal to be to 0.100=1 not 0.80=1.
I've found help elsewhere which has helped me come up with an array formula which works by modifying the decimal to 0.80. Unfortunately I've only been able to get this to work in its own right and I'm struggling to make this formula work alongside the SUMIF formula I mentioned earlier.
Hopefully this makes sense and any suggestions to solve this would be very much welcomed
I'm currently devising a 'one stop shop' database for myself and I've hit a bit of a problem with regards to the haulage aspect of it. I wasnt going to bother with haulage as it's not a priority of mine but I do record my mileage per loco/unit so thought it best to include it now save trying to add it in later. Basically I'm looking to include a 'Haulage' tab where I manually input the number of the loco/unit along with the miles accumulated on that trip (and other info but that's irrelevant to my problem - essentially a haulage log). Through a SUMIF formula this information is then allocated to that loco/unit within its own tab - e.g. if I had 3 separate instances of haulage behind 87002, the spreadsheet would work out the total mileage for those 3 outings and add that to the mileage column for 87002 I'd have in the Class 87 tab.
The problem I'm having is that I work out my mileage using the fantastic railmiles mileage engine which gives distance in miles and chains but getting a spreadsheet to understand that 1mi 69ch + 0mi 31ch = 2.20 and not 2.00 is proving difficult because spreadsheets automatically assume any decimal to be to 0.100=1 not 0.80=1.
I've found help elsewhere which has helped me come up with an array formula which works by modifying the decimal to 0.80. Unfortunately I've only been able to get this to work in its own right and I'm struggling to make this formula work alongside the SUMIF formula I mentioned earlier.
Hopefully this makes sense and any suggestions to solve this would be very much welcomed