Using Partial Numbers to Calculate Sum

[vlooku]

Example of my problem:
A1- 85,331.93
A2- 508,007.30
A3- 515,677.05
A4- 175,311.71
A5- 535,316.91
A6- 147,687.14
A7- 141,606.92

I need to use any combination of these numbers (including partials meaning I can use 60,000 from A1 and save the rest for later) to equal a a set of sums. For example, I need to break down the above numbers to equal 1,000,000 another 1,000,000 and 190,000, while using the smallest amount of different cells.

Try to explain a little more. I would like to use 3 different cells to equal one of the million, 2 to equal the other million and 2 to equal the 190,000. I know in this case it may not be mathematically possible (I haven't checked for this range of numbers), but I need to be able to do his on a regular basis. If anyone can help, thank you!

 

[number kruncher]

I used the following method..

Sheet4

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Helvetica,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 88px"><COL style="WIDTH: 19px"><COL style="WIDTH: 91px"><COL style="WIDTH: 19px"><COL style="WIDTH: 82px"><COL style="WIDTH: 19px"><COL style="WIDTH: 71px"><COL style="WIDTH: 19px"><COL style="WIDTH: 91px"><COL style="WIDTH: 19px"><COL style="WIDTH: 82px"><COL style="WIDTH: 19px"><COL style="WIDTH: 80px"><COL style="WIDTH: 19px"><COL style="WIDTH: 91px"><COL style="WIDTH: 19px"><COL style="WIDTH: 82px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD></TR><TR style="HEIGHT: 51px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">Original Data</TD><TD></TD><TD style="TEXT-ALIGN: right">Original Data Sorted Max - Min</TD><TD></TD><TD style="TEXT-ALIGN: right">Use Largest Numbers first</TD><TD></TD><TD style="TEXT-ALIGN: right">Remainder 1</TD><TD></TD><TD style="TEXT-ALIGN: right">Remainder 1 Sorted Max - Min</TD><TD></TD><TD style="TEXT-ALIGN: right">Use Next Biggest Numbers</TD><TD></TD><TD style="TEXT-ALIGN: right">Remainder 2</TD><TD></TD><TD style="TEXT-ALIGN: right">Remainder 2 Sorted Max - Min</TD><TD></TD><TD style="TEXT-ALIGN: right">Use Next Biggest Numbers</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">85,331.93</TD><TD></TD><TD style="TEXT-ALIGN: right">535,316.91</TD><TD></TD><TD style="TEXT-ALIGN: right">535,316.91</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">508,007.30</TD><TD></TD><TD style="TEXT-ALIGN: right">508,007.30</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">57,945.00</TD><TD></TD><TD style="TEXT-ALIGN: right">57,945.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">508,007.30</TD><TD></TD><TD style="TEXT-ALIGN: right">515,677.05</TD><TD></TD><TD style="TEXT-ALIGN: right">464,683.09</TD><TD></TD><TD style="TEXT-ALIGN: right">50,993.96</TD><TD></TD><TD style="TEXT-ALIGN: right">175,311.71</TD><TD></TD><TD style="TEXT-ALIGN: right">175,311.71</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">50,993.96</TD><TD></TD><TD style="TEXT-ALIGN: right">50,993.96</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">515,677.05</TD><TD></TD><TD style="TEXT-ALIGN: right">508,007.30</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">508,007.30</TD><TD></TD><TD style="TEXT-ALIGN: right">147,687.14</TD><TD></TD><TD style="TEXT-ALIGN: right">147,687.14</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">175,311.71</TD><TD></TD><TD style="TEXT-ALIGN: right">175,311.71</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">175,311.71</TD><TD></TD><TD style="TEXT-ALIGN: right">141,606.92</TD><TD></TD><TD style="TEXT-ALIGN: right">141,606.92</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">535,316.91</TD><TD></TD><TD style="TEXT-ALIGN: right">147,687.14</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">147,687.14</TD><TD></TD><TD style="TEXT-ALIGN: right">85,331.93</TD><TD></TD><TD style="TEXT-ALIGN: right">27,386.93</TD><TD></TD><TD style="TEXT-ALIGN: right">57,945.00</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">147,687.14</TD><TD></TD><TD style="TEXT-ALIGN: right">141,606.92</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">141,606.92</TD><TD></TD><TD style="TEXT-ALIGN: right">50,993.96</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">50,993.96</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">141,606.92</TD><TD></TD><TD style="TEXT-ALIGN: right">85,331.93</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">85,331.93</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD><TD></TD><TD style="TEXT-ALIGN: right">0.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD></TD><TD></TD><TD>Target</TD><TD></TD><TD style="TEXT-ALIGN: right">1,000,000.00</TD><TD></TD><TD></TD><TD></TD><TD>Target</TD><TD></TD><TD style="TEXT-ALIGN: right">1,000,000.00</TD><TD></TD><TD></TD><TD></TD><TD>Target</TD><TD></TD><TD style="TEXT-ALIGN: right">190,000.00</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2</TD><TD>=LARGE(A:A,ROW()-1)</TD></TR><TR><TD>E2</TD><TD>=IF(C2<E$12,C2,E$12)< TD></TD><TR><TD>G2</TD><TD>=C2-E2</TD></TR><TR><TD>I2</TD><TD>=LARGE(G:G,ROW()-1)</TD></TR><TR><TD>K2</TD><TD>=IF(I2<K$12,I2,K$12)< TD></TD><TR><TD>M2</TD><TD>=I2-K2</TD></TR><TR><TD>O2</TD><TD>=LARGE(M:M,ROW()-1)</TD></TR><TR><TD>Q2</TD><TD>=IF(O2<Q$12,O2,Q$12)< TD></TD><TR><TD>C3</TD><TD>=LARGE(A:A,ROW()-1)</TD></TR><TR><TD>E3</TD><TD>=IF((C3+SUM(E$2:E2))<E$12,C3,E$12-SUM(E$2:E2))</TD></TR><TR><TD>G3</TD><TD>=C3-E3</TD></TR><TR><TD>I3</TD><TD>=LARGE(G:G,ROW()-1)</TD></TR><TR><TD>K3</TD><TD>=IF((I3+SUM(K$2:K2))<K$12,I3,K$12-SUM(K$2:K2))</TD></TR><TR><TD>M3</TD><TD>=I3-K3</TD></TR><TR><TD>O3</TD><TD>=LARGE(M:M,ROW()-1)</TD></TR><TR><TD>Q3</TD><TD>=IF((O3+SUM(Q$2:Q2))<Q$12,O3,Q$12-SUM(Q$2:Q2))</TD></TR><TR><TD>C4</TD><TD>=LARGE(A:A,ROW()-1)</TD></TR><TR><TD>E4</TD><TD>=IF((C4+SUM(E$2:E3))<E$12,C4,E$12-SUM(E$2:E3))</TD></TR><TR><TD>G4</TD><TD>=C4-E4</TD></TR><TR><TD>I4</TD><TD>=LARGE(G:G,ROW()-1)</TD></TR><TR><TD>K4</TD><TD>=IF((I4+SUM(K$2:K3))<K$12,I4,K$12-SUM(K$2:K3))</TD></TR><TR><TD>M4</TD><TD>=I4-K4</TD></TR><TR><TD>O4</TD><TD>=LARGE(M:M,ROW()-1)</TD></TR><TR><TD>Q4</TD><TD>=IF((O4+SUM(Q$2:Q3))<Q$12,O4,Q$12-SUM(Q$2:Q3))</TD></TR><TR><TD>C5</TD><TD>=LARGE(A:A,ROW()-1)</TD></TR><TR><TD>E5</TD><TD>=IF((C5+SUM(E$2:E4))<E$12,C5,E$12-SUM(E$2:E4))</TD></TR><TR><TD>G5</TD><TD>=C5-E5</TD></TR><TR><TD>I5</TD><TD>=LARGE(G:G,ROW()-1)</TD></TR><TR><TD>K5</TD><TD>=IF((I5+SUM(K$2:K4))<K$12,I5,K$12-SUM(K$2:K4))</TD></TR><TR><TD>M5</TD><TD>=I5-K5</TD></TR><TR><TD>O5</TD><TD>=LARGE(M:M,ROW()-1)</TD></TR><TR><TD>Q5</TD><TD>=IF((O5+SUM(Q$2:Q4))<Q$12,O5,Q$12-SUM(Q$2:Q4))</TD></TR><TR><TD>C6</TD><TD>=LARGE(A:A,ROW()-1)</TD></TR><TR><TD>E6</TD><TD>=IF((C6+SUM(E$2:E5))<E$12,C6,E$12-SUM(E$2:E5))</TD></TR><TR><TD>G6</TD><TD>=C6-E6</TD></TR><TR><TD>I6</TD><TD>=LARGE(G:G,ROW()-1)</TD></TR><TR><TD>K6</TD><TD>=IF((I6+SUM(K$2:K5))<K$12,I6,K$12-SUM(K$2:K5))</TD></TR><TR><TD>M6</TD><TD>=I6-K6</TD></TR><TR><TD>O6</TD><TD>=LARGE(M:M,ROW()-1)</TD></TR><TR><TD>Q6</TD><TD>=IF((O6+SUM(Q$2:Q5))<Q$12,O6,Q$12-SUM(Q$2:Q5))</TD></TR><TR><TD>C7</TD><TD>=LARGE(A:A,ROW()-1)</TD></TR><TR><TD>E7</TD><TD>=IF((C7+SUM(E$2:E6))<E$12,C7,E$12-SUM(E$2:E6))</TD></TR><TR><TD>G7</TD><TD>=C7-E7</TD></TR><TR><TD>I7</TD><TD>=LARGE(G:G,ROW()-1)</TD></TR><TR><TD>K7</TD><TD>=IF((I7+SUM(K$2:K6))<K$12,I7,K$12-SUM(K$2:K6))</TD></TR><TR><TD>M7</TD><TD>=I7-K7</TD></TR><TR><TD>O7</TD><TD>=LARGE(M:M,ROW()-1)</TD></TR><TR><TD>Q7</TD><TD>=IF((O7+SUM(Q$2:Q6))<Q$12,O7,Q$12-SUM(Q$2:Q6))</TD></TR><TR><TD>C8</TD><TD>=LARGE(A:A,ROW()-1)</TD></TR><TR><TD>E8</TD><TD>=IF((C8+SUM(E$2:E7))<E$12,C8,E$12-SUM(E$2:E7))</TD></TR><TR><TD>G8</TD><TD>=C8-E8</TD></TR><TR><TD>I8</TD><TD>=LARGE(G:G,ROW()-1)</TD></TR><TR><TD>K8</TD><TD>=IF((I8+SUM(K$2:K7))<K$12,I8,K$12-SUM(K$2:K7))</TD></TR><TR><TD>M8</TD><TD>=I8-K8</TD></TR><TR><TD>O8</TD><TD>=LARGE(M:M,ROW()-1)</TD></TR><TR><TD>Q8</TD><TD>=IF((O8+SUM(Q$2:Q7))<Q$12,O8,Q$12-SUM(Q$2:Q7))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> Excel Jeanie HTML 4

HTH

P.S. Cell E3 on is =IF((C3+SUM(E$2:E2))<E$12,C3,E$12-SUM(E$2:E2)) (jeanie truncated formula)

 

[vlooku]

I have two questions on here...the C2 cell with formula =IF(C2 ....this may sound dumb, but what am I missing here for the rest of that formula?

2nd question...is there a way to put some sort of limit on these, say for the lots of 1,000,000 you want it <= 4 of those combined cells?

 

[number kruncher]

=IF(C2< E$12,C2,E$12)
As it uses the largest numbers first, then a limit on cells is linked to the value of those cells!!
HTH

Thanks shg

 

[shg]

nk, put a space after < to keep it from being interpreted as an html tag.