Excel query

[Alan Medic]

Wonder if anyone can tell me if there is a way of doing this? I have a series of values (which could be either negative or positive) that will add up to Zero. However I don?t know which are positive or which are negative.

Is there a way of calculating how these numbers being one way or the other will add up to Zero and if there is more than one way of doing it?

I?m being lazy here as I should be able to deduce which is a negative or a positive and am just looking for a quicker option.

[Jeremy]

Are you talking about methodically assigning +ve/-ve signs to each number until you reach a solution?

Easy to code in VBA.. but if your data set is large, you might find it surpisingly slow to compute.

[*Bob*]

Fun times.

[????]

Is this a new full proof betting system Mr Medic?

[Mick Mac]

fool proof?

[red devil]

What happens if there's more than one permutation?...

[ed_pete]

Assuming none of the values are themselves zero, then there are (2 x (2^(n-1))-2 permutations of positive and negative numbers. e.g. 5 numbers = 30 permutations.

But in short there is no easy way to do this in Excel without resorting to VBA as Jeremy says.

[Jeremy]

It's just 2^n, isn't it? Soon becomes intractable with a large set.

[miga]

ed_pete: (2 x (2^(n-1)) a.k.a 2^n, and not sure where - 2 comes from, are you maybe forgetting all/no - or all/no +?

With no further restrictions, reckon Jeremy's right.

There are some vaguely similar counting/summing problems which you can do in order of n steps, but these tend to have a kind of "ordering" implicit in them, such as finding the point in a set which gives two parts, the sums of each respective part giving minimal absolute difference.

[ed_pete]

Surely all positive or all negative can't sum to zero ?

[Alan Medic]

Jeremy Wrote:

-------------------------------------------------------

> Are you talking about methodically assigning

> +ve/-ve signs to each number until you reach a

> solution?

>

> Easy to code in VBA.. but if your data set is

> large, you might find it surpisingly slow to

> compute.

We're talking about less than 40 values which already have a plus or minus assigned to them as a result of a reconciliation. However these add up to over ?4,000 in value. By a process of deduction, a plus or a minus can be reversed depending of the reason it is what it is in the first place. With the correct alterations the sum of the numbers will be zero.

May be easy to code in VBA if you know how.

[Alan Medic]

ed_pete Wrote:

-------------------------------------------------------

> Surely all positive or all negative can't sum to

> zero ?

Right. But they are not all one or the other.

[Alan Medic]

red devil Wrote:

-------------------------------------------------------

> What happens if there's more than one

> permutation?...

I'll have to think.

[Alan Medic]

???? Wrote:

-------------------------------------------------------

> Is this a new full proof betting system Mr Medic?

Nah. Only 40%.

[Jeremy]

You can do it using Excel "Solver"

Example here - very similar to what you need to do

http://www.k2e.com/tech-update/tips/147-using-excel-to-identify-entries-that-add-to-a-specific-value

[miga]

ed_pete Wrote:

-------------------------------------------------------

> Surely all positive or all negative can't sum to

> zero ?

Yep, fair enough.

[red devil]

*Waves at El Pibe*...his kinda thread

[Jah Lush]

I have absolutely no idea what you're on about.

As a gambling man is it some new way for you to try and weigh up the pros and cons of the odds at stake?

[Alan Medic]

Jeremy Wrote:

-------------------------------------------------------

> You can do it using Excel "Solver"

>

> Example here - very similar to what you need to

> do

> http://www.k2e.com/tech-update/tips/147-using-exce

> l-to-identify-entries-that-add-to-a-specific-value

Thanks Jeremy. Will try that after eating my rice cakes with peanut butter and banana.

[red devil]

Going to be windy tonight...

[Jeremy]

It will take a bloody long time though. With 40 numbers, that is one trillion combinations to test...

[Help-Ma-Boab]

Ha Ha RD....same thoughts. straight out the EP guide to breaking the ice at a party this one!

red devil Wrote:

-------------------------------------------------------

> *Waves at El Pibe*...his kinda thread

[Mick Mac]

Help-Ma-Boab Wrote:

-------------------------------------------------------

> Ha Ha RD....same thoughts. straight out the EP

> guide to breaking the ice at a party this one!

>

:)

[????]

Someone let him know his ears must be burning :)

[HopOne]

This is called the subset sum problem and will take a lot of number crunching. If you can break the problem down into a number of sums that are all positive or all negative then it will be much quicker to solve.