# Using Partial Numbers to Calculate Sum

#### vlooku

##### New Member
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

##### Active Member
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

##### New Member
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

##### Active Member
=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

Last edited:

#### shg

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

1,082,276
Messages
5,364,195
Members
400,786
Latest member
ismi88

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...