# 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?

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)

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

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

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

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

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

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)),"")

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?

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

Thats true too, will give it a try!

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

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

That works, and bit neater looking, thank you

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

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1