|
Mileage Claim Formula
Hi, 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 |
Mileage Claim Formula
Hope I understood your problem.
Let you SUM from cells A1 to A5 and you want to multipy it by 0.25 if it is greater than 10000, then =IF(SUM(A1:A5)10000,SUM(A1:A5)*0.25,0)) This would be OK.Remember to substitute A1:A5 with your data range If your problem is solved,spend time to look at my blog http://xlmaster.blogspot.com johndavies wrote: Hi, 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 |
Mileage Claim Formula
Hi, Without knowing the cell references I would guess that the formual you are after would be something along the lines of =if(sum of fields10000,sum of fields * 0.25,sum of fields) Hope this helps Regards Carl johndavies Wrote: Sum of a number of fields and IF that SUM is than 10000 then * by 0.25 -- mr_teacher ------------------------------------------------------------------------ mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352 View this thread: http://www.excelforum.com/showthread...hreadid=570693 |
Mileage Claim Formula
MILEAGE REPORT EXPENSE REPORT PERSONAL CREDIT CARD /
CASH EXPENSE REPORT COMPANY CREDIT CARD & AUTOMOBILE EXPENSES Name: Period Ending: Name: Period Ending: Name: Period Ending: Date Beginning Odometer Ending Odometer Miles Destination Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total ENTERTAINMENT DETAIL Total Expense Report Expenses ENTERTAINMENT DETAIL Total Expenditures Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Total Automobile Expenses Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Personal Expenditures (Subtract from Entry Above) Total Expenses (Sum of the two Entries Above) Total Company Expense Cash Advances (Subtract from Entry Above) Total Amount to be Reimbursed Signatu Signatu Date: Date: Approved: Page 1 0 $0.00 12/03/05 Page 2 12/03/05 Approved: Page 3 12/03/05 Total Chargeable Miles Driven times per Mile = Total Automobile Expense MILEAGE REPORT EXPENSE REPORT PERSONAL CREDIT CARD / CASH EXPENSE REPORT COMPANY CREDIT CARD & AUTOMOBILE EXPENSES Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Date Beginning Odometer Ending Odometer Miles Destination Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total ENTERTAINMENT DETAIL Total Expense Report Expenses $0.00 ENTERTAINMENT DETAIL Total Expenditures $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Total Automobile Expenses $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Personal Expenditures (Subtract from Entry Above) Total Expenses (Sum of the two Entries Above) $0.00 Total Company Expense $0.00 Cash Advances (Subtract from Entry Above) Total Amount to be Reimbursed $0.00 Signatu Signatu Date: Date: Approved: Page 4 0 $0.00 12/03/05 Page 5 12/03/05 Approved: Page 6 12/03/05 Total Chargeable Miles Driven 0 times $0.000 per Mile = $0.00 Total Automobile Expense MILEAGE REPORT EXPENSE REPORT PERSONAL CREDIT CARD / CASH EXPENSE REPORT COMPANY CREDIT CARD & AUTOMOBILE EXPENSES Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Date Beginning Odometer Ending Odometer Miles Destination Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total ENTERTAINMENT DETAIL Total Expense Report Expenses $0.00 ENTERTAINMENT DETAIL Total Expenditures $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Total Automobile Expenses $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Personal Expenditures (Subtract from Entry Above) Total Expenses (Sum of the two Entries Above) $0.00 Total Company Expense $0.00 Cash Advances (Subtract from Entry Above) Total Amount to be Reimbursed $0.00 Signatu Signatu Date: Date: Approved: Page 7 0 $0.00 12/03/05 Page 8 12/03/05 Approved: Page 9 12/03/05 Total Chargeable Miles Driven 0 times $0.000 per Mile = $0.00 Total Automobile Expense MILEAGE REPORT EXPENSE REPORT PERSONAL CREDIT CARD / CASH EXPENSE REPORT COMPANY CREDIT CARD & AUTOMOBILE EXPENSES Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Date Beginning Odometer Ending Odometer Miles Destination Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total ENTERTAINMENT DETAIL Total Expense Report Expenses $0.00 ENTERTAINMENT DETAIL Total Expenditures $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Total Automobile Expenses $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Personal Expenditures (Subtract from Entry Above) Total Expenses (Sum of the two Entries Above) $0.00 Total Company Expense $0.00 Cash Advances (Subtract from Entry Above) Total Amount to be Reimbursed $0.00 Signatu Signatu Date: Date: Approved: Page 10 0 $0.00 12/03/05 Page 11 12/03/05 Approved: Page 12 12/03/05 Total Chargeable Miles Driven 0 times $0.000 per Mile = $0.00 Total Automobile Expense MILEAGE REPORT EXPENSE REPORT PERSONAL CREDIT CARD / CASH EXPENSE REPORT COMPANY CREDIT CARD & AUTOMOBILE EXPENSES Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Date Beginning Odometer Ending Odometer Miles Destination Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total ENTERTAINMENT DETAIL Total Expense Report Expenses $0.00 ENTERTAINMENT DETAIL Total Expenditures $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Total Automobile Expenses $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Personal Expenditures (Subtract from Entry Above) Total Expenses (Sum of the two Entries Above) $0.00 Total Company Expense $0.00 Cash Advances (Subtract from Entry Above) Total Amount to be Reimbursed $0.00 Signatu Signatu Date: Date: Approved: Page 13 0 $0.00 12/03/05 Page 14 12/03/05 Approved: Page 15 12/03/05 Total Chargeable Miles Driven 0 times $0.000 per Mile = $0.00 Total Automobile Expense MILEAGE REPORT EXPENSE REPORT PERSONAL CREDIT CARD / CASH EXPENSE REPORT COMPANY CREDIT CARD & AUTOMOBILE EXPENSES Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Date Beginning Odometer Ending Odometer Miles Destination Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total ENTERTAINMENT DETAIL Total Expense Report Expenses $0.00 ENTERTAINMENT DETAIL Total Expenditures $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Total Automobile Expenses $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Personal Expenditures (Subtract from Entry Above) Total Expenses (Sum of the two Entries Above) $0.00 Total Company Expense $0.00 Cash Advances (Subtract from Entry Above) Total Amount to be Reimbursed $0.00 Signatu Signatu Date: Date: Approved: Page 16 0 $0.00 12/03/05 Page 17 12/03/05 Approved: Page 18 12/03/05 Total Chargeable Miles Driven 0 times $0.000 per Mile = $0.00 Total Automobile Expense MILEAGE REPORT EXPENSE REPORT PERSONAL CREDIT CARD / CASH EXPENSE REPORT COMPANY CREDIT CARD & AUTOMOBILE EXPENSES Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Date Beginning Odometer Ending Odometer Miles Destination Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total ENTERTAINMENT DETAIL Total Expense Report Expenses $0.00 ENTERTAINMENT DETAIL Total Expenditures $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Total Automobile Expenses $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Personal Expenditures (Subtract from Entry Above) Total Expenses (Sum of the two Entries Above) $0.00 Total Company Expense $0.00 Cash Advances (Subtract from Entry Above) Total Amount to be Reimbursed $0.00 Signatu Signatu Date: Date: Approved: Page 19 0 $0.00 12/03/05 Page 20 12/03/05 Approved: Page 21 12/03/05 Total Chargeable Miles Driven 0 times $0.000 per Mile = $0.00 Total Automobile Expense MILEAGE REPORT EXPENSE REPORT PERSONAL CREDIT CARD / CASH EXPENSE REPORT COMPANY CREDIT CARD & AUTOMOBILE EXPENSES Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Date Beginning Odometer Ending Odometer Miles Destination Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total ENTERTAINMENT DETAIL Total Expense Report Expenses $0.00 ENTERTAINMENT DETAIL Total Expenditures $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Total Automobile Expenses $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Personal Expenditures (Subtract from Entry Above) Total Expenses (Sum of the two Entries Above) $0.00 Total Company Expense $0.00 Cash Advances (Subtract from Entry Above) Total Amount to be Reimbursed $0.00 Signatu Signatu Date: Date: Approved: Page 22 0 $0.00 12/03/05 Page 23 12/03/05 Approved: Page 24 12/03/05 Total Chargeable Miles Driven 0 times $0.000 per Mile = $0.00 Total Automobile Expense MILEAGE REPORT EXPENSE REPORT PERSONAL CREDIT CARD / CASH EXPENSE REPORT COMPANY CREDIT CARD & AUTOMOBILE EXPENSES Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Date Beginning Odometer Ending Odometer Miles Destination Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total ENTERTAINMENT DETAIL Total Expense Report Expenses $0.00 ENTERTAINMENT DETAIL Total Expenditures $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Total Automobile Expenses $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Personal Expenditures (Subtract from Entry Above) Total Expenses (Sum of the two Entries Above) $0.00 Total Company Expense $0.00 Cash Advances (Subtract from Entry Above) Total Amount to be Reimbursed $0.00 Signatu Signatu Date: Date: Approved: Page 25 0 $0.00 12/03/05 Page 26 12/03/05 Approved: Page 27 12/03/05 Total Chargeable Miles Driven 0 times $0.000 per Mile = $0.00 Total Automobile Expense MILEAGE REPORT EXPENSE REPORT PERSONAL CREDIT CARD / CASH EXPENSE REPORT COMPANY CREDIT CARD & AUTOMOBILE EXPENSES Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Date Beginning Odometer Ending Odometer Miles Destination Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total ENTERTAINMENT DETAIL Total Expense Report Expenses $0.00 ENTERTAINMENT DETAIL Total Expenditures $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Total Automobile Expenses $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Personal Expenditures (Subtract from Entry Above) Total Expenses (Sum of the two Entries Above) $0.00 Total Company Expense $0.00 Cash Advances (Subtract from Entry Above) Total Amount to be Reimbursed $0.00 Signatu Signatu Date: Date: Approved: Page 28 0 $0.00 12/03/05 Page 29 12/03/05 Approved: Page 30 12/03/05 Total Chargeable Miles Driven 0 times $0.000 per Mile = $0.00 Total Automobile Expense MILEAGE REPORT EXPENSE REPORT PERSONAL CREDIT CARD / CASH EXPENSE REPORT COMPANY CREDIT CARD & AUTOMOBILE EXPENSES Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Date Beginning Odometer Ending Odometer Miles Destination Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total ENTERTAINMENT DETAIL Total Expense Report Expenses $0.00 ENTERTAINMENT DETAIL Total Expenditures $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Total Automobile Expenses $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Personal Expenditures (Subtract from Entry Above) Total Expenses (Sum of the two Entries Above) $0.00 Total Company Expense $0.00 Cash Advances (Subtract from Entry Above) Total Amount to be Reimbursed $0.00 Signatu Signatu Date: Date: Approved: Page 31 0 $0.00 12/03/05 Page 32 12/03/05 Approved: Page 33 12/03/05 Total Chargeable Miles Driven 0 times $0.000 per Mile = $0.00 Total Automobile Expense MILEAGE REPORT EXPENSE REPORT PERSONAL CREDIT CARD / CASH EXPENSE REPORT COMPANY CREDIT CARD & AUTOMOBILE EXPENSES Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Name: 0 Period Ending: #VALUE! Date Beginning Odometer Ending Odometer Miles Destination Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total Date Meals Lodging Transport Car Rental Telephone Entertainment (Detail) Miscellaneous Expenses (Explain - Shop Supplies, Hand Tools, etc.) Total ENTERTAINMENT DETAIL Total Expense Report Expenses $0.00 ENTERTAINMENT DETAIL Total Expenditures $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Total Automobile Expenses $0.00 Date Type Meal Name of Person and Co. / Entertainment Location & Purpose Cost Personal Expenditures (Subtract from Entry Above) Total Expenses (Sum of the two Entries Above) $0.00 Total Company Expense $0.00 Cash Advances (Subtract from Entry Above) Total Amount to be Reimbursed $0.00 Signatu Signatu Date: Date: Approved: Page 34 0 $0.00 12/03/05 Page 35 12/03/05 Approved: Page 36 12/03/05 Total Chargeable Miles Driven 0 times $0.000 per Mile = $0.00 Total Automobile Expense Total Miles Driven to Date 0 times $0.275 per Mile = $0.00 Total Automobile Reimbursement Total Miles Driven to Date 0 times $0.375 per Mile = $0.00 Total IRS Automobile Deduction $0.00 "johndavies" wrote: Hi, 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 |
Mileage Claim Formula
The numbers that you have selected in the OP suggests to me that you may be
trying to do an annual mileage claim based on the UK AMAP rates. Only the excess over 10000 should be at 25 pence per mile, with the first 10000 at 40 pence. If my guess is wrong then ignore this post. Otherwise you might try something like: =sum(range)*0.25+min(sum(range),10000)*.15 "johndavies" wrote in message ... Hi, 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 |
| All times are GMT +1. The time now is 11:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com