Formula to calculate COGS value using different prices based on qty available in different locations

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Hi,

    I am struggling to come up with a formula to calculate total cost of goods value based on qty available in different locations and taking into account orders already taken for the same product, so I am hoping someone in this forum could help me out :)

    I am attaching sample data - "Cost of Goods" column D is qty ordered and the "Unit Rate" tab are various rates I would like to use. So firstly I would like to consider rates in the warehouse, then in transit and once no stock is available in these locations use the latest rate card to place new orders.

    Thanks in advance.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!