Store 1st result of iterative calc, then 2nd... to 200+ iterations

[atfoley16]

I have a staffing formula that looks avoids excess capacity by choosing the minimum number needed over the next 12 weeks. The result is shown below (only using 10 weeks for simplicity here but actually need over 200 weeks). Currently, I have to manually enter the selected staffing one number at a time since I can't calculate the next number in the sequence until the correct number is staffed in the previous week. This is manual and prevent me from doing sensitivity analysis that dynamically adjusts staffing. I'm look for a solution to help automate it. I tried created a reference that counted up from 1 to 10 and would calculate until that week was reached and store the number (e.g. week 1 calculates 4 and is stored but all other weeks are ignored, then week 2 is calculated and stored and so on) but was unsuccessful. Any tips for how to achieve this or what to google for help? I haven't been able to find anything so far but may not being describing the problem effectively for a search.

 

[myall_blues]

It sounds like a job for VBA but it's not clear at all how the calculation is done and therefore how and what it needs to store. Can you provide an example run-through of two or three weeks of calculations. For example, it's not clear how it goes from 4 to 13 between weeks 1 and 2, or 8 to 11 between weeks 2 and 3, or why week 2 has a 1 at iteration 3.

 

[atfoley16]

Thanks for the reply. I have expanded on the calculations below but had to use some new numbers so please refer to this rather than the original post.

Step 1. The calculation starts with 0 staff being added
Step 2. The formula in I22 calculates the minimum additional staff that need to start week of 6/10 to to meet the demand the week of 7/1. J22 then calculates the minimum needed for the demand the week of 7/8… and so on
Step 3. The minimum of the calculations in step 2 is the number that ultimately needs to be hired 6/10 to minimize excess capacity over future weeks. This is often the first number of the series but not always.
Step 4. The staff calculated in step 3 need to be input as additional staff being added. 6 staff the week of 6/10.
Step 5. Once step 4 is complete, there is a new series of staffing needs by week shown as Iteration 2. Repeat steps 2 - 4 but start the minimum calculation in the week of 6/17, the week you are ultimately solving for the new staff needed.

Note: The key is that you can't solve for the 6/17 staffing adds unless the correct staff have been added the week of 6/10, otherwise the number needed will be inflated to try and make up for underhiring 6/10, and no staff have been added after 6/17, otherwise your need 6/17 will be artificially low.

 

[atfoley16]

I'll add that I tried linking the staff added section to row 48 but it blew up. I need it to start at 0 then take the value from I48, recalculate without changing the value in I48 and then take the value from J48 and so on.

 

[hagia_sofia]

Is it always true that the MIN is on the diagonal? If yes, you could try to put this formula into I49:

=LET(array,I37:R46,TRANSPOSE(BYROW(--(array=TRANSPOSE(array))*(array),LAMBDA(a,SUM(a)))))

 

[hagia_sofia]

Here is an alternate formula to test - put this formula into I49:

=LET(array,I37:R46,TRANSPOSE(BYROW(IFERROR(CHOOSEROWS(DROP(LET(seq_1,(SEQUENCE(COLUMNS(array),,0)),REDUCE("",seq_1,LAMBDA(a,b,VSTACK(a,DROP(array,,b))))),1),SEQUENCE(COLUMNS(array),,1,COLUMNS(array)+1)),""),LAMBDA(a,MIN(a)))))

 

[atfoley16]

Here is an alternate formula to test - put this formula into I49:

=LET(array,I37:R46,TRANSPOSE(BYROW(IFERROR(CHOOSEROWS(DROP(LET(seq_1,(SEQUENCE(COLUMNS(array),,0)),REDUCE("",seq_1,LAMBDA(a,b,VSTACK(a,DROP(array,,b))))),1),SEQUENCE(COLUMNS(array),,1,COLUMNS(array)+1)),""),LAMBDA(a,MIN(a)))))

Thanks, I'll give it a try and let you know if it works.

 

[atfoley16]

Here is an alternate formula to test - put this formula into I49:

=LET(array,I37:R46,TRANSPOSE(BYROW(IFERROR(CHOOSEROWS(DROP(LET(seq_1,(SEQUENCE(COLUMNS(array),,0)),REDUCE("",seq_1,LAMBDA(a,b,VSTACK(a,DROP(array,,b))))),1),SEQUENCE(COLUMNS(array),,1,COLUMNS(array)+1)),""),LAMBDA(a,MIN(a)))))

I tried both formulas. The second worked for calculating the minimum value from the array, although it didn't like when I tried to apply it to a very large data set; however, it still didn't iterate the way I'm hoping for. For example, when I dropped it in the actual data set, the first three numbers are 6, 15, and 20. Once I enter the 6 into the added staff line, the 15 becomes an 8 due to the additional staff added. I then need to manually input the 8 and so on. I'm looking for a way to not have to enter those manually (i.e. calculate the formula that results in 15 only after 6 has been calculated so that the result for the second number is 8 instead of 15.

 

[hagia_sofia]

I see. Could you please describe the mechanism in a more detailed way? E.g. how are numbes in row constructed and what is the relation between row 8 - row 19 to row 22 - row 34? E.g. L8/L2 is 6,8 and not 6. What is to be added manually and what is to be calculated?

 

[myall_blues]

There's rather a lot here that doesn't make sense. For example:
- The formula for the selected staffing as shown in row 49 is =MIN(I37,U37). Since U37 is blank, the minmum is zero, not six as shown in row 48.
- What is the formula in I37?
- How is there a 1 in I38 and I39? There is no corresponding calculation in row 23 or 24.