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

 

[barry houdini]

Hi NBVC

Assuming A1 contains $1.60 or higher ($1.60 being the minimum fare) then it works as far as I can see. If A1 increases by 20 cents then the result of the formula I suggested will be 1/11 greater (subject to rounding as requested by the poster).

I saw another thread earlier from the OP (it now seems to have been deleted) in which he gave an example. $5.80 should return 2 miles. The formula I posted will do that too which re-inforces my belief that it's correct.

In fact, it isn't much different from the formula posted by egeslani, except I assumed that the fare can't be lower than $1.60 and added in the rounding element.

Of course, if A1 might be blank you may wish to insert an IF function to ensure that the result is blank too....

...and finally....

Don't taxis normally charge for time too so that the driver still gets paid if he's stuck in traffic for an hour? In which case you can't extrapolate the distance travelled from the fare....or don't they have jams in san Diego? :wink:

 

[NBVC]

Hi Barry,

Thanks for responding!

I guess we will have to leave it to the OP to try the formulas and do some manual calculations to see which gives the right answer. He should beware that our formulas are tested for how we each interpret his original question.

I interpreted that each 1/11th of a mile over whole miles is charged at 20 cents each and so if I enter $5.80 in my formula, it returns 2.0909 miles.

I used this formula to go from miles to $ :=1.6+TRUNC(F10)*2.2+MOD(F10,1)*11/10*0.2-(11/10*0.2), where F10 contains the # of miles and assumed the 1st mile is 1.60, each additional whole mile is $2.20 and each fraction of a mile after that is charged at $0.20/11th of a mile. So if I enter 2 miles, I get $5.78.

Then my attempt at reversing the formula so that you enter $ and get miles is what I posted: =TRUNC((G10-1.6+11/10*0.2)/2.2)+MOD((G10-1.6+11/10*0.2)/2.2,1)*10, which I admit is a little clumsy, but returns the correct reverse amount (i.e If I enter $5.78, I get 2 miles.)

As I said, the original poster has to decide on what his requirements are and what results he wants.

 

[Yogi Anand]

Hi Ralph:

Here is my take on it ...

=INT((amount-1.6)/0.2)*1/11+1/11

 

[barry houdini]

Ralph,

I see you've started another thread

http://www.mrexcel.com/board2/viewtopic.php?t=181598

It's normally easier to continue within the same thread so that everybody can follow the "story so far" as it were.

If you want the result to be blank before the distance has been entered then use something like

=IF(A1<>"",ROUND((A1-1.6)/2.2+1/11,1),"")

 

[Ralphnet]

Thank You Everbody

I want to thank everybody for helping me with this, I wasn't doubting your formulas, I was using them wrong and the cells were formatted wrong.I phrased my questions wrong, I wasn't meaning to doubt the people I asked for help. I now have a working spreedsheet for billing the customer they way they want it . Thank You all, Ralph[/b][/i]