hey :) i'm having problems with my little issue of calculating mileage costs.

Basically I need to work out my mielage costs for a company i'm working for
who ask that i will need to work out 40cents per mile for first 10,000 miles,
then 25 cents thereafter. eventually adding the whole lot up for one lump sum

i'm not sure how to put that in formula form.

I managed the first half of it by multiplying the overall milage by 0.4. but
how do i get it to detect when the mileage has reached 10,000 in order for it
to recalculate at 0.25?

On Fri, 31 Jul 2009 07:18:01 -0700, Scott Dann <Scott
wrote:

hey :) i'm having problems with my little issue of calculating mileage costs.

Basically I need to work out my mielage costs for a company i'm working for
who ask that i will need to work out 40cents per mile for first 10,000 miles,
then 25 cents thereafter. eventually adding the whole lot up for one lump sum

i'm not sure how to put that in formula form.

I managed the first half of it by multiplying the overall milage by 0.4. but
how do i get it to detect when the mileage has reached 10,000 in order for it
to recalculate at 0.25?

With the number of miles in cell A1, try this formula:

=IF(A1<10000,0.4*A1,4000+0.25*(A1-10000))

Hope this helps / Lars-Åke

Hi, tried your formula but it's giving me the #value error, even though there
is a value in the cell i'm using.

"Scott Dann" wrote:

hey :) i'm having problems with my little issue of calculating mileage costs.

Basically I need to work out my mielage costs for a company i'm working for
who ask that i will need to work out 40cents per mile for first 10,000 miles,
then 25 cents thereafter. eventually adding the whole lot up for one lump sum

i'm not sure how to put that in formula form.

I managed the first half of it by multiplying the overall milage by 0.4. but
how do i get it to detect when the mileage has reached 10,000 in order for it
to recalculate at 0.25?

Hi,

Try this. Cell D4 holds 10000. Cell E4:E5 hold 0.40 and 0.25. Cell D8
contains the mileage

=IF(D8<=$D$4,D8*$E$4,($D$4*$E$4)+(D8-$D$4)*$E$5)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Scott Dann" <Scott wrote in message
...
hey :) i'm having problems with my little issue of calculating mileage
costs.

Basically I need to work out my mielage costs for a company i'm working
for
who ask that i will need to work out 40cents per mile for first 10,000
miles,
then 25 cents thereafter. eventually adding the whole lot up for one lump
sum

i'm not sure how to put that in formula form.

I managed the first half of it by multiplying the overall milage by 0.4.
but
how do i get it to detect when the mileage has reached 10,000 in order for
it
to recalculate at 0.25?

same thing again...#value. the mileage cell has a number in it but its a
formula in itself, picking up the cumulative total of another group of cells
on a previous speadsheet page. would that be why. its all numbers in the end
though haha no extra text.

cheers
scott

"Ashish Mathur" wrote:

Hi,

Try this. Cell D4 holds 10000. Cell E4:E5 hold 0.40 and 0.25. Cell D8
contains the mileage

=IF(D8<=$D$4,D8*$E$4,($D$4*$E$4)+(D8-$D$4)*$E$5)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Scott Dann" <Scott wrote in message
...
hey :) i'm having problems with my little issue of calculating mileage
costs.

Basically I need to work out my mielage costs for a company i'm working
for
who ask that i will need to work out 40cents per mile for first 10,000
miles,
then 25 cents thereafter. eventually adding the whole lot up for one lump
sum

i'm not sure how to put that in formula form.

I managed the first half of it by multiplying the overall milage by 0.4.
but
how do i get it to detect when the mileage has reached 10,000 in order for
it
to recalculate at 0.25?

Lars's formula worked for me, and it did the correct calculation. It also
does a good job of showing you how to do these kind of calculations.

-- If you typed it in, don't. Use copy and paste.
-- If you simply accepted it as is, you probably have to change it.
Remember, Lars has no idea where your mileage amount is, so he used A1 as an
example. If your mileage amount is in another cell, you have to modify the
formula. If you don't know how, or are uncomfortable doing this, then
provide the cell address when you ask your question.
-- The only other problem is that your cell doesn't have a number in it (to
Excel at least), it has text. Test this with =isnumber(a1).

Regards,
Fred

"Scott Dann" wrote in message
...
Hi, tried your formula but it's giving me the #value error, even though
there
is a value in the cell i'm using.

"Scott Dann" wrote:

hey :) i'm having problems with my little issue of calculating mileage
costs.

Basically I need to work out my mielage costs for a company i'm working
for
who ask that i will need to work out 40cents per mile for first 10,000
miles,
then 25 cents thereafter. eventually adding the whole lot up for one lump
sum

i'm not sure how to put that in formula form.

I managed the first half of it by multiplying the overall milage by 0.4.
but
how do i get it to detect when the mileage has reached 10,000 in order
for it
to recalculate at 0.25?

That should not be the reason. Please check again

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Scott Dann" wrote in message
...
same thing again...#value. the mileage cell has a number in it but its a
formula in itself, picking up the cumulative total of another group of
cells
on a previous speadsheet page. would that be why. its all numbers in the
end
though haha no extra text.

cheers
scott

"Ashish Mathur" wrote:

Hi,

Try this. Cell D4 holds 10000. Cell E4:E5 hold 0.40 and 0.25. Cell D8
contains the mileage

=IF(D8<=$D$4,D8*$E$4,($D$4*$E$4)+(D8-$D$4)*$E$5)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Scott Dann" <Scott wrote in message
...
hey :) i'm having problems with my little issue of calculating mileage
costs.

Basically I need to work out my mielage costs for a company i'm working
for
who ask that i will need to work out 40cents per mile for first 10,000
miles,
then 25 cents thereafter. eventually adding the whole lot up for one
lump
sum

i'm not sure how to put that in formula form.

I managed the first half of it by multiplying the overall milage by
0.4.
but
how do i get it to detect when the mileage has reached 10,000 in order
for
it
to recalculate at 0.25?

Hi. Same solution as the others...

=Min(A1*0.4,1500 + A1/4)

= = = = = = = =
Dana DeLouis


Scott Dann wrote:
hey :) i'm having problems with my little issue of calculating mileage costs.

Basically I need to work out my mielage costs for a company i'm working for
who ask that i will need to work out 40cents per mile for first 10,000 miles,
then 25 cents thereafter. eventually adding the whole lot up for one lump sum

i'm not sure how to put that in formula form.

I managed the first half of it by multiplying the overall milage by 0.4. but
how do i get it to detect when the mileage has reached 10,000 in order for it
to recalculate at 0.25?