Formula to ship product when cumulative tonnage reaches a certain level.

Hi all,

I need help with a formula. Essentially I manufacture three products - A, B and C. Once I have manufactured 25,000 tons of product it can be shipped. Within the shipment I want to take first available Product A then product B and then Product C. Hopefully the attached Excel file will make the problem a little clearer. Formulas required for the shaded area.

To add a further complication I want to have the flexibility to change the order in which to ship the product for example first to Product B then Product A and then Product C. (Perhaps this can be achieved with a VLOOKUP or MATCH?).

Happy to use code if that is the most elegant solution.

Many thanks in advance,

Andrew

Attached is the missing attachmentProduct Shipment Example.xlsmProduct Shipment Example.xlsm

first formula at E19
=IF(SUM(E$11:E11)<$E$17;E11;E11-(E11-($E$17-SUM(E10:E$11))))

copy down till E21

Thanks very much indeed Ghozi,

This doesn't quite do what I want because if I extend the series into Week 4 and beyond I need to use the outstanding stock levels for each product (together with new manufactured product) to make the calculation. However, you have given me a base from which I can hopefully build on.

Regards,

Andrew