Google Sheets Array Formula issue

Question

The goal of this formula is to take a starting balance and break it down using a sorted list of pre-determined values. The only catch is that the three largest values can only be used once while the values that are smaller can be used as many times as necessary to reduce the balance to zero in the shortest number of steps.

The sheet can be found here.

Column B contains the pre-determined values that the balance can be reduced by (sorted smallest to largest) Column C displays the starting balance (randomized with the check box) and then the running balance as values are removed. Column D should be a decreasing list of values that the running balance is decreased by.

The array formula is in D2 and looks like this; =ArrayFormula(IF(C2:C25>=0,IF(C2:C25>=$B$11,IF(COUNTIF(D$2:D2, $B$11)=0, $B$11,IF(C2:C25>=$B$10,IF(COUNTIF(D$2:D2, $B$10)=0, $B$10,IF(C2:C25>=$B$9, IF(COUNTIF(D$2:D2, $B$9)=0, $B$9, IF(C2:C25>0,INDEX($B$2:$B$8, MATCH(C2, $B$2:$B$8, 1),0))))))))))

You can use the check box to seed a new random number.

The formula is producing a result when the starting balance is greater than the largest value in the pre-determined list ($250) but returns FALSE with everything else.

Any help would be greatly appreciated.

Answer 1

Here you have an option:

As first step I changed your first formula =RANDBETWEEN(1,50000)/100 so you can get some decimals.

For the formula I nested two REDUCE functions, one for the first three values that need to be grabbed only once, and then for the other values:

=LET(bigger,REDUCE("Decrease by",SORT(B9:B11,1,0),LAMBDA(a,v,IF(v<=(C2-IFERROR(SUM(a))),VSTACK(a,v),a))),
REDUCE(bigger,SORT(B2:B8,1,0),LAMBDA(a,v,IF(v=0,{a;0},IF(v<=(C2-IFERROR(SUM(a))),VSTACK(a,INDEX(IF(SEQUENCE(ROUNDDOWN((C2-IFERROR(SUM(a)))/v)),v))),a)))))

First I check if the value is smaller than the cumulative sum of previous values, expressed like C2 - the sum of values in "a". If it's smaller, then it stacks that value.

With that list of the three bigger values, then I use REDUCE again, but first I check if v=0, so it stops at that time. Then it grabs each value and checks if it's smaller than the cumulative sum, but this time it stacks all the times that the cumulative sum can be divided by it. I do it with the SEQUENCE part and putting "v" instead of the actual sequence with INDEX(IF(SEQUENCE(ROUNDDOWN((C2-IFERROR(SUM(a)))/v)),v))) .

The last "a" is to skip the values that are smaller than the cumulative sums