Microsoft Excel Problem Solving Question?

Question

I have two excel columns (A and B) with different numbers (each column has 135 numbers). Two sums (SUM(A1:A135) and SUM(B1:B135)) of each column are different. I need to find the minimum sum of the combinations from two columns. The combination includes numbers with every indices from 1 to 135 and numbers with only one index, for example, if it includes A3, then it exclude B3. There is condition that the sum of chosen numbers from B column has to be more than or equal to that of A column.

Answer 1

I'm not sure if I have clearly understood the question, but if what I understand is correct, you have some complicated math on your hands.

I would use an Excel macro to handle this. This is assuming a somewhat random mix of numbers in rows 1..135.

Here's an example: (just off the top of my head, so I don't know if this would work)

Sub FindMinimumSum ()

sumA=0
sumB=0
for i=1 to 135
a= worksheets("Sheet1").cells(i,"A").value
b= worksheets("Sheet1").cells(i,"B").value
if a>=b then sumB=sumB+B else sumA=sumA+A
next i
TOTALSUM=sumA +sumB

'These next three lines will WRITE data to your spreadsheet!!!
worksheets("Sheet1").cells(1,"C").value=SumA
worksheets("Sheet1").cells(2,"C").value=SumB
worksheets("Sheet1").cells(3,"C").value=TOTALSUM

End Sub

Just copy and paste that macro into your Excel Visual Basic code, and then run it. NOTE: If you have data in Column "C", rows 1,2, & 3, then you will want to change the last three lines to put the numbers where you want them (in empty cells somewhere). You might also like to backup your file--Once a macro is run, Excel will not UNDO it!

Answer 2

Why not add a third column C with the function:

=if(A1 .
.
.
=if(A135

and then sum that column?

Answer 3

Have you tried copy - paste { values / transpose } ? It normally works unless you are selecting a range of different size to paste the output. Alternatively, you can create a macro that reads values one by one and pastes them out in a column.

Answer 4

use SumIF()
it allows you to set conditions on whether or not to include a number in a range.
there is help available in excel for this function, but if you use the function tool - it steps you through anyway