I am trying to create a tool in excel that would enable someone to easily obtain a quote based off of different Mileage and Weight. Essentially I'm trying to have it where I can enter a weight and distance, and have a formula apply the appropriate rate which would include the minimum if the total does not meet that amount. The price is determined by .01*weight*rate(dollar amount in table)
I started out by using nested "If" formulas but the cap is 7 for those...If anyone could share the best route to accomplish this, it would be very much appreciated.
MILES MINIMUM 100 1000 2000 5000 CAP
0 - 11 $10.00 $5.25 $4.85 $4.30 $3.40 $170.00
11.1 - 22 $11.00 $5.75 $5.10 $4.55 $3.70 $185.00
22.1 - 35 $12.00 $6.25 $5.40 $4.90 $4.00 $200.00
35.1 - 50 $15.00 $7.00 $5.70 $5.40 $4.20 $210.00
Bookmarks