Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Formula for Mileage

  1. #1
    Join Date
    3rd March 2005
    Posts
    5

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    20th May 2003
    Location
    Mesa, Az
    Posts
    1,110

    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

  3. #3
    Join Date
    3rd March 2005
    Posts
    5

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    20th May 2003
    Location
    Mesa, Az
    Posts
    1,110

    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 .

    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  5. #5
    Join Date
    3rd March 2005
    Posts
    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    20th May 2003
    Location
    Mesa, Az
    Posts
    1,110

    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.

    VB:
    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:

    VB:
    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,

  7. #7
    Join Date
    3rd March 2005
    Posts
    5

    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    20th May 2003
    Location
    Mesa, Az
    Posts
    1,110

    Re: Formula for Mileage

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

    Click my name and then a drop down appears.

  9. #9
    Join Date
    20th May 2003
    Location
    Mesa, Az
    Posts
    1,110

    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.

  10. #10
    Join Date
    3rd March 2005
    Posts
    5

    Re: Formula for Mileage

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Calculate Mileage For Discrete Date Ranges
    By robjv1 in forum EXCEL HELP
    Replies: 5
    Last Post: August 14th, 2008, 08:04
  2. Calculate Mileage
    By rsikin in forum EXCEL HELP
    Replies: 2
    Last Post: October 1st, 2005, 23:32
  3. calculating cumulative mileage
    By hunteryik in forum EXCEL HELP
    Replies: 7
    Last Post: February 11th, 2005, 14:33

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
  •  
porno