# Taxi Meter dollars to miles driven per trip

#### Ralphnet

##### New Member
The meter is \$1.60 to start for the 1st 11th of a mile, then it's .20 for each additional 11th of a mile or 2.20 a mile for each additional mile. I need a formula that will let me enter the meter amount (ie 24.80) and convert it to miles driven showing whole and 10ths of a mile (IE 8.6). This is for billing LIFT Transportation in San Diego for an account they have with us. they want the trips billed to show the distance of the ride. This would be from the total meter in one cell to the distance in another cell. Any help would Ralph

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
let's try this...

=IF(A10<=0.1,1.6,IF(A10>0.1,1.6+(A10-0.1)*2))

Given that your distance traveled is in A10

HTH

EDIT

I misread your request...this formula will probably not do what you want

I might be way off, but does this formula give the correct answer, where A1 contains your meter amount?

=TRUNC((A1-1.6+11/10*0.2)/2.2)+MOD((A1-1.6+11/10*0.2)/2.2,1)*10

I am not sure if your example of \$24.10 should yield 8.6 miles or if that was just an example, but I can't figure out how you got to that number based on your charges.

ok ralph,

Let's try this...I looked again at what you were requesting and I think this should work. First, given that charges increase after the first 1/11th of a mile by .20, when you convert back to 10ths of a mile, it's not going to look "even". Anyways, I believe this formula should get you your desired result.

Code:
``=IF(J9<=1.6,1/11,IF(J9>1.6,((J9-1.6)/2.2)+(1/11)))``

Edit...I just wanted to add that I think your example of a meter charge of \$24.80 having a distance of 8.6 might be misleading. The way I read it, the first 1/11th of a mile is \$1.60. So 24.8-1.6 = 23.2. We know that each mile is \$2.20. So with \$23.20 in remaining charges, we know that the distance was at least 10 miles. So 10 x 2.2 gives us \$22.20. Add in the last dollar (5/11th of a mile). That gives us 10 miles + 5/11ths of a mile + the intial 1/11th of a mile.

Granted, I might be completely wrong in the way I approached your problem, but at least you know how I went about looking at your situation! Wally

NPVC + Poorwallace thanks those dollar amounts and numbers ment nothing. just a type of answer. do you think one of your formulas will get me the correct distance by entering the totol amount? Thanks Ralph

Ralph,

My last post should be accurate. However, if you want your answer rounded to the nearest 10th of a mile, I would just use the formatting feature in Excel (otherwise, if you've got your cells formatted as general, you'll get answers like this...)

1.6 0.090909091 = 1/11
1.8 0.181818182 = 2/11
2 0.272727273 = 3/11
2.2 0.363636364 = 4/11
2.4 0.454545455 = 5/11
2.6 0.545454545 = 6/11
4.4 1.363636364 = 15/11
5.8 2
6.4 2.272727273
8 3
8.4 3.181818182
25.6 11
11.4 4.545454545
24.8 10.63636364

Wally

Hi,
poorwallace formula is good. You can even delete the second logical statement if(J9>1.6), and the answer still correct.
=IF(J9<=1.6,1/11,((J9-1.6)/2.2)+(1/11))

Regards,

egeslani said:
Hi,
poorwallace formula is good. You can even delete the second logical statement if(J9>1.6), and the answer still correct.
=IF(J9<=1.6,1/11,((J9-1.6)/2.2)+(1/11))

Regards,
Thanks Very Much Ralph. So do I put the formula in the cell where I want the miles to be and do I ref it to (say, J9) or whichever cell I will enter the dollar amount, Ralph

I'm not sure you need an IF statement at all,

If the taxi fare is in A1 this should give you the miles, rounded to a tenth of a mile

=ROUND((A1-1.6)/2.2+1/11,1)

barry houdini said:
I'm not sure you need an IF statement at all,

If the taxi fare is in A1 this should give you the miles, rounded to a tenth of a mile

=ROUND((A1-1.6)/2.2+1/11,1)

How about those extra partial miles 20 cents each 1/11th mile? How does that formula take into account those as indicated by the OP?

The meter is \$1.60 to start for the 1st 11th of a mile, then it's .20 for each additional 11th of a mile or 2.20 a mile for each additional mile

Replies
3
Views
435

### Forum statistics

1,203,524
Messages
6,055,910
Members
444,834
Latest member
ComputerExcel ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back