Setting up a spreadsheet to calculate mileage
Pilots' Pal
Thread Starter
Join Date: Nov 1998
Location: USA
Age: 63
Posts: 1,158
Likes: 0
Received 0 Likes
on
0 Posts
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!!)
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!!)
Join Date: Nov 2005
Location: UK
Age: 41
Posts: 56
Likes: 0
Received 0 Likes
on
0 Posts
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
=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
Join Date: Nov 2005
Location: UK
Age: 41
Posts: 56
Likes: 0
Received 0 Likes
on
0 Posts
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
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
Last edited by Rob82; 1st Jan 2010 at 20:06.
Join Date: Aug 2007
Location: The World
Posts: 55
Likes: 0
Received 0 Likes
on
0 Posts
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
=A1*(IF(A1<=10000,0.4,0)+IF(A1>10000,0.25,0))
Then format cell and select currency.
MP
Last edited by MetoPower; 2nd Jan 2010 at 07:00.
Pilots' Pal
Thread Starter
Join Date: Nov 1998
Location: USA
Age: 63
Posts: 1,158
Likes: 0
Received 0 Likes
on
0 Posts
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.
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.
