Formula Search - Need some master magic!

CNelson19

New Member
Joined
Jul 31, 2018
Messages
3
Hello, Below is a list of data. I am trying to find a common Divisor for the last column ( Dim. weight w/o Divisor) that will make the rated weight equal to the shipment rated weight. We can throw out outliers like line 2 with 24 rated weight and 1 original weight. Any ideas? I am stuck!

Rated Weight Orig. Weight Height Weight Length Dim. Weight w/o Divisor
[TABLE="width: 833"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]2.00[/TD]
[TD]1.00[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]320[/TD]
[/TR]
[TR]
[TD]24.00[/TD]
[TD]1.00[/TD]
[TD]15[/TD]
[TD]14[/TD]
[TD]22[/TD]
[TD]4620[/TD]
[/TR]
[TR]
[TD]7.00[/TD]
[TD]4.00[/TD]
[TD]9[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]1296[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[TD]1.00[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]224[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[TD]1.00[/TD]
[TD]3[/TD]
[TD]11[/TD]
[TD]8[/TD]
[TD]264[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[TD]1.00[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]384[/TD]
[/TR]
[TR]
[TD]3.00[/TD]
[TD]2.00[/TD]
[TD]4[/TD]
[TD]11[/TD]
[TD]13[/TD]
[TD]572[/TD]
[/TR]
[TR]
[TD]14.00[/TD]
[TD]4.00[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]18[/TD]
[TD]2592[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[TD]1.00[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]210[/TD]
[/TR]
[TR]
[TD]7.00[/TD]
[TD]4.00[/TD]
[TD]9[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]1296[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[TD]1.00[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]210[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[TD]1.00[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]330[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[TD]1.00[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[TD]1.00[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]384[/TD]
[/TR]
[TR]
[TD]6.00[/TD]
[TD]3.00[/TD]
[TD]4[/TD]
[TD]13[/TD]
[TD]22[/TD]
[TD]1144[/TD]
[/TR]
[TR]
[TD]14.00[/TD]
[TD]7.00[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]18[/TD]
[TD]2592[/TD]
[/TR]
[TR]
[TD]3.00[/TD]
[TD]2.00[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]13[/TD]
[TD]390[/TD]
[/TR]
[TR]
[TD]9.00[/TD]
[TD]4.00[/TD]
[TD]10[/TD]
[TD]13[/TD]
[TD]13[/TD]
[TD]1690[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[TD]1.00[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]384[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[TD]1.00[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]384[/TD]
[/TR]
[TR]
[TD]3.00[/TD]
[TD]1.00[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]432[/TD]
[/TR]
[TR]
[TD]3.00[/TD]
[TD]2.00[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]432[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
It's just the product of the previous 3 columns, I'm not sure you can connect that to the rated weight.
 
Upvote 0
It the product of the L*W*H divided by an unknown number. This number should result in the original weight.
 
Upvote 0
If you divide there's still no exact pattern, even after deleting some outliers. A regression might help, like:

UrhISm3.png
 
Last edited:
Upvote 0
It the product of the L*W*H divided by an unknown number. This number should result in the original weight.

Maybe the second column is some kind of adjustment, but I'm not seeing exactly how yet, and there are outliers as you said. E.g. the last two rows differ only by:


Excel 2010
ABCDEF
2231689432
2332689432
Sheet1 (3)
 
Last edited:
Upvote 0
How about
Excel 2013/2016
ABCDEFG
1215883202
2241151422462024
3749121212967
4214782242
52131182642
6216883842
732411135723
8144121218259214
Engine
Cell Formulas
RangeFormula
G1=ROUNDUP(F1/194,0)
 
Last edited:
Upvote 0
In what way?
Assuming weights are in Kg & dimensions in cm then
1Kg equates to ~194cc
 
Upvote 0
Not as simple :(
If the rated weight is 2Kg then it could be anywhere from 1.01Kg to 2Kg giving anywhere between 196 & 388 cubic whatevers
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top