# Calculate Utilisation First come first basis.

#### premjeetgupta

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

#### Attachments

• 10.4 KB Views: 9

#### Peter Bartholomew

##### Well-Known Member
This appear to work.
I would be the first to admit that it is not entirely in the spirit of Excel and end user computing.
I will leave it to others to improve.

#### Attachments

• 18.3 KB Views: 8

#### premjeetgupta

##### Member
Hi @Peter Bartholomew thanks for your response.
The formula giving the desired result for just 1 Row (in actual sheet each row is for different product); and literally I'm not able to understand how it is defined but thanks.

#### premjeetgupta

##### Member
Attaching updated reference file.

#### Attachments

• 10.5 KB Views: 6

#### Peter Bartholomew

##### Well-Known Member
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.

#### Attachments

• 18.7 KB Views: 10
• 17.5 KB Views: 8

#### John Jairo V

##### Well-Known Member
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))),J8-MMULT({0,0,0;1,0,0;1,1,0},CHOOSE({1;2;3},E8,G8,I8)),CHOOSE({1;2;3},D8,F8-D8,H8-F8))

Blessings!

#### premjeetgupta

##### Member
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))),J8-MMULT({0,0,0;1,0,0;1,1,0},CHOOSE({1;2;3},E8,G8,I8)),CHOOSE({1;2;3},D8,F8-D8,H8-F8))

Blessings!
Your given formula working like charm..!!! Thanks for helping me out.

#### premjeetgupta

##### Member
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.

#### Peter Bartholomew

##### Well-Known Member
@John Jairo
I suspect your version more closely matches 'client expectations'.

Strictly speaking my solution is also a 'single formula' because in calculation each named part of the formula is simply nested within the next. What naming the partial formulas does is allow me to do is lay out the calculation as a sequence of steps if I so choose, and, in fact, that is how I build the formula.

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?

@premjeet gupta
The calculation steps I set out in the first file should also help you with John's solution. There are differences:
I use the fact that Names are array formulas; John uses SUMPRODUCT as an array wrapper function.
I used LOOKUP to select alternate cells as an array; John used CHOOSE to select the cells;
I used a further matrix operation to difference adjacent values; John used directly calculatd differences.

Last edited:

#### John Jairo V

##### Well-Known Member
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?
Hi, @Peter Bartholomew !

In my case, just start from inside to outside, working with F9 evaluating every step checking the parts. I build all the formula in a single cell. Blessings!