Help with Excel. Speading cost based on weight of items

pformag on 14/12/2020 - 18:05

Hi All,

I am trying to work out an excel spreadsheet for calculating the cost of goods inside a shipping container based on the kgs per item.

Ie.

I know the unit cost of each item.
The kgs or cbm of each item.
the cost to transport for the whole container.

So I need to spread the cost of the transport over each item, but there will be multiple items, with different kgs, so I need it to be weighted to each item.

Hope this makes sense.

Education & Work

Comments

  • Hybroid on 14/12/2020 - 18:18
    +1

    Easy. Got a basic template for you setup.

    https://i.imgur.com/IWv1EvF.png

    Plug in the amber fields and will auto-calc.

    • Switchblade88 on 14/12/2020 - 18:23

      Not in an image he won't!

      But the premise is sound - find the total weight, and the total shipping cost to get the $/kg. Then, use another column to multiply this figure by the weight of the item.

      Protip - set your fomula variable to refernce the 'price/kg' cell as absolute, i.e. with the dollar signs $E$12. That way, when you fill down, it'll keep the right reference in your column.

      • Hybroid on 14/12/2020 - 18:25

        Yep, I can share the template or they can recreate it based on above and understand the logic in the process.

        =G4*$H$7 already included. :)

    • [Deactivated] on 14/12/2020 - 18:25

      Cost/KG field doesn't seem right

      • Hybroid on 14/12/2020 - 18:27

        Ehh, 4.76 is the Cost/KG. The line items ones above are Total KG times Cost/KG. Can be renamed I suppose.

        • [Deactivated] on 14/12/2020 - 18:38
          +1

          Ah, that would make more sense.

    • pformag on 14/12/2020 - 18:36

      any chance you can PM the excel file, via dropbox or anything?

      • [Deactivated] on 14/12/2020 - 18:39
        +4

        I'm not saying Hybroid is malicious or anything but considering what seems to be a lack of experience with Excel, don't enable any content/macros in a workbook from an unknown source until you've done DD.

        • pformag on 14/12/2020 - 18:39

          cheers for the advice.

  • tsunamisurfer on 14/12/2020 - 18:25
    +3

    Are you wanting help with the arithmetic or excel?

    Because what you are asking is very basic in Excel.

  • Muzeeb on 14/12/2020 - 18:27

    Not my forté

  • JimmyF on 14/12/2020 - 19:55
    +1

    Best to use airtasker to farm your work out.

  • Emerald Owl on 14/12/2020 - 20:21
    -5

    Why are you people helping for free?

  • netjock on 14/12/2020 - 22:42

    Your container is CBM therefore the % per items should be: CBM Item / CBM of container to work out % multiply by $ to move the full size container.

    Unless the big container has a kg limit which your items might go over you won't need it as a secondary rule.

    • pformag on 15/12/2020 - 08:38

      Thanks

Login or Join to leave a comment
Login Join