Excel Introducing a tolerance into a lookup of +/- 10%
Hi Experts
Hope you can help
Ive noticed some threads on here regarding this however none of them appear to have the specific criteria Im trying to reach as a result
basicaly, we ahve supplier packing lists that should contain 3 sizes of approved packaging sizes. By pasting in the detail, Column K performs a calculation (kindly provided previously by an expert on here) and when I performa lookup to the actual sizes they should retrieve the result of YES or NO
Ive dicsovered on some supplier entries they have either used the exact dimensions or near to the sizes that obviously results in slightly different calculations
Ive tried to use the TRUE function to buld in a tolerance but is there a way via formula to return a YES if the dimensions caluculation is below or above 10%?
If tries combinations of using ABS with <=10 and using helper columns but pnly return #VALUE results
I have attached the file - The formula Im looking for is currently in Column L - any help would be greatly appreciated
J
SumDimensions.xlsm
Hope you can help
Ive noticed some threads on here regarding this however none of them appear to have the specific criteria Im trying to reach as a result
basicaly, we ahve supplier packing lists that should contain 3 sizes of approved packaging sizes. By pasting in the detail, Column K performs a calculation (kindly provided previously by an expert on here) and when I performa lookup to the actual sizes they should retrieve the result of YES or NO
Ive dicsovered on some supplier entries they have either used the exact dimensions or near to the sizes that obviously results in slightly different calculations
Ive tried to use the TRUE function to buld in a tolerance but is there a way via formula to return a YES if the dimensions caluculation is below or above 10%?
If tries combinations of using ABS with <=10 and using helper columns but pnly return #VALUE results
I have attached the file - The formula Im looking for is currently in Column L - any help would be greatly appreciated
J
SumDimensions.xlsm
ASKER
HI
The values in Column H are being added together, foe example, 50x36x26 will result in 112 so we know this carton is correct
In columns R & S are the correct results so if the calculation in column J matches one of these, it returns YES as we know the correct size carton is shipping
However, if the supplier uses decimal points and the calc is slightly over or under I need the tolerance built into the lookup so it still returns the nearest result and does not flag as invalid
Jase
The values in Column H are being added together, foe example, 50x36x26 will result in 112 so we know this carton is correct
In columns R & S are the correct results so if the calculation in column J matches one of these, it returns YES as we know the correct size carton is shipping
However, if the supplier uses decimal points and the calc is slightly over or under I need the tolerance built into the lookup so it still returns the nearest result and does not flag as invalid
Jase
Jase
How/where is the correct carton size being determined for each item?
Is it based on something in the data?
How/where is the correct carton size being determined for each item?
Is it based on something in the data?
ASKER
HI
there are three carton sizes
50x26x36 (added together is 112)
40.5x28x17.8 (added together is 86)
40.5x28x35.6 (added together is 104)
These are the triggers for compliance in Column R that once added together, if the values in Column J match these then it returns YES - if not then NO
Id like to include in the lookup for example (vlookup(J2 +/- 10%,R:S,2,FALSE) in exact terms of course but Im not sure how to populate this into a formula ?
there are three carton sizes
50x26x36 (added together is 112)
40.5x28x17.8 (added together is 86)
40.5x28x35.6 (added together is 104)
These are the triggers for compliance in Column R that once added together, if the values in Column J match these then it returns YES - if not then NO
Id like to include in the lookup for example (vlookup(J2 +/- 10%,R:S,2,FALSE) in exact terms of course but Im not sure how to populate this into a formula ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
HI Ejgil
Thank you for your help with this
Cant thank you enough - its perfect !!
J
Thank you for your help with this
Cant thank you enough - its perfect !!
J
How are you looking up the approved package sizes/dimensions?
If you have the approved package size/dimension then it would easy to compare it to the actual size with a tolerance.