LinkBack URL
About LinkBacks
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
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
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
>
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 fields>10000,sum of fields * 0.25,sum of fields)
Hope this helps
Regards
Carl
Originally Posted by johndavies
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 Signature:
Signature: 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 Signature:
Signature: 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 Signature:
Signature: 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 Signature:
Signature: 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 Signature:
Signature: 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 Signature:
Signature: 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 Signature:
Signature: 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 Signature:
Signature: 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 Signature:
Signature: 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 Signature:
Signature: 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 Signature:
Signature: 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 Signature:
Signature: 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
>
>
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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks