I have a spread sheet I was given to use for mileage reimbursement. They

have one colum where you enter the miles you've traveled, say 230 miles and

the formula is =IF(SUM(H18:H43)>0,SUM(H18:H43),"") the next column is

supposed to total the miles and I believe multiply by .36 per mile but the

formula is broke. What they have is: IF(SUM(I18:I43)*0.36)

can anyone help me figure out this formula?

Thanks,

Cinder

have one colum where you enter the miles you've traveled, say 230 miles and

the formula is =IF(SUM(H18:H43)>0,SUM(H18:H43),"") the next column is

supposed to total the miles and I believe multiply by .36 per mile but the

formula is broke. What they have is: IF(SUM(I18:I43)*0.36)

can anyone help me figure out this formula?

Thanks,

Cinder

- Mileage reimbursement formula
- Formula for Mileage
- How do I sum rounded values from two seperate formulas in Excel?
- Mileage Claim Formula
- Mileage Claim Formula
- 'IF' formula HELP!!!
- Lotus email attachment doesn't show Excel formula
- Conditional Lookups
- Help Creating Mileage Calculation Formula with Blank Cells/Multiple Lines?
- How can I connect a word of text to a certain dollar value?
- Add a drop down calander to excel sheet
- Lookup, Find and Replace macro needed
- Formula for Calculating Cumulative Mileage
- Formula for Mileage from Latitude and Longitude
- Fill Cell Contents On Multiple Conditions (Formula or VBA)
- Using a formula to calcualte different mileage rates
- Formula for mileage log?
- IF, OR, CHOOSE, TRUNC Help with formula please
- Mileage/Hr log and date formula
- Formula designed to reimburse employees up to a certain amt?
- Excel Formula - IF/AND statement
- Calculating cumulative mileage
- Formula for Weekly Mileage and Cumulative Total
- Having Trouble with Formula

have one colum where you enter the miles you've traveled, say 230 miles and

the formula is =IF(SUM(H18:H43)>0,SUM(H18:H43),"") the next column is

supposed to total the miles and I believe multiply by .36 per mile but the

formula is broke. What they have is: IF(SUM(I18:I43)*0.36)

can anyone help me figure out this formula?

Thanks,

Cinder

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

seperate formulas to calculate personal services and mileage reimbursement.

The two areas are paid with different checks so they need to be seperate.

However, in order to balance there must also be a calculated total. I

formated the cells so 'currency' is used and two decimal points are

displayed. But, when the two cells are summed, they are summed on actual

values and not the displayed rounded values. The problem is obvious . . . you

cannot pay someone $57.125 so it needs to be rounded to a paid amount of

$57.13 . . . but how do I get Excel to sum on $57.13 and not $57.125?

Thanks for any help or suggestions.

Having a spot of bother here with a formula, what I need if any one can

help is the following

Sum of a number of fields and IF that SUM is > than 10000 then * by

0.25

This a mileage workout and it's pulling my hair out.

Thanks in Advance

John

--

johndavies

------------------------------------------------------------------------

johndavies's Profile: http://www.excelforum.com/member.php...o&userid=37383

View this thread: http://www.excelforum.com/showthread...hreadid=570693

Having a spot of bother here with a formula, what I need if any one can help is the following

Sum of a number of fields and IF that SUM is > than 10000 then * by 0.25

This a mileage workout and it's pulling my hair out.

Thanks in Advance

John

It is currently set up where entries and 'AMOUNT' in Column F self populate and match the "TOTALS' in Column K. and then subsequently to the next page and consolidated according to code(s). I would like to now add the above option ONLY IF someone enters a "NUMBER OF MILES" in Column F and selects '7501' from the dropdown in I without disrupting all other formulas in place.

I am in WAY OVER MY HEAD!!!!

For example, if I put '210' in the AMOUNT Column of F, I want Column K to show "$106.50".

Is this even possible?!? PLEASE HELP!!!!

CCL Expense Report Trial.xlsx

If for example D2 is empty (column C has previous mileage, column D has current mileage), the formula would cause "ERROR, mileage not reported" to be displayed in E2. The problem is...only the message is what shows up in the email recipient's attachment (column E) and although it is still an accurate message for the recipient to see, I want the formula to also be there because the recipient will be asked to enter their mileage into the spreadsheet (and return to us) and the formula is supposed to alert them with the messages if the mileage they enter is wrong in some way.

Here is the section of my code that does the sending. Was wondering if my problem had to do with the CreateRichTextItem of > Set obAttachment = noDocument.CreateRichTextItem("stAttachment"). I'm a novice at vba code..so any help/direction would be very much appreciated.Public Sub SendEmail(ByVal pEmail As String, ByVal pEFN As String, ByVal pNotifType As String, pNotification As String, ByVal pPOC As String) 'Inherits System.Windows.Forms.RichTextBox Dim noSession As Object, noDatabase As Object, noDocument As Object Dim obAttachment As Object, EmbedObject As Object Dim stSubject As Variant, stAttachment As String Dim vaRecipient As Variant, vaMsg As Variant Dim Password As String Dim CurDate CurDate = Date Dim LDate As String LDate = (MonthName(DatePart("m", CurDate))) & ", " & DatePart("yyyy", CurDate) Const EMBED_ATTACHMENT As Long = 1454 Const stTitle As String = "Active workbook status"x Const stMsg As String = "The active workbook must first be saved " & vbCrLf _ & "before it can be sent as an attachment." Do vaRecipient = pEmail Loop While vaRecipient = "" 'If the user has canceled the operation. If vaRecipient = False Then Exit Sub If pNotification = "Final mileage due to Govt" Then vaMsg = "Removed for the message board " & LDate & vbCrLf & vbCrLf ElseIf pNotification = "Gas cutoff notification" Then vaMsg = "Removed for the message board" & vbCrLf & vbCrLf ElseIf pNotification = "Third notice" And pNotifType <> "PM service due notification" Then vaMsg = "Removed for the message board, " & vbCrLf & vbCrLf _ Else 'First and second notices (third notice is above) Do vaMsg = "Removed for the message board, " & vbCrLf & vbCrLf _ Loop While vaMsg = "" End If 'If the user has canceled the operation. If vaMsg = False Then Exit Sub Do stSubject = pEFN Loop While stSubject = "" stAttachment = ActiveWorkbook.FullName 'Instantiate the Lotus Notes COM's Objects. Set noSession = CreateObject("Notes.NotesSession") Set noDatabase = noSession.GETDATABASE("", "") 'If Lotus Notes is not open then open the mail-part of it. If noDatabase.IsOpen = False Then noDatabase.OPENMAIL 'Create the e-mail and the attachment. Set noDocument = noDatabase.CreateDocument Set obAttachment = noDocument.CreateRichTextItem("stAttachment") Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment) 'Add values to the created e-mail main properties. With noDocument .Form = "Memo" .SendTo = vaRecipient .Subject = stSubject .Body = vaMsg .SaveMessageOnSend = True End With 'Send the e-mail. With noDocument .PostedDate = Now() On Error GoTo Err_Handle .Send 0, vaRecipient 'Release objects from the memory. Set EmbedObject = Nothing Set obAttachment = Nothing Set noDocument = Nothing Set noDatabase = Nothing Set noSession = Nothing Exit Sub Err_Handle: If vaRecipient = " " Then MsgBox pPOC & " has no email address in column L of your Master spreadsheet" Else MsgBox " Either the Group is setup wrong in this program for " & pPOC & " or something else is going on. Alert Catherine" End If End With 'Release objects from the memory. Set EmbedObject = Nothing Set obAttachment = Nothing Set noDocument = Nothing Set noDatabase = Nothing Set noSession = Nothing End Sub

High level view, I'm trying to populate columns BL, BM, and BN ("Division", "Purchase/Contract Description", "Vendor/Employee Name"). There's another workbook ("Lookup.xls") with one sheet ("Sheet1") and three columns; "Lookup#", "Div", and "Name", that I use to pull information from.

Here's the part I'm stuck at

In the LBB_ACCT column, if the acct is "L2009", if the "Descr" field entry begins with T1001, or says "Travel", "Travel Exp", "Travel Expense", or says "Reimbursement", "Volunteer Mileage Reimbursement", "Volunteer Reimbursement", or "Volunteer Travel Exp" and the Ln Vendor ID begins with a "2", these all relate to travel. For all of these I'd like to use the last 9 numbers of the Ln Vendor ID to lookup the Div and Name from my Lookup sheet, and put "Travel Expense" in the "Purchase/Contract Description" field. That's the first part.

Now, some of the lookup numbers may not be on my list of numbers. In that case I'd like to pull the name from the LN Vendor Name column, then scan up and down column BN for the same name.' If it's found, pull the Div from that one and paste to the line I'm working on, put "Travel Expense" in column BM. If it's not found, paste the name from the LN Vendor Name column into column BN, put "Travel Expense" in column BM, and put "#N/A" in column BL.

Whew! I know it's a lot to ask. I start thinking about ways to do it all and my mind goes numb, so any help you can provide is greatly appreciated.

Thanks,

John

I am trying to create a formula that will calculate mielage totals for our drivers' daily trips. I have attached a spreadsheet for your review. The fields I need totals in are highlighted in yellow.

I ultimately need total miles for each job (line), total WI miles for that day, total IL miles for that day, and total miles for that truck (tab). This is easily doable if our drivers sequentially fill in the cells and leave no blanks, however, that is rarely the case as they go to muliple jobs in multiple states. I have set up two examples, one in green and one in blue.

We need to tack all of this info for job costing and government purposes. Thank you for any help you can provide. We are on 2003.

working at certain jobs. I want to know how I can enter the name of the job

and have the dollar amount of the reimbursement show up in the appropriate

column. Thanks!

I need to format a sheet with several hundred employees, be able to send

this sheet to a supervisor, have him record wages to be paid by date and

mileage reimbursement, with a month total due employee. Have hime send it

back to me and be able to print a sheet for each employee with the totals

listed by date that tehn becomes a voucher for payment.

Thanks

What I'm trying to do is populate columns BL, BM, and BN ("Division", "Purchase/Contract Description", "Vendor/Employee Name") in the "Example" workbook. The other workbook ("Lookup.xls") has three columns; "Lookup#", "Div", and "Name", that I use to pull information from. From that, and from other sources, I fill in the data in Columns BL, BM, and BN. What I can't find results in an #N/A.

Once I'm done, there's cleanup that needs to done for my Travel expenses in L2009 Travel. That's where I'm stuck. There are two tasks I need to accomplish.

The First is: In the LBB_ACCT column, if the acct is "L2009", if the "Descr" field entry begins with "T1001", or says "Travel", "Travel Exp", or "Travel Expense", or says "Reimbursement", "Volunteer Mileage Reimbursement", "Volunteer Reimbursement", or "Volunteer Travel Exp" and the Ln Vendor ID begins with a "2", then the lines are all travel related. For all of these lines I'd like to use the last 9 numbers of the Ln Vendor ID to lookup the Div and Name from my Lookup sheet, and put "Travel Expense" in the "Purchase/Contract Description" field.

The Second is: Some (most) of the lookup numbers for L2009 may not be on my list of numbers. In that case I'd like to pull the name from the LN Vendor Name column, then scan up and down the LN Vendor Name column for the same name. If it's found, pull the Div from column BL for that one and paste to the line I'm working on, put "Travel Expense" in column BM. If it's not found, paste the name from the LN Vendor Name column into column BN, put "Travel Expense" in column BM, and put "#N/A" in column BL.

Whew! I know it's a lot to ask. I start thinking about ways to do it all and my mind goes numb, so any help you can provide is greatly appreciated.

Thanks,

John

I have a spreadsheet that tracks mileage for our fleet. Three times a week (mon, wed, & friday), we get odometer readings from our drivers. The thing is sometimes one or more drivers is off or on vacation, so when there's a blank cell, it throws off the formula, so it has to be manually calculated.

When it works it looks something like this:

Joe Schmo 45888 45891 45905 WEEKLY MILES: 14

[=(d1-c1)+(c1-b1)]

But, if there's a missing number, it looks like this:

Jill Schmo 67900 68566 0 WEEKLY MILES: -67900

Is there a formula that has an AND/OR operator or deals with blanks that I could use instead?

Any help would be much appreciated!

I am looking for an Excel formula I can use to determine mileage between 2

zip codes based on their Latitude and Longitude. I have found several but

have been unable to get them to work in Excel. The following is an example

of what I have to work with.

Zip Latitude Longitude

23219 37.541696 -77.439204 Richmond VA

20315 38.928861 -77.017948 WASHINGTON DC

Any and all help/assistance will be greatly appreciated.

Thanks

One of these items is a travel requirements worksheet. Essentially it's a table of people with relevant information. I have a number of calculations already to calculate various cost estimates. What I need now is essentially to fill the contents of one cell to specify which reimbursement form to complete based off city of residence AND distance. I thought I had a good idea of how to proceed with formulas, but the longer I tried to do the formula the more confused I've gotten.

"Table2" contains the data we're working with (this table may expand or shrink depending on personnel assignments). Multiple columns, but the only columns I'm focused on for this are:

'N' - Where I need the data to fill to (text specific based on other columns); [Header:VOUCHER_VER]

'J' - The city they live in; [Header:ADDRESS_CITY]

'M' - The distance (miles) from their home to permanent work site ("SAN ANTONIO"). [Header:DISTANCE_WS]

What I'm trying to do is get the following result for each individual (which will update if they change address info):

* If greater than or equal to 50 miles ('M'), regardless of city ('J'), then fill, "Zone 2 (Complete Voucher)" in 'N'.

* If less than 50 miles ('M') AND city ('J') is "SAN ANTONIO", then fill, "Zone 1A (Local, No voucher)" in 'N'.

* If less than 50 miles ('M') AND city ('J') is NOT "SAN ANTONIO", then fill, "Zone 1B (Mileage Only, No Voucher)" in 'N'.

Does that make sense? I do not care if it is a formula or requires VBA (I need to expand my horizons, anyway, and VBA might help reduce the chance of them overwriting a formula typed directly in the cell). I also intend to apply conditional (fill) formatting to each row based on which of the three options are filled in 'N' to help them better identify a person's status at-a-glance.

I'm certain this is possible. I'm just apparantly not quite up to the task all by myself.

I have attached a file in which i need some help using a formula to calculate a milage rate once it hits a certain level. So in my sheet the limit for mileage is 10,000 at which these can be claimed at 0.45p and anything thereafter at 0.25p. The opening miles is 9800 which means there are 200 miles left at 0.45p and this is reached on the 4th when 30 miles are done in the day - so i need to calculate 20 @0.45p and10 @0.25p. But i need a formula which will say anything which is under 10,000 charge at 0.45p and anything after that threshold at 0.25p.

I hope this makes sense -this is my first post!

Regards

Andrew.Mileage Rate.xlsx

I'm using Excel to record auto mileage.

I have it set up well enough except for one thing.

Please refer to the screenshot.

Mileage Log example.jpg

Columns A – D are self-explanatory

Column E is the total miles for the day

Column F describes gen'l info for the day.

Cell G 2 is total business miles

Cell H 2 (highlighted in screenshot) is total “rec” or recreation miles

Notice the formula for H 2 –> =SUMIF(F:F,”rec”,E:E)

This means that if I type rec in column F, as in F 3, the miles are considered recreation miles, and therefore are recorded in cell H 3. Conversely, if rec is not typed in, (as in cell F 4), then the resulting mileage goes to cell G 3. So far so good.

The problem is, if I want to type in rec and additional info, eg “rec, went to store”, then the mileage shifts to column G instead of H. ie, The rec miles become business miles.

My question is, how do I amend the formula so it will record “rec” miles, but I can also write other info in the same cell in Column F.

Hope this is clear. Thanks in advance for your help.

M

on mileage turned in for reimbursement. Our payroll takes the mileage turned

in and looks at the $ amount of production that was turned in by that

individual. If the mileage turned in is greater than what was allowed for

that particular dollar amount of production we lessen the mileage or "cap it"

based on the table below. If the mileage turned in is less than or equal to

what was allowed we do not adjust it. The mileage is pulled off a daily

sheet and talleyed on line $E$2 of the weekly production sheet for each

individual. Every individual has a weekly production sheet in a workbook

where line E2 is mileage and line G47 is production. The process is similar

in all of our locations although the mileage cap varies from site to site.

The following represents one paticular location whereas we may allow 150

miles for the same amount of production in another system. in other systems

we may allow the same amount of miles but base it on less or more production.

My initial thought was to use an Index and Match formula but I couldn't make

it work. When I tried this one it worked except for the ones where the

mileage was less than the cap. This one gives them miles that they didn't

turn in. Unless I'm not understanding and putting the wrong values in it. The

formula written by Mr. Ogilvy was:

=if(or(e3="",e3=0),"",CHOOSE(TRUNC(E3/200)+1,100,200,300,400,500)) The

formula as I tried it

was:=IF(OR('9508'!$E$2="",'9508'!$E$2=0),"",CHOOSE(TRUNC('9508'!$E$2/200)+1,100,200,300,400,500))

Thanks in advance.

For Periods: 10/5/2007 - 10/12/2007

Total Mileage: 0

Total Hours: 13

Total Reimbursement: $91.00

Another question: Is it possible, for when I enter an Invoice number, say #1, it will grab the first two weeks for the For Period cell, and do the rest in my first question. Enter #2 grab the second two weeks, enter 3 grab the third two weeks, etc...

Here's a screenshot and I also uploaded it.

Thanks in advance!

http://img99.imageshack.us/img99/291/61150519rv7.png

need to find out how to set it up to pay up to a certain amount. For

example, if somene turns in a claim for $550, and we only pay $400, how do I

set up that formula?

Thanks!

I have a column (COLUMN E) which list either of 2 insurance payors. I have another column (COLUMN F) which lists the sum of charges.

COMPANY A - will pay 70% of the charges (in Column F) if they exceed $150K... if NOT they pay a base reimbursement rate (which is listed in column K).

COMPANY B - will pay 65% of the charges (in Column F) if they exceed $70K... if NOT they pay a base reimbursement rate (which is listed in column K).

so my statement should be:

IF COLUMN A="Company A" and COLUMN F>$150K, then 70%*Total Charge (column F)...IF NOT-just give me COLUMN K (base reimbursement amount)....

OR

IF COLUMN A="Company B" and COLUMN F>$70K, then 65%*Total Charge (column F)...IF NOT-just give me COLUMN K (base reimbursement amount)

It looks so easy but I can't seem to figure it out. Any help would be appreciated.

Thx,

Tommy

My last hurdle is this:

I have a list of Tax Exempt Customers that when thier name is entered into the form, I would like it to not add tax.

I have tried a forumula like this:

Code:

The above formula does not work, but I dont know why. I want it to lookup the city tax and add it in if the customer is not on the tax exempt list.

Ideas?