Hello forum
I am learning Excel and I am trying to create a formula which will display tarriffs for a taxi depending on how far it has travelled -
Distance Tarriff
0-0.5 miles $1.00
0.6-2 miles $0.80
3-5 miles $0.75
6-20 miles $0.70
I want to enter the exact mileage travelled into a cell e.g. 8 miles, then I would like the exact cost to be shown in the cell below. In this case the cell will display $0.70 for 8 miles.
How can I do this?
Thankyou,
Tom
Last edited by tomkilbourn; 07-17-2009 at 08:06 AM. Reason: I entered the wrong tarrif for 8 miles
In the distance column enter only the lower limits.
ie. 0, 0.6, 3, 6
Then use formula like:
=Lookup(A1,X1:Y4)
where A1 contains your entered mileage and X1:Y4 contains your table
Note: Did you really want 0.70 for 8miles?
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I can't figure why it's 0,75?
Whoops I meant $0.70 for 8 miles.
The actual amounts can be altered, I just need to get the formula running.
But it's not 0,70?
It's 0,70*number of miles, right?
Or is same price for 8 miles and 18 miles?
Like.. when you go half mile you pay 1$, when you go 20 miles you pay 0,70
I guess it's like this: Book1.xls
It is currently the same price for 8 or 18 milesOriginally Posted by zbor
But it's not 0,70?
It's 0,70*number of miles, right?
Or is same price for 8 miles and 18 miles?
Like.. when you go half mile you pay 1$, when you go 20 miles you pay 0,70
I guess it's like this: Attachment 48608
But as this is not "real world", Im just making it up, I could change it..
I just want to get the general idea of working out the formula up and running
Have you considered my suggestion above?
Yes, I am trying to make it work as we speak, Im really new to formulas like this so I am struggling a bit!
You could also create a custom function to calculate the answer.
Then in the cell you would type =TARRIFF(A1)
Please Login or Register to view this content.
Well, in that case you just remove B1* from formula and leave LOOKUP part...It is currently the same price for 8 or 18 miles
But as this is not "real world", Im just making it up, I could change it..
I just want to get the general idea of working out the formula up and running
But I still don't know why driving 1 mile would cost 0.80 and driving 15 miles would cost 0,70$.
In that case I would say to driver: charge me this mile as you driving me 10 miles
I still think you need that B1* to get price per mile
I have altered the tarrifs as I agree they were a little perculiar!
Now I have the following tarrif -
Tarrif Cost in $
0 - 1 mile 1
1.1 - 5 miles 0.8
5.1 - 10 miles 0.75
10.1 - 20 miles 0.7
And I am using the formula -
=D7*LOOKUP(D7,{0;1.1;5.1;10.1},{1;0.8;0.75;0.7})
If I travel 3.5 miles the formula is bringing up a total of $2.80, is this correct?
If I travel 15.7 miles the forumula is bringing up a toal of $10.99, is this correct?
I have attached my .xls file.
Yes, they are right per your array definitions
How could I calculate a total if the tarrif was added up by the actual amount per mile, not the final mile tarrif?
For example under the existing lookup formula of -
=D7*LOOKUP(D7,{0;1.1;5.1;10.1},{1;0.8;0.75;0.7})
3.5 miles will cost $2.80. Calculated by the final mile tarrif being $0.80 per mile ($0.80+0.80+0.80+$0.40 = $2.80).
But, the first 0-1 miles tarrif is actually $1.00 per mile, not $0.80, so the final mile tarrif should be $1.00+$0.80+$0.80+$0.40 = $3.00.
How can I make the formula calulate a total tarrif based on the tarrifs throughout the journey, not just the last mile tarrif?
I hope what I have said makes sense!
Can you clarify how you come up with your new total?
What should the results be for these (and how do you get those results?)
1.5 miles
2.0 miles
4.0 miles
5.0 miles
5.5 miles
10.0 miles
Sure..
Under the old method which is =D7*LOOKUP(D7,{0;1.1;5.1;10.1},{1;0.8;0.75;0.7}) -
1.5 miles = $1.20
2.0 miles = $1.60
4.0 miles = $3.20
5.0 miles = $4.00
5.5 miles = $4.125
10.0 miles = $7.50
But, these totals are worked out by the final mile tarrif.
The acutal price should be worked out by each tarrif price during the journey -
Tarrif (by distance) Cost in £ per mile
0 - 1 mile 1
1.1 - 5 miles 0.8
5.1 - 10 miles 0.75
10.1 - 20 miles 0.7
So a 4.0 mile journey could be worked out something like this -
0-1 mile = $1.00
1.1-5 miles = $0.80* 3 miles @ $0.80
Total - £3.80
Perhaps:
=IF(D7>=1,1+IF(D7>=1,(D7-1)*LOOKUP(D7-1,{0;5.1;10.1},{0.8;0.75;0.7})))
Thats quite some formula, I would never be able to do that on my own! It does seem to work, but brain wont stretch that far to confirm if it is producing the correct numbers, so I have attached my spread sheet with the original formula and the also your new formula.
Maybe someone can enter some figures into the distance cell (D7) and confirm wether it is crunching the numbers correctly by working out the total cost by each tarrif price during the journey?
Can you check to see if the formula is correct?
If I add up a few totals by hand I am getting different results to the formula. I have attached the .xls spreadsheet.Please Login or Register to view this content.
For example, If I enter 2 miles as the distance, the formula above is giving me a total of $2.00, which isn't correct, it should be $1.80
Try this:
=SUM(IF(D19>{0,1,5,10}, (D19 - {0,1,5,10}) * {1,-0.2,-0.05,-0.05}) )
It gives these results:
Is there a minimum fee?Please Login or Register to view this content.
Do you round to the nearest 0.05, or something else?
Entia non sunt multiplicanda sine necessitate
Hello SHG,
Your formula seems spot-on.
I will need to add a pickup fee to the final tariff.
The next step is for me to see if I can use the same formula to calculate the length of time of the journey -
Journey Time Cost in $ per minute
0-10 minutes 1
11-20 minutes 0.80
21-30 minutes 0.75
31-40 minutes 0.70
Then I can add the total of the journey time cost to the other formula (the distance travelled total cost) and then add a pickup tarrif e.g.
Weekday 9am-5pm $5.00,
Weekday other $3.00,
Weekend tariff $10.00 etc
Would it be possible for a breakdown of your formula? I would really like to know how you created it.
The formula does this: The Fee is
plus $1.00/mile for miles over 0
minus $0.20/mile for miles over 1
minus $0.05/mile for miles over 5
minus $0.05/mile for miles over 10
The same formula for time fee is
=SUM(IF(A1 > {0,10,20,30}, (A1 - {0,10,20,30}) * {1,-0.2,-0.05,-0.05}) )
Yes you have got it
Thank you for explaining the problem more clearly than what I could!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks