[mso] Estimating the (cubic) volume of an order
- From: "Aharon David \(Eron\)" <wellard_br@xxxxxxxxx>
- To: "mso@xxxxxxxxxxxxx" <mso@xxxxxxxxxxxxx>
- Date: Sat, 30 Apr 2011 12:33:46 -0700 (PDT)
Hi List,
Thanks to you, I am now able to validate whether the quantities ordered are
multiples of the master carton of each product. Now I am trying to figure how
to have excel estimate the volume of the order, for freight quotation purposes,
and that is where things get really hairy...
My first idea was to create an imaginary "pile" using the width and lenght of
the largest carton (they vary, as you can see in columns D, E and F), and
multiply these two numbers by the sum of all heights.
In the formula to show the supposed volume, I used the expressions $D$3 and
$E$3 for width and lenght; then, after deleting all the products that were not
ordered, I used Sort/ column D to have the largest width at $D$3 (too bad it
does not necessarily corresponds to the largest lenght, but you cannot have it
all. Perhaps one can tell excel to compare the values in a column and pick the
largest, but this seems stratospheric for me...).
In the example below, the volume informed at the bottom of column F is dumb,
because I failed to consider the number of boxes ordered for each product.
Column G (an attempt to take the failure above into consideration) must/ needs/
has to be also wrong, because I know from experience that this cargo does not
measure 40CbM (that's 1412,5 cubic feet)!
Any hint at what I should do, or where to look for an education on this? (this
is my respectful way to tell you guys that I do not expect the answer in a
platter - I know neither excel nor any program "think"; they carry the
instructions the thinker tells them to do. my thinking capacity however seems
rather primitive so far...).
Thanks,
Aharon
A B C D E F G H I J K L M
Unit #/carton weight (Kg) W (m) L (m) H (m) total height Vol M3 Model Quant.
unit price total price Weight validation
SETS 2 14.2 0.8 0.35 0.25 0.5 0.07 AA 2 395 790 14.2 TRUE
PRS 4 12.5 0.57 0.43 0.26 3.12 0.0515 AB 12 27.7 332.4 37.5 TRUE
SETS 4 12.3 0.57 0.41 0.22 0.88 0.0515 AC 4 143 572 12.3 TRUE
SETS 4 13.7 0.56 0.4 0.28 10.1 0.063 AD 36 69.3 2494.8 123.3 TRUE
PRS 4 10 0.55 0.5 0.21 4.2 0.053 AE 20 27.7 554 50 TRUE
SETS 4 11.5 0.5 0.5 0.21 16.8 0.053 AF 80 41.6 3328 230 TRUE
PCS 2 17.4 0.41 0.41 0.44 1.76 0.074 AG 4 110 440 34.8 TRUE
PCS 2 17.8 0.41 0.41 0.44 1.76 0.074 AH 4 130 520 35.6 TRUE
PCS 4 10.6 0.4 0.4 0.22 0.88 0.035 AI 4 119 476 10.6 TRUE
2.53 40 9507.2 548.3 (MOD(I,B)=0
Price after 10% discount 8556.48
Aharon Konforti
http://personal-computer-tutor.com/abc3/v28/greg28.htm
>*************************************************************
>
>
>
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to share files with the group, you must join our Yahoo sister group.
This group will not allow for posting of emails, but will allow you to join
and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
Other related posts:
- » [mso] Estimating the (cubic) volume of an order - Aharon David \(Eron\)
