# calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate

1. ## calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate

Id like to calculate TOTAL COST based on user entering mileage into one cell:

First 2 miles of journey are charged at £3.50, additional miles thereafter ( on the same journey) are charged at £1.50. Any ideas?   Register To Reply

2. ## Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate

Hello & Welcome to the Forum,

Maybe just...

=(2*3.5)+((A1-2)*1.5)  Register To Reply

3. ## Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate

i think wrap it with below considering 1 or 2 km as well

=IF(OR(A1=1,A1=2),A1*3.5,(2*3.5)+((A1-2)*1.5))  Register To Reply

4. ## Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate Originally Posted by jeffreybrown Hello & Welcome to the Forum,

Maybe just...

=(2*3.5)+((A1-2)*1.5)

in that event 1 (c4) mile is calculated at £5.50 =(2*3.5)+((C4-2)*1.5)
in that event 2 (c5) miles is calculated at £7.00 =(2*3.5)+((C5-2)*1.5)
and 3 miles = £8.50

which is incorrect  Register To Reply

5. ## Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate

So we get all the requirements correct the first time...

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic  Register To Reply

6. ## Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate Originally Posted by amy_d2 i think wrap it with below considering 1 or 2 km as well

=IF(OR(A1=1,A1=2),A1*3.5,(2*3.5)+((A1-2)*1.5))
That's solved it, Thank you!!  Register To Reply

7. ## Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate

Thanks for asking jeff, think amy_d2 has got it, except for one niggle, when cell is at 0 fr mileage the cost is being calculated at  Register To Reply

8. ## Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate

You could just say less than 3...

...and check A1 for a number...

=IF(A1,IF(A1<3,A1*3.5,(2*3.5)+((A1-2)*1.5)),"")  Register To Reply

9. ## Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate

£3.32, but when changed to 1 mile calculates correcty as 3.50?  Register To Reply

10. ## Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate

Thats true too, will give it a try!  Register To Reply

11. ## Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate

Hi You can use this

=IF(A1=0,"",IF(OR(A1=1,A1=2),A1*3.5,(2*3.5)+((A1-2)*1.5)))

should have consider 0 too , sorry about that

Edited

or use jeffreybrown's formula  Register To Reply

12. ## Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate

That works, and bit neater looking, thank you  Register To Reply

13. ## Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate

Amy, Jeff,

I want the first 2 miles to be charged at £3.50 inclusive; for example regardless if they travel 1/2 mile, 1 mile, 1.5 miles or 2 miles, the charge is £3.50. Once that threshold is reached, anything in excess of 2 miles is charged an additional 1.67 per mile

Therefore a passenger travelling 1 mile charge = £3.50, 2 miles = £3.50, 3 miles = £4.67, 4 miles = £6.84  Register To Reply

14. ## Re: calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate

Amy, Jeff,

I want the first 2 miles to be charged at £3.50 inclusive; for example regardless if they travel 1/2 mile, 1 mile, 1.5 miles or 2 miles, the charge is £3.50. Once that threshold is reached, anything in excess of 2 miles is charged an additional 1.67 per mile

Therefore a passenger travelling 1 mile charge = £3.50, 2 miles = £3.50, 3 miles = £4.67, 4 miles = £6.84  Register To Reply