Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 4
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 152
Default 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


  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 47
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula for Mileage from Latitude and Longitude Adams135 Excel Discussion (Misc queries) 3 September 11th 07 04:00 PM
Locking portions of a formula tiggatattoo Excel Worksheet Functions 2 June 5th 06 04:51 PM
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
adding row to forumla carrera Excel Discussion (Misc queries) 9 August 23rd 05 10:24 PM
Creating a check mark box MarthaSue Setting up and Configuration of Excel 18 April 28th 05 12:31 AM


All times are GMT +1. The time now is 12:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"