Looking for guidance with a fairly complex (to me anyways) transportation cost estimating workbook.

[dwmlr]

Hello Ladies and Gentlemen,

Just like the title says, I need some guidance with this project.

I am part of a very small transport company and am trying to make a workbook that I can input parameters into and it will spit out a dollar amount that it would cost my potential customer.

Below are my 8 input fields and the cost is what I'm trying to get filled in.

​

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]R/T Miles[/TD]
[TD]Stop[/TD]
[TD]Qty[/TD]
[TD]Weight[/TD]
[TD]Liftgate[/TD]
[TD]Route Type[/TD]
[TD]Vehicle Type[/TD]
[TD]Wait Time[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






R/T Miles: =Round Trip Miles; total route miles.
Stop: = Number of stops that the vehicle will be making. This will be $9.00 per stop.
Qty: = Number of packages, pallets.
Weight: =Total weight of all items. First 50 lbs are not charged. There will be a charge of $5 for every 50 lbs over the initial 50 lbs.
Liftgate: = Is the liftgate on the truck required. If it's required, then a charge of $25 will be added.
Route Type: = Routed($10), STAT($50), 2-4 hours($45), 4-6 hours($40), 6-8 hours($35), 12 hours($30), 24 hours($25). Anything over 24 hours will not have an additional charge.
Vehicle Type: = Based on the vehicle type, the per mile charge will be; Car(.85), Mini van(.95), cargo van(1.15), sprinter van(1.25), small pickup(1.35), full size pickup(1.45), straight truck(1.70).
Wait Time: = Amount we will charge if we have to wait over a predetermined grace period. First 10 minutes are free. After that it will be $3 for every 5 minutes we have to wait.
And then, after all of that has been calculated, there will be a FSC (Fuel Surcharge) added to the total. It will be a percent based off the national average for the fuel type used by the vehicle that has done/is doing the delivery.

I have tried to use multi-ifs and vlookups, but have thus far been unsuccessful. I'm using Office 2016 and running Windows 7.

So, I hope that you will not beat me up too bad over not being able to figure this out on my own.

I can't attach the workbook that I have to this forum for some reason?, so I can email it to whoever thinks they can help.

Thank you for taking the time to read this post and hopefully offer me some help.

Doug

 

[liveinhope]

how does the qty impact on the price ... can't see anything in your post to explain that

 

[njimack]

Although this would be easier with a series of lookup tables, the below formula has everything you've asked for (apart from the fuel surcharge because you haven't explained how it's calculated)...

Sheet1

*	A	B	C	D	E	F	G	H	I
R/T	Stop	Qty	Weight	Liftgate	Route Type	Vehicle Type	Wait Time	Cost
*	Yes	STAT	Mini van

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:68px;"><col style="width:68px;"><col style="width:68px;"><col style="width:68px;"><col style="width:68px;"><col style="width:68px;"><col style="width:97px;"><col style="width:81.5px;"><col style="width:68px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]250[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]143.5[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula I2 =([Stop]*9) +(MAX(ROUNDUP(([Weight]-50)/50,0),0)*5) +IF([Liftgate]="Yes",25,0) +IFERROR(LOOKUP([Route Type],{"Routed","STAT",0,4,6,8,12,24},{10,50,45,40,35,30,25}),25) +([R/T]*LOOKUP([Vehicle Type],{"Car","Mini van","Cargo van","Sprinter van","Small pickup","Full size pickup","Straight truck"},{0.85,0.95,1.15,1.25,1.35,1.45,1.7})) +MAX(ROUNDUP(([Wait Time]-10)/5,0),0)*3 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[dwmlr]

Thank you for taking the time to read my post LiveInHope. After giving that some thought, I'm going to say let's take it out of the equation.

Doug

 

[dwmlr]

Thanks for your reply njimack. I have all of the parameters listed on separate sheets with named ranges. Would it help you to help me if I sent it to you or put it on my OneDrive?

Doug

 

[njimack]

I'll pm you my email address.

Thanks for your reply njimack. I have all of the parameters listed on separate sheets with named ranges. Would it help you to help me if I sent it to you or put it on my OneDrive?

Doug

 

[dwmlr]

Here's a link to my OneDrive where I have the file if it will help anyone?

Calculator Workbook.

Thank you for your time.

 

[dwmlr]

@njimack, you are awesome. I don't know how you did it, but your work is right on!!!!

Thank you so very much.