Jump to content

Excel query


Alan Medic

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Latest Discussions

Home
Events
Sign In

Sign In



Or sign in with one of these services

Search
×
    Search In
×
×
  • Create New...