who can write Excel Formulae
 

I have an idea for an Excel Formula that I think could be a big help to any
Excel user that would need to do a reconciliation of any type.

My idea is to take a range of cells and then in the background run a
summation for every combination of cells within that range. The second part
of the formula would be a determined number, the out desired output of the
formula would be any combinations of the range that could be used to derive
the determined number.

The formula would look something like:
=possible(a1:a100,desired#) and the output could be the list of cells that
make up "desired#".

Does anyone know either how to write a formula that can do this or who one
can contact at Microsoft to request such a formula? As I said earlier, this
would be a great help to many in the accounting and bookkeeping field when
trying to reconcile an account.

who can write Excel Formulae
 

Craig Wargel <Craig wrote...
....
My idea is to take a range of cells and then in the background run
a summation for every combination of cells within that range. . . .

You're unfamiliar with the math. The number of nontrivial combinations
of N numbers is 2^N-1. Even if you had a computer capable of
performing 10^10 calculations per second, it'd take several times the
age of the universe (according to cosmologists rather than Christian
fundamentalists) to calculate the sums of all combinations of even 100
numbers.

. . . The second part of the formula would be a determined number,
the out desired output of the formula would be any combinations of
the range that could be used to derive the determined number.
....

Read the entire linked thread.

http://groups.google.com/group/micro...e1064b7a215a0c

Does anyone know either how to write a formula that can do this or
who one can contact at Microsoft to request such a formula? As I
said earlier, this would be a great help to many in the accounting
and bookkeeping field when trying to reconcile an account.

Read the linked thread above. What you want has been requested MANY
times in the past, and ad hoc approaches are all that are on offer.
Macros are much more practical than functions. The lack of such a
feature is due to the gross impracticality of calculating the sums of
all combinations of even modest cardinality sets of numbers.

who can write Excel Formulae
 

He needs a quantum computer.
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Harlan Grove" wrote:

Craig Wargel <Craig wrote...
....
My idea is to take a range of cells and then in the background run
a summation for every combination of cells within that range. . . .

You're unfamiliar with the math. The number of nontrivial combinations
of N numbers is 2^N-1. Even if you had a computer capable of
performing 10^10 calculations per second, it'd take several times the
age of the universe (according to cosmologists rather than Christian
fundamentalists) to calculate the sums of all combinations of even 100
numbers.

. . . The second part of the formula would be a determined number,
the out desired output of the formula would be any combinations of
the range that could be used to derive the determined number.
....

Read the entire linked thread.

http://groups.google.com/group/micro...e1064b7a215a0c

Does anyone know either how to write a formula that can do this or
who one can contact at Microsoft to request such a formula? As I
said earlier, this would be a great help to many in the accounting
and bookkeeping field when trying to reconcile an account.

Read the linked thread above. What you want has been requested MANY
times in the past, and ad hoc approaches are all that are on offer.
Macros are much more practical than functions. The lack of such a
feature is due to the gross impracticality of calculating the sums of
all combinations of even modest cardinality sets of numbers.

who can write Excel Formulae
 

Well, I have had a bit of statstics myself so I do understand the complexity
of the numbers, but I guess I've underestimated today's processors. I would
assume that once the numbers have added to more than the desired number that
that string of calculations can be canceled. Obviously, the program would
not have to calculate every string of cells.
Once A1 plus A2 exceeds the desired number...move on. This would at least
cut the calculation down to 1/2 eternity, right?

"Dave F" wrote:

He needs a quantum computer.
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Harlan Grove" wrote:

Craig Wargel <Craig wrote...
....
My idea is to take a range of cells and then in the background run
a summation for every combination of cells within that range. . . .

You're unfamiliar with the math. The number of nontrivial combinations
of N numbers is 2^N-1. Even if you had a computer capable of
performing 10^10 calculations per second, it'd take several times the
age of the universe (according to cosmologists rather than Christian
fundamentalists) to calculate the sums of all combinations of even 100
numbers.

. . . The second part of the formula would be a determined number,
the out desired output of the formula would be any combinations of
the range that could be used to derive the determined number.
....

Read the entire linked thread.

http://groups.google.com/group/micro...e1064b7a215a0c

Does anyone know either how to write a formula that can do this or
who one can contact at Microsoft to request such a formula? As I
said earlier, this would be a great help to many in the accounting
and bookkeeping field when trying to reconcile an account.

Read the linked thread above. What you want has been requested MANY
times in the past, and ad hoc approaches are all that are on offer.
Macros are much more practical than functions. The lack of such a
feature is due to the gross impracticality of calculating the sums of
all combinations of even modest cardinality sets of numbers.

who can write Excel Formulae
 

If you look though the history in this forum, You are defiuniately not the
first who needs this. Unfortunately it is not practical to make a simple
formula to do this.
there are more than 10^30 combinations for 100 inputs
(more than a 1000 for 10 inputs.)
There are methods of simplifying the process, that allows some combinations
to not be excessive, This is when the number to be resolved is small or very
large compared to the numbers in the list

"Craig Wargel" wrote:

I have an idea for an Excel Formula that I think could be a big help to any
Excel user that would need to do a reconciliation of any type.

My idea is to take a range of cells and then in the background run a
summation for every combination of cells within that range. The second part
of the formula would be a determined number, the out desired output of the
formula would be any combinations of the range that could be used to derive
the determined number.

The formula would look something like:
=possible(a1:a100,desired#) and the output could be the list of cells that
make up "desired#".

Does anyone know either how to write a formula that can do this or who one
can contact at Microsoft to request such a formula? As I said earlier, this
would be a great help to many in the accounting and bookkeeping field when
trying to reconcile an account.

who can write Excel Formulae
 

The 10^10 figure Harlan cites is 10 billion calculations per second, a
processing speed several times the fastest processors available.

2^99 (i.e., the combinations of 100 numbers) is 6.34*10^29.

So, you're looking at a calculation that could take tens of billions of
years if not more.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Craig Wargel" wrote:

Well, I have had a bit of statstics myself so I do understand the complexity
of the numbers, but I guess I've underestimated today's processors. I would
assume that once the numbers have added to more than the desired number that
that string of calculations can be canceled. Obviously, the program would
not have to calculate every string of cells.
Once A1 plus A2 exceeds the desired number...move on. This would at least
cut the calculation down to 1/2 eternity, right?

"Dave F" wrote:

He needs a quantum computer.
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Harlan Grove" wrote:

Craig Wargel <Craig wrote...
....
My idea is to take a range of cells and then in the background run
a summation for every combination of cells within that range. . . .

You're unfamiliar with the math. The number of nontrivial combinations
of N numbers is 2^N-1. Even if you had a computer capable of
performing 10^10 calculations per second, it'd take several times the
age of the universe (according to cosmologists rather than Christian
fundamentalists) to calculate the sums of all combinations of even 100
numbers.

. . . The second part of the formula would be a determined number,
the out desired output of the formula would be any combinations of
the range that could be used to derive the determined number.
....

Read the entire linked thread.

http://groups.google.com/group/micro...e1064b7a215a0c

Does anyone know either how to write a formula that can do this or
who one can contact at Microsoft to request such a formula? As I
said earlier, this would be a great help to many in the accounting
and bookkeeping field when trying to reconcile an account.

Read the linked thread above. What you want has been requested MANY
times in the past, and ad hoc approaches are all that are on offer.
Macros are much more practical than functions. The lack of such a
feature is due to the gross impracticality of calculating the sums of
all combinations of even modest cardinality sets of numbers.

who can write Excel Formulae
 

Craig Wargel wrote...
....
. . . but I guess I've underestimated today's processors. I would
assume that once the numbers have added to more than the desired
number that that string of calculations can be canceled. Obviously,
the program would not have to calculate every string of cells.
....

Look at my code in the linked article in my previous response. It does
that, but it's still VERY, VERY, VERY S**L**O**W. And *IF* you have
both positive and negative values in a range, such heuristics can
become a practical impossibility.

Even if all values were positive, if the sum sought (S) is just 2
orders of magnitude larger than the average value in the set of N,
you're still likely to need to check at least

SUMPRODUCT(COMBIN(N,{3;4;5;6;7}))

combinations (and that assumes moderate skewness for your set of
values). For N=100, that'd be 17,278,988,595 combinations. Your CPU/
FPU may be up to that, but it's unlikely you have the RAM to store
that many double precision values (8 bytes per, so a bit more that
131GB). If you have to rely on virtual memory/paging to store the
temporary results, your run time will be on the order of years. A
decided improvement over the age of the universe, but still not in the
realm of practicality.

who can write Excel Formulae
 

Dave F wrote...
....
2^99 (i.e., the combinations of 100 numbers) is 6.34*10^29.
....

That may be what 2^99 is, but the number of nontrivial combinations of
100 numbers (i.e., all except the empty set) 2^100-1, i.e., (2^100)-1,
which is 1,267,650,600,228,229,401,496,703,205,375 (10^30). But I'll
go partway an toss out the combinations of 1 value too, reducing this
to 2^100-101, or just 1,267,650,600,228,229,401,496,703,205,275.

who can write Excel Formulae
 

"Harlan Grove" wrote...
....
SUMPRODUCT(COMBIN(N,{3;4;5;6;7}))

combinations (and that assumes moderate skewness for your set of
values). For N=100, that'd be 17,278,988,595 combinations. . . .

Nope, it's just 17,278,983,645. I had {2;3;4;5;6;7} in the original
formula.

who can write Excel Formulae
 

It's not that its such a bad idea, or that no one hasn't made the same
request before. As Dave and Harlan have pointed out - the results are simply
overwhelming - both to the computer trying to do the work and even if we had
a couple of Dave's Quantum Computers (maybe a Quanpac??) to get the work done
virtually instantaneously, the sheer number of possible results to sift
through would overwhen the user.

Sometimes our technology overwhelms us even sooner, in the computer world
things pile up rapidly. Consider the simple Excel worksheet:
Pre-2007, 255*65536 = 16,711,680 cells
put a single character into each of them and it requires 16MB of storage for
the data (not giving consideration to other overhead). An amout of storage
easily dealt with using most of today's computers with =256MB RAM and disk
storage measured in GBs.
Along comes Excel 2007:
65536*1048576 = 68,719,476,736 which is 64GB (at 1024=1K, 1024*1024 = 1M,
1024*1024*1024=1G) so most of today's home computers can't fill an Excel
2007 sheet with one alpha character per cell on a single sheet and hold the
whole thing in memory. Thank goodness for 500GB Hard drives!

"Craig Wargel" wrote:

I have an idea for an Excel Formula that I think could be a big help to any
Excel user that would need to do a reconciliation of any type.

My idea is to take a range of cells and then in the background run a
summation for every combination of cells within that range. The second part
of the formula would be a determined number, the out desired output of the
formula would be any combinations of the range that could be used to derive
the determined number.

The formula would look something like:
=possible(a1:a100,desired#) and the output could be the list of cells that
make up "desired#".

Does anyone know either how to write a formula that can do this or who one
can contact at Microsoft to request such a formula? As I said earlier, this
would be a great help to many in the accounting and bookkeeping field when
trying to reconcile an account.