• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate Utilisation First come first basis.

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

Attachments

  • Multiply First Come Basis.xlsx
    10.4 KB · Views: 11
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

  • Multiply First Come Basis (PB).xlsx
    18.3 KB · Views: 9
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.
 
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

  • Multiply First Come Basis (PB doc).xlsx
    18.7 KB · Views: 11
  • Multiply First Come Basis (PB).xlsx
    17.5 KB · Views: 10
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.
 
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.

Thanks for giving your precious time. I'm going through your update.
 
@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:
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!
 
Back
Top