Taxi Meter dollars to miles driven per trip

[Ralphnet]

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

 

[poorwallace]

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

 

[NBVC]

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.

 

[poorwallace]

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.

=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

 

[Ralphnet]

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

 

[poorwallace]

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

 

[egeslani]

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,

 

[Ralphnet]

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

 

[barry houdini]

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)

 

[NBVC]

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