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.
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!!!!
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!
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 .
HTHCode: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
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.....
Here is what you need to do:
- Open the workbook you wish to use this in.
- Press Alt - F11 (the Alt key and the F11 key at the same time)
- Now that the Visual Basic Editor is open select your file name from the top left window
- Double click the name of the worksheet you want to use the macro in.
- Copy the macro code from this post (see below)
- In the big code window on the right side, paste the code.
- Return to Excel and save the file.
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.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
To limit this only to B17:H17 , add the 17 in this line:
To adjust it to a different range, simply enter the relevant range between the quotation marks in that line.Code:If Intersect(Target, Range("B:H")) Is Nothing Then Exit Sub
To change the mileage reimbursemetn amount, simply change the 0.405 to whatever the appropriate rate is.
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)