Creating an Excel Freight Calculator

[Elissa]

I am trying to create an excel calculater based on the information belo
and am getting stuck:

Freight Rates Percentage
$0.00 - $10,000.00 2.00%
$10,001.00 - $50,000.00 1.50%
$50,001.00 - $400,000.00 0.50%

So based on the dollar of a cell i want the freight cell to calculat
the amount based on the corresponding percentage.

Here is the formula i was using thinking it would work but its not:
=IF(H65<10,0000.00,.2,IF(H65<50,000.00,.15,IF(H65<400,0000.00,.05))

 

[Spencer101]

I am trying to create an excel calculater based on the information belo
and am getting stuck:

Freight Rates Percentag
$0.00 - $10,000.00 2.00
$10,001.00 - $50,000.00 1.50
$50,001.00 - $400,000.00 0.50

So based on the dollar of a cell i want the freight cell to calculat
the amount based on the corresponding percentage

Here is the formula i was using thinking it would work but its not
=IF(H65<10,0000.00,.2,IF(H65<50,000.00,.15,IF(H65<400,0000.00,.05))

If I've understood you correctly, this should work...

=IF(AND(H65>0,H65<=10000),H65*0.2,IF(AND(H65>10000,H65<=50000),H65*0.15,IF(AND(H65>50000,H65<=400000),H65*0.05,""))

EDIT: Formula amended to not show "FALSE" if H65 is empt
EDIT2: Sorry, had the ='s in the wrong place. This one SHOULD work ;

 

[Claus Busch]

Hi Elisa,

Am Wed, 6 Jun 2012 16:56:36 +0000 schrieb Elissa:

Freight Rates Percentage
$0.00 - $10,000.00 2.00%
$10,001.00 - $50,000.00 1.50%
$50,001.00 - $400,000.00 0.50%

your price in A1, then your price plus percentage:
=A1*VLOOKUP(A1,{0,1.02;10001,1.015;50001,1.005},2,1)


Regards
Claus Busch