Accounting Formula

[Bridge1969]

Is there a formula out there that would look at a range of numbers and return what numbers would sum up to an amount. For instance.

101.5
600.32 X
100.75
384.00 X
991.00
600.00 X
725.11

Looking for what numbers above would = 1584.32

Any suggestions?

[ragulduy]

You could use a "brute force method" along the lines of:
in column A put your numbers, in column B put =randbetween(0,1) in C1 put = sumif(B1:B7,1,A1:A7)
then recalculate unril you reach your target.

[newbydeveloper]

Hi Bridge,

I'm not sure if there's a formula that can do this, but it can be done using the solver application.

In column B, put 1 in cells B1:B7 and in column C put =A1*B1 in cell C1 and copy down. In cell C8 enter =SUM(C1:C7)

Got to Data->Solver at the top and set objective as $C$8 to Value of 1584.32 by changing variable cells $B$1:$B$7

subject to constraints of
$B$1:$B$7 <=1
$B$1:$B$7 = integer
$B$1:$B$7 >=0

and solve for answer. Hope this helps.

[ragulduy]

@newbydeveloper...

are you sure? I would have thought the solver would find it difficult to arrive at an answer as there is no logic behind the function it is trying to solve.

[newbydeveloper]

@Yudlugar, yes it works

Solver sets column B's values to be either 0 or 1 and creates a solution for the sum of column C (Ax*Bx) to equal 1584.32, give it a try.

[ragulduy]

So it does !

For some reason I though the solver used a newton-raphson type thing so you had to be trying to solve an actual function (like y=3x^2) rather than a random problem like this one.

[Alf]

Instead of

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

Alf