Closed Thread
Results 1 to 5 of 5

Trying to build a navigation Route card for Royal Marine Training

  1. #1
    Registered User
    Join Date
    10-17-2018
    Location
    Plymouth
    MS-Off Ver
    Excel 2021
    Posts
    96

    Trying to build a navigation Route card for Royal Marine Training

    Hello.

    As my title states I am trying to build a route card that automatically works out back bearings, time and pace.
    I have filed out the form with some examples on how I would like this to be done.

    1. I would like the grid bearing column (E) to automatically input into the Mag bearing column (F). What goes in the MAG column depend on the GMA input (calculations page) C2. For example if I put 3200 in Grid (column E) 3204 should appear in MAG (F), as it does. I would not want the GMA figure to appear in the MAG column unless a Grid figure has ben entered. As you can see it says 4 all the way down the MAG column.

    2. I would like the Back (G) column to auto work out the 180 degree opposite to the mag (F) bearing. The military work n MILS not Degrees 360 deg = 6400 Mils. Therefore I need a formula where if the mg (F) is more than 3200 the opposite is -3200 and if F is less than 3200 then you add 3200.

    E.g A mag (F) of 600 would give a BACK (G) result of 3800.
    E.g A mag (F) of 3800 would give a BACK (G) result of 600.

    3. My pace is 70 paces per 100m see (calculations E2). I would like it to automatically work this out.

    eg if 200 is put in Dist. (H) then 140 will automatically be inputted in Pace (I)

    4. In the Time (K) column I would like this the be automatically worked out. This will be worked out by using dist. (H) x calc G2 + Elev. (J)

    eg. if I walk 100m at 4kph it will take me 1.5 mins. However if I have an elevation of 1 then I will add another min on to my time.

    I have tried to have a crack at this but as you can imagine Royal Marine training barely allows much time to sleep, let alone embellish excel spreadsheets. Any assistance will be greatly appreciated. An example of how I would like it to look has been attached
    Attached Files Attached Files

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Trying to build a navigation Route card for Royal Marine Training

    1) Change the formula in F5 to =IF([@GridB]>0,[@GridB]+Calculations!C$2,"") and copy down.

    2) In G5 and copied down =IFERROR(IF([@MAGB]>3200,[@MAGB]-3200,[@MAGB]+3200),"")

    3) In I5 and copied down =[@DIST]*(Calculations!E$2/100)

    4) I'm stuck on this one. You state 100m at 4kph will take 1.5 mins but with an elevation of 1 it will add 1 min. What would be the extra time for 200m at that pace and elevation? What would be the extra time for different elevations? Is there a calculation you use to work that out?

    This in K5 would give you the 2.5 you're looking for but it doesn't seem to me to work universally. It needs an element to calculate the extra time for elevation over distance depending on pace, but without more info I don't know what that should be.
    =[@DIST]/((Calculations!G$2*1000)/60)+[@[Elev.]]

    BSB
    Last edited by BadlySpelledBuoy; 05-19-2019 at 01:16 AM.

  3. #3
    Registered User
    Join Date
    10-17-2018
    Location
    Plymouth
    MS-Off Ver
    Excel 2021
    Posts
    96

    Re: Trying to build a navigation Route card for Royal Marine Training

    Thank you for the assistance it worked very well.

  4. #4
    Registered User
    Join Date
    04-15-2021
    Location
    London, England
    MS-Off Ver
    XL
    Posts
    1
    Quote Originally Posted by aabbey View Post
    Thank you for the assistance it worked very well.
    Hi, do you have a link to the Excel spreadsheet formula please? This would save me a lot of time as well...

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Trying to build a navigation Route card for Royal Marine Training

    The spreadsheet is in post #1 and the formulae are in post #2.

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Tracking training Sessions and Leader training with Countif and if statements?
    By lreed in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-02-2017, 06:38 PM
  2. Retired Marine from Illinois
    By Retiredguns in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 11-19-2016, 05:59 PM
  3. [SOLVED] Need to Determine Training Room Availability based off a training plan
    By sinspawn56 in forum Excel General
    Replies: 2
    Last Post: 06-08-2015, 03:15 PM
  4. Replies: 1
    Last Post: 07-28-2014, 02:40 AM
  5. Hi from Royal Wootton Basset
    By icalderhead in forum Hello..Introduce yourself
    Replies: 3
    Last Post: 05-19-2013, 02:22 PM
  6. An Italian Marine Biologist .... playing guitar too !!!!
    By pasquale.lanera in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 11-19-2012, 01:23 PM
  7. Replies: 0
    Last Post: 11-02-2011, 08:47 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1