Setting up a spreadsheet to calculate mileage

[Bus429]

Hi All,
Need an Excel expert here.
I want to set up a function so that a mileage claim that allows £0.40/mile upto 10000 miles (and does the simple calculation for that) that will automatically calculate the mileage at £0.25/mile for any mileage over 10000. I've tried an IF function based on a summed column of mileage but it doesn't changeover at figures > 10000.
Any pointers? I am using Excel 2007 and have searched the web but found nothing simple (maybe because there is nothing simple!!)

[Rob82]

Try something like

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

Where A1 is the cell with the total amout of miles.

May be an easier way to do it but that seems to work for me.

Rob

[Bus429]

Thanks Rob but it calculates at £0.4/mile after 10000.

[Rob82]

That formula i have given you will take the said amount of miles in A1 and return 40p per mile for the first 10k and 25p per mile afterwards as one figure.

I used the formula i gave you in excel 2007 and below are the results i got.

5000 miles returns £2000 (5000 at 0.4)
9999 miles returns £3999.60 (9999 at 0.4)
10000 miles returns £4000 (10000 at 0.4)
10001 miles returns £4000.25 (10000 at 0.4 and 1 at 0.25)
15000 miles returns £5250 (10000 at 0.4 and 5000 at 0.25)
20000 miles returns £6500 (10000 at 0.4 and 10000 at 0.25)

Is this not what you were after?

Rob

[MetoPower]

In A1 insert the distance, then in B1:
=A1*(IF(A1<=10000,0.4,0)+IF(A1>10000,0.25,0))
Then format cell and select currency.

MP

[Bus429]

Meto - worked - thanks.
Rob - many thanks for your input, too.
My problem is I find it difficult to wade through the mass of information available on the web and elsewhere; time to book my sorry self on a course.