Calculating Transportation Cost with certain range of miles

[Mahfuzah90]

Hello guys, can anyone help me to create a formula for 2 situation as below:

SITUATION 1

KM = Kilometres
RM = currency
Total KM = 943KM (Assumption Cell = A2) , Total Cost = RM??
[TABLE="width: 500"]
<tbody>[TR]
[TD]First 500KM[/TD]
[TD]X RM0.70[/TD]
[/TR]
[TR]
[TD]Next 500KM[/TD]
[TD]X RM0.65[/TD]
[/TR]
</tbody>[/TABLE]

Formula i used in excel:
=A2*0.65+MIN(A2,500)*0.05
=RM637.95
*it's correct formula???,

SITUATION 2

KM = Kilometres
RM = currency

Total KM = 943KM (Assumption Cell = A2), Total Cost = RM??
[TABLE="width: 500"]
<tbody>[TR]
[TD]First 50 KM[/TD]
[TD]RM150.00[/TD]
[/TR]
[TR]
[TD]Next 200KM[/TD]
[TD]x RM3.00[/TD]
[/TR]
[TR]
[TD]Next 250KM[/TD]
[TD]x RM2.25[/TD]
[/TR]
[TR]
[TD]Next 250KM[/TD]
[TD]x RM1.50[/TD]
[/TR]
[TR]
[TD]Next 250KM[/TD]
[TD]x RM1.13[/TD]
[/TR]
[TR]
[TD]Next 250KM[/TD]
[TD]x RM0.75[/TD]
[/TR]
</tbody>[/TABLE]


Or if you have other formula that more easier than this? kindly share with me.
Thank you in advance.

 

[CatOnASpreadsheet]

Hi Mahfuzah90,

Are the values in the cells entered just as numeric values (as in, for Situation 1, Cell A3 has the value 500 or literally the text "First 500KM")? The same question applies to the second column, as well. Also, are your values likely to change over time or are we safe to hardcode them?

For example, here are two solutions for Situation 1:
=IF(A2<=500,A2*0.7,(500*0.7)+((A2-500)*0.65)) , for cases where the values will never change and / or there is text in the column, not numeric values
=IF(A2<=A3,A2*B3,(A3*B3)+((A2-A3)*B3)) , for cases where the values entered are numeric and may change over time.

Depending on your answer I can provide the solution for situation 2 accordingly.

Cheers,
Catalin

 

[Mahfuzah90]

Thanks for the feedback,

I already try the formula that u give for Situation 1, and it's worked. TQ

For the situation 2, I attach the table for more understanding.

Sorry for trouble. TQ for ur time

 

[CatOnASpreadsheet]

Hi Mahfuzah90,

By the looks of your spreadsheet you're using the hardcoded formulas. Here is the one for Situation 2:

=IF(F30<51,150,IF(F30<251,150+(F30-50)*3,IF(F30<501,750+(F30-250)*2.25,IF(F30<751,1312.5+(F30-500)*1.5,IF(F30<1000,1687.5+(F30-750)*1.13,1970+(F30-1000)*0.75)))))

Please keep in mind that if your milestones change or your RM rates change the formulas will no longer give the correct results.

Edit: (Also, there probably is a more efficient way of doing this than just nested IFs but I couldn't come up with it off the top of my head)

Cheers,
Catalin

 

[Mahfuzah90]

That's work. Thank you Catalin

 

[Dr. Demento]

The only suggestion I might add to Catalin's formula is that instead of compiling a single formula for the sum, have a single formula for each mileage range and SUM for the total. Easier to update/diagnose that way.

hth