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

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 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 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

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