Reverse finding matches to a number

[arnaudmanir]

Ok, this is a long shot but I was wondering if there is an excel function that would list cell values that match a certain number if added up together.
For instance, if my number is $4500, it would return 3 cells that have these values: $1500, $1000 and $2000
Is that possible?

[martindwilson]

you can use solver to find a single solution but if you had say
$1500, $1000 , $2000 ,$400 ,$600,$3500 it would find one of
$400 ,$600,$3500
$1500, $1000 , $2000
$2000 ,$400 ,$600,$1500
not all of them

[arnaudmanir]

Unfortunately I'm using Excel 2007 at work and therefore Solver is inaccessible. But thanks a lot.

[azumi]

Try with format that you must follow like this (separate with commas) :
$10, $100, $1000 --> so Excel can calculate how many text strings in there

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

that formula count 3 as a result

Regards
Azumi

[azumi]

or

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+1

Azumi

[martindwilson]

Unfortunately I'm using Excel 2007 at work and therefore Solver is inaccessible.????? why is that then

[azumi]

Or just take a look to my files

Azumi

[martindwilson]

azumi i think you are missing the point

[:) Sixthsense :)]

Or

In A1 Cell
4500

In B1 Cell

=CEILING(A1*25%,500)

In C1 Cell

=CEILING(A1*20%,500)

In D1 Cell

=A1-SUM(B1:C1)

Just for fun lol