premjeetgupta
Member
Need help for a formula that calculate utilised items first come first basis.
Sample sheet attached.
Sample sheet attached.
Attachments

10.4 KB Views: 9
Your given formula working like charm..!!! Thanks for helping me out.Hi, to both!
Another option, with single formula, could be:
=SUMPRODUCT((J8>MMULT({0,0,0;1,0,0;1,1,0},CHOOSE({1;2;3},E8,G8,I8))),J8MMULT({0,0,0;1,0,0;1,1,0},CHOOSE({1;2;3},E8,G8,I8)),CHOOSE({1;2;3},D8,F8D8,H8F8))
Blessings!
Thanks for giving your precious time. I'm going through your update.The first file gives you an outside chance of following the calculation for a single case.
The second file uses row relative references for 'data' and 'totalUtilized' so will take a column of data inputs.
Once you have understood the calculation I am sure it is possible to replace the array multiplications by three distinct summations or differences, perhaps using helper cells. I have tidied the calculation up a bit by using LOOKUP to pick the alternate columns of data out to form an array before using array multiplication to calculate the cumulative sums and the differences.
Hi, @Peter Bartholomew !Something I never managed was to produce direct referencing formulas of the complexity that you handle at a single step. How do you manage it? Do you start with helper ranges for development and then eliminate them or do you simply go for the one cell solution?