Excel Functions

[stealthshaker]

Hi folks. Trying to get to grips with excel and have
become stuck, bamboozled, stumped!! I am trying to get
excel to tell me how many combinations of cells add up to
a given number. I.e. as a starter I have put the
following values in 4 different cells: 2,3,4,5 and want
excel to tell me how many combinations of these cells add
up to 7. I know the answer is 2 but I can't find a
formula that gives me this answer. I need a formula to
work this out as it's needed for a much larger group of
numbers. Any help would be greatly appreciated. I've
looked at the functions COMBIN and PERMUT to no avail.
Kind Regards,
Stealthshaker

 

[Paul Corrado]

Such a function does not exist in XL. To calculate that figure you would
need to calculate the prime factorization of your given number and then
analyze all of the numbers in your data set to see which are candidates for
inclusion in the result. A quick look at the google results didn't return
any decent solutions, though there were some things that could be combined
to develop a custom function to solve this. Also, from my perspective there
are three answers, without resorting to fractional amounts.

2+2+3 =7
2+5=7
3+4=7

PC

 

[Stealthshaker]

My appologies I didn't say that each cell may only be
used once in each calculation.
Regards,
Stealthshaker

 

[Glenn Schwandt]

With 7 in A1 and 2, 3, 4 & 5 in A2:A5, the following formula (array-entered)
will give you the answer you need:

=(SUM(--((A2:A5+TRANSPOSE(A2:A5))=A1))-SUM(--((A2:A5)*2=A1)))/2

However, it only works for combinations of 2 numbers. In other words, it
will result in 1 when you put 9 in A1 (4+5) when clearly (2+3+4) also equals
9.