Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
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. ## 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. I agreed to these rules
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?

Thank!
Sylvie

Excel Video Tutorials / Excel Dashboards Reports

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 .

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

5. I agreed to these rules
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. ## 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.

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. I agreed to these rules
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?

Sylvie

Excel Video Tutorials / Excel Dashboards Reports

8. ## Re: Formula for Mileage

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

Click my name and then a drop down appears.

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.

10. I agreed to these rules
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

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

#### 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