need help logical function alternate result

[stan_s]

i want to make a spreadsheet to quote truck load rates.
the carrier rate per mile will depend on the final destination stat
from a single origination point. each destination state has a rate pe
mile. how may i create a function that would autmatically result i
the carrier rate after entering the destination state?

also need to include a formula to calculate fuel surcharges. the fue
surcharge increases every $0.05 per gallon over $1.20 per gallon

any suggestions

 

[Frank Kabel]

Hi
for the first one try the following:
- create a table which has the destinations and rates
- use VLOOKUP

See:
http://www.mvps.org/dmcritchie/excel/vlookup.htm

 

[Gord Dibben]

Stan

For rate per mile based on State, use a VLOOKUP formula.

Assume 20 States are in Column A, corresponding Rates are in Column B.

In C1 enter =VLOOKUP(D1,A1:B20,2,FALSE)

Enter a State in D1 and C1 will reurn the Rate for that State.

You could create a Data Validation drop-down list in D1 so you could just
click on a State name to have it entered.

See Debra Dalgleish's site for Data Validation info.

http://www.contextures.on.ca/xlDataVal01.html

For surcharge rates use the following formula in a cell.

=LOOKUP(A1,{1.25;1.3;1.35;1.4;1.45;1.5},{1;2;3;4;5;6})

The group or 1;2;3;4;5;6 would be your surcharge rates. The first group is
the cost per gallon of gas.

Enter a cost per gallon in A1 and the formula will choose the surcharge.

Gord Dibben Excel MVP