Need function to calculate mathmatic formula base on two critera

[SPenney]

The combinations as follows:

LTL with 1 stop
LTL with 2 stops
LTL with 3 stops

TL with 1 stop
TL with 2 stops
TL with 3 stops

I need the (X miles times Y rate per mile) time percent discount (i
applicable) plus charge for number of stops. The discount occurs i
the shipment is TL. Stop rates are exclusive of one another (2 stop
isn't 1+2)

Staci

 

[Frank Kabel]

Hi
now you only have to tell for each combination what should be
calculated exactly (best with reference to your existing cell
references) :-=
e.g.
LTL with 1 stop -> A11*A12
LTL with 2 stops -> 0.89*A11*A12

 

[bsullins]

If the stop charges are static numbers you could do a vlookup for tha
portion also and just add it to the current one, otherwise you may wan
to use a nested 'IF' statement to return the stop charges

Here's the nested IF statemtent:

IF($A$13=1,5,IF($A$13=2,10,15))

This will return '5' if there is one stop, '10' if 2, and '15' if 3
Just change those values to whatever you like, or you can use th
vlookup formula.

Using this method you will ultimately have a formula like this...

=IF($A$12="LTL",(0.89*$A$10*$A$11)+(IF($A$13=1,5,IF($A$13=2,10,15))),IF($A$12="TL",($A$10*$A$11))+(IF($A$13=1,5,IF($A$13=2,10,15))))

Here's the result...

50 Rate <A10
300 Milage <A11
LTL % off <A12
2 Stops <A13
13360 Total <A14

You could also combine all of this into one formula by replacing th
cell references with the formulas in those cells.

Hope this helps...