Setting up a spreadsheet to calculate mileage
 

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

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

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

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

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

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.