Another Greater Than Less Than onditional problem.

  • Thread starter Thread starter Scott Dann
  • Start date Start date
S

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?
 
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?
Click to expand...

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.
 
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
 
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
 
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
 
Hi. Same solution as the others...

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

= = = = = = = =
Dana DeLouis
 
Back
Top