Using Partial Numbers to Calculate Sum

vlooku

New Member
Joined
Mar 1, 2011
Messages
7
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
Joined
Dec 7, 2010
Messages
425
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
Joined
Mar 1, 2011
Messages
7
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
Joined
Dec 7, 2010
Messages
425
=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
Joined
May 7, 2008
Messages
21,717
Office Version
2010
Platform
Windows
nk, put a space after < to keep it from being interpreted as an html tag.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top