Another Greater Than Less Than onditional problem.

[Scott Dann]

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-Åke Aspelin]

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

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

 

[Scott Dann]

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

 

[Ashish Mathur]

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]

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

 

[Fred Smith]

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

 

[Ashish Mathur]

That should not be the reason. Please check again

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Dana DeLouis]

Hi. Same solution as the others...

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

= = = = = = = =
Dana DeLouis