Finding all combinations to a summed value

[cerberus1845]

I'm looking for some help on the following problem I have. I have an excel workbook which has 2 columns - one column is the description and the other column is a decimal value. I then have a series of summed values (totals) that i want to work out what combinations of values in the two columns could result in the total.

I've found the following custom function approach - which gets me somewhere to where i want to be: Find all combinations of numbers that equal a given sum in Excel

This produces the output of the combinations - but the output isn't in the original decimal format (it drops the 2 decimal places) and in an ideal world I would like either the descriptions returned that correspond to the values or the numbers returned as a decimal (or both i.e. descriptions and their decimal values which equal the target cell) - but this is a bit out of my skill set and I'm hoping someone on here might be able to help me please?

 

[jkpieterse]

One trick you could use is to use a separate area in which you multiply all amounts by 100, use that function and then divide its results by 100.

 

[hagia_sofia]

Hello, any chance you have access to Excel 365?

 

[cerberus1845]

Hello, any chance you have access to Excel 365?

yes i do!

 

[hagia_sofia]

yes i do!

If it is the case, test this (adjust ranges):

=LET(
numbers,A1:A10,
total,B1,
combinations,TEXTAFTER(DROP(REDUCE("",TOCOL(numbers),LAMBDA(a,b,VSTACK(a,a&" | "&b))),1)," | ",1),
sum,DROP(REDUCE(0,TOCOL(numbers),LAMBDA(a,b,VSTACK(a,a+b))),1),
FILTER(combinations,sum=total))

It should return a list of numbers that add to the total; if it works, it might be further elaborated to identify which numbers and descriptions are relevant.

 

[cerberus1845]

hi - y

If it is the case, test this (adjust ranges):

=LET(
numbers,A1:A10,
total,B1,
combinations,TEXTAFTER(DROP(REDUCE("",TOCOL(numbers),LAMBDA(a,b,VSTACK(a,a&" | "&b))),1)," | ",1),
sum,DROP(REDUCE(0,TOCOL(numbers),LAMBDA(a,b,VSTACK(a,a+b))),1),
FILTER(combinations,sum=total))

It should return a list of numbers that add to the total; if it works, it might be further elaborated to identify which numbers and descriptions are relevant.

yes this works in terms of values!!!

 

[cerberus1845]

If it is the case, test this (adjust ranges):

=LET(
numbers,A1:A10,
total,B1,
combinations,TEXTAFTER(DROP(REDUCE("",TOCOL(numbers),LAMBDA(a,b,VSTACK(a,a&" | "&b))),1)," | ",1),
sum,DROP(REDUCE(0,TOCOL(numbers),LAMBDA(a,b,VSTACK(a,a+b))),1),
FILTER(combinations,sum=total))

It should return a list of numbers that add to the total; if it works, it might be further elaborated to identify which numbers and descriptions are relevant.

actually when I've expanded the table/List i receive a #NUM! error (it was working for a smaller set of data 10 rows - I've expanded this to 55 and receive this error) - I've tested incrementally and it seems to work up until 20 items and then errors - any ideas how to resolve?

 

[hagia_sofia]

Ah, indeed, there are too many combinations. I guess it would be better to test what @jkpieterse proposed?

 

[cerberus1845]

Ah, indeed, there are too many combinations. I guess it would be better to test what @jkpieterse proposed?

that solution didn't really make sense? - couldn't work out how it would apply in this scenario?

it's a pity there is a limitation on it as what you proposed worked really well (out of curiosity - how would you adapt it to include the descriptions to the left of the values)

 

[cerberus1845]

One trick you could use is to use a separate area in which you multiply all amounts by 100, use that function and then divide its results by 100.

thanks for your reply.. how would this actually work in terms of changes i would need to make?