Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Formula for Mileage

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Formula for Mileage

    Hello,

    I'm a first timer and would love to have some help with a particular formula. I'm working on an Expense Report in Excel and would like to have a formula in the mileage section so that people can enter the miles they drove and it will automatically calculate. Our current mileage reimbursement rate is 40.5 cents per mile. Does anyone know what formula to use????

    Thank you for any assistance!!!!

    Sylvie

  • #2
    Re: Formula for Mileage

    Welcome to the Forum!

    If the miles are entered in Cell B1, then enter this formula:

    =B1 * 40.5

    into the cell you desire the answer to be in.

    HTH
    Brandtrock

    Brandtrock Consulting | Brandtrock Files | ISU Athletics | Bricktown | VBA Express

    Comment


    • #3
      Re: Formula for Mileage

      Hi HTH,

      Thanks so much for getting back to me. I feel like such an Excel dummy sometimes! It's telling me now that I've created a circular reference? Is that ok? I just want others to have the ability to just enter in a number in the cell and have it automatically calculate. I'm not sure if it's working correctly?

      Your help is much appreciated!

      Thank!
      Sylvie

      Comment


      • #4
        Re: Formula for Mileage

        HTH = Hope This Helps
        My name is Brandtrock.

        The formula above should be entered in C1 for example. Then the user enters a number in B1 and the answer appears in C1. If the formula above is placed in B1 a circular reference does occur.

        To be able to enter a number in a cell and have it change by a factor of .405 will require a macro. If you want to do it that way, take a look at the attachment. It will convert any value entered in Column B to its mileage reimbursement amount. The location can be amended if Column B is not the one you need to use. The code is located in the Selection Change event of the sheet .

        Code:
        Option Explicit
        
        Private Sub Worksheet_Change(ByVal Target As Excel.Range)
            If Target.HasFormula Or IsEmpty(Target) Then Exit Sub
            If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
            If IsNumeric(Target) Then
                Application.EnableEvents = False
                Target.Value = Target.Value * 0.405
                Application.EnableEvents = True
            End If
        End Sub
        HTH
        Attached Files
        Brandtrock

        Brandtrock Consulting | Brandtrock Files | ISU Athletics | Bricktown | VBA Express

        Comment


        • #5
          Re: Formula for Mileage

          HI Brandtrock!

          Thanks for getting back to me yesterday. I'm new to this site so I had no idea that HTH meant what it does! So I read your last post to me and I definitely need to do that macro you sent to me. It's actually not column B it's Column B17 thru H17. I have no idea about macros, do I just click in B17 and Record New Macro? Sorry I'm being so ignorant on this.....

          !
          Sylvie

          Comment


          • #6
            Re: Formula for Mileage

            Here is what you need to do:
            1. Open the workbook you wish to use this in.
            2. Press Alt - F11 (the Alt key and the F11 key at the same time)
            3. Now that the Visual Basic Editor is open select your file name from the top left window
            4. Double click the name of the worksheet you want to use the macro in.
            5. Copy the macro code from this post (see below)
            6. In the big code window on the right side, paste the code.
            7. Return to Excel and save the file.

            Code:
            Option Explicit 
             
            Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
                If Target.HasFormula Or IsEmpty(Target) Then Exit Sub 
                If Intersect(Target, Range("B:H")) Is Nothing Then Exit Sub 
                If IsNumeric(Target) Then 
                    Application.EnableEvents = False 
                    Target.Value = Target.Value * 0.405 
                    Application.EnableEvents = True 
                End If 
            End Sub
            Now you should be able to enter a number of miles into any cell in columns B through H and they will "convert" to the reimburseable amount.

            To limit this only to B17:H17 , add the 17 in this line:

            Code:
               If Intersect(Target, Range("B:H")) Is Nothing Then Exit Sub
            To adjust it to a different range, simply enter the relevant range between the quotation marks in that line.

            To change the mileage reimbursemetn amount, simply change the 0.405 to whatever the appropriate rate is.

            Cheers,
            Brandtrock

            Brandtrock Consulting | Brandtrock Files | ISU Athletics | Bricktown | VBA Express

            Comment


            • #7
              Re: Formula for Mileage

              Hey there!

              I did exactly what you said and it's not calculating for some reason. I limited it to B16:H16 like you said in the correct code. Not sure what I'm doing wrong. I saved it and went back into excel...saved that and still nothing. Any other suggestions? Is there any way I could send you this file and then you could see what I'm doing wrong?

              Thanks again your the best!
              Sylvie

              Comment


              • #8
                Re: Formula for Mileage

                Sure, send it to the e-mail in my profile.

                Click my name and then a drop down appears.
                Brandtrock

                Brandtrock Consulting | Brandtrock Files | ISU Athletics | Bricktown | VBA Express

                Comment


                • #9
                  Re: Formula for Mileage

                  Just so anyone reading along will know, the macro worked fine. Macro security just needed to be set to medium instead of high. Darn security settings.
                  Brandtrock

                  Brandtrock Consulting | Brandtrock Files | ISU Athletics | Bricktown | VBA Express

                  Comment


                  • #10
                    Re: Formula for Mileage

                    Yes, everything worked out perfectly. Thanks so much Brandtrock....you rock!

                    Comment


                    • #11
                      Re: Formula for Mileage

                      Here's a little workbook with a mileage calculator. It also calculates VAT reclaimable in the UK. This can be edited out if required.
                      Attached Files
                      Last edited by royUK; March 4th, 2005, 17:49.
                      Hope that Helps

                      Roy

                      New users should read the Forum Rules before posting

                      For free Excel tools & articles visit my web site

                      If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                      RoyUK's Web Site

                      royUK's Database Form

                      Where to paste code from the Forum

                      About me.

                      Comment

                      Trending

                      Collapse

                      There are no results that meet this criteria.

                      Working...
                      X