Percentage tolerance with break points.

[Mcgraw]

Hello, I'm trying to devise a simple to use calclator to determine if the checked weight of an item of cargo submitted to a shipper, compared to the declared weight is within a range of acceptable variance percentages.

The acceptable tolerances are

0-100 KG- 10%
+300 KG- 7%
+500 KG- 5%
+1000 Kg-3%

I basically want a user to enter declared weight in A1, check weight A2, and then have a cell display OK or AMEND when both values are entered.

I have no clue if this is really complicated, or I am really dumb, but any help would be warmly received!

 

[Ron Rosenfeld]

Hello, I'm trying to devise a simple to use calclator to determine if the checked weight of an item of cargo submitted to a shipper, compared to the declared weight is within a range of acceptable variance percentages.

The acceptable tolerances are

0-100 KG- 10%
+300 KG- 7%
+500 KG- 5%
+1000 Kg-3%

I basically want a user to enter declared weight in A1, check weight A2, and then have a cell display OK or AMEND when both values are entered.

I have no clue if this is really complicated, or I am really dumb, but any help would be warmly received!

Set up a table and NAME it ToleranceTable (or use the absolute cell reference in the formula below).
Given your data, and assuming that weights over 1000 Kg also have a tolerance of 3%, then:

=IF(VLOOKUP(A1,ToleranceTable,2)>=ABS((A2-A1)/A1),"OK","AMEND")

Your table should look like:

Wt Tolerances
0 10%
100 7%
300 5%
500 3%