ExcelBanter - Creating an Excel Freight Calculator

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Creating an Excel Freight Calculator (https://www.excelbanter.com/excel-worksheet-functions/446246-creating-excel-freight-calculator.html)

Elissa

Creating an Excel Freight Calculator

I am trying to create an excel calculater based on the information below 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 calculate 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

Quote:

Originally Posted by Elissa (Post 1602428)

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

=IF(AND(H650,H65<=10000),H65*0.2,IF(AND(H6510000 ,H65<=50000),H65*0.15,IF(AND(H6550000,H65<=400000 ),H65*0.05,"")))

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

Elissa

Quote:

Originally Posted by Spencer101 (Post 1602429)

That worked amazing. Thank you so much for your help :)

Claus Busch

Creating an Excel Freight Calculator

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Spencer101

Quote:

Originally Posted by Elissa (Post 1602430)

That worked amazing. Thank you so much for your help :)

Not a problem. You know where we are if you need any more help :)

All times are GMT +1. The time now is 08:24 AM.