Help needed on finding a formula for this...

[LBala]

I have a simple table with A, B, and C columns and D, E, F rows and numeric values in each cell of the table. I need to get one value from each column (only one value per column) that will result in the lowest possible number when adding those values up. There is one constraint: I can not get cells that belong to the same row.

On the example I am providing, the sum of the values 404, 970 and 604 totals 1978 which is the lowest possible result on that table provided that I'm selecting one cell per column and each selected cell belongs to an unique row.

Is there an excel formula for this problem? I’m looking for a formula to provide what the lowest possible number is and also need to identify the cells being selected.

Thanks in advance.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]984[/TD]
[TD]970[/TD]
[TD]1048[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]404[/TD]
[TD]1454[/TD]
[TD]396[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]663[/TD]
[TD]1797[/TD]
[TD]604[/TD]
[/TR]
</tbody>[/TABLE]

 

[healey21]

Welcome to the forum,

Try this out, based on your question.

Sheet2

■	A	B	C	D	E	F	G	H
■	■	■
■	■	$A$3
■	■	$A$2
■	■	$A$4

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 162px;"><col style="width: 90px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="align: left"]A[/TD]
[TD="align: left"]B[/TD]
[TD="align: left"]C[/TD]

[TD="align: left"]Lowest Values[/TD]
[TD="align: left"]Cell ID[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: left"]D[/TD]
[TD="align: left"]984[/TD]
[TD="align: left"]970[/TD]
[TD="align: left"]1048[/TD]

[TD="align: right"]404[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]404[/TD]
[TD="align: left"]1454[/TD]
[TD="align: left"]396[/TD]

[TD="align: right"]970[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: left"]F[/TD]
[TD="align: left"]663[/TD]
[TD="align: left"]1797[/TD]
[TD="align: left"]604[/TD]

[TD="align: right"]396[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G2 =SMALL(B2:B4,1) H2 =ADDRESS(MATCH(G2,$B$1:$B$4,-1)*1,1) G3 =SMALL(C1:C4,1) H3 =ADDRESS(MATCH(G3,$B$1:$B$4,-1)*1,1) G4 =SMALL(D1:D4,1) H4 =ADDRESS(MATCH(G4,$B$1:$B$4,-1)*1,1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[healey21]

Oops the formula I added for the cell address was wrong, please substitute with this

Sheet2

■	I
$B$3
$C$2
$D$3

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula I2 =CELL("address",INDEX(B1:B4,MATCH(G2,B1:B4,0))) I3 =CELL("address",INDEX(C2:C5,MATCH(G3,C2:C5,0))) I4 =CELL("address",INDEX(D3:D6,MATCH(G4,D3:D6,0))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[swaink]

Hi Guys

This is great work by healey21 nice one I like it

Just to take it a step further if you amend the cells in column I as below this will allow you to calculate the value you are expecting to see

All I have done is change the formulas to show row rather than address this gives you just the row numbers this allows us to find the second smallest value in each case if the results must be on different rows

I2=CELL("ROW",INDEX(B1:B4,MATCH(G2,B1:B4,0)))
I3=CELL("ROW",INDEX(C2:C5,MATCH(G3,C2:C5,0)))
I4=CELL("ROW",INDEX(D3:D6,MATCH(G4,D3:D6,0)))

J3=COUNTIF($I$2:$I$3,I3)
J4=COUNTIF($I$2:$I$4,I4)

F2=SMALL(B2:B4,1)
F3=IF(J3=1,SMALL(C2:C4,1),SMALL(C2:C4,2))
F4=IF(J4=1,SMALL(D2:D4,1),SMALL(D2:D4,2))

F6=SUM(F2:F5)

Hope this helps

Kevin

 

[LBala]

Thanks swaink and healey21, nice work! Unfortunately the set of formulas will only work for that table with the values on that specific order. For example, if you switch the values on column C (1048, 396, 604) with the values on column A (984, 404, 663) making the same table looking like the one below, the final result will be 2029 applying the same set of formulas, and 2029 is not the lowest possible sum of values with the constraint that each value we get from the columns (one value per column) needs to be in a different row.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]1048[/TD]
[TD]970[/TD]
[TD]984[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]396[/TD]
[TD]1454[/TD]
[TD]404[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]604[/TD]
[TD]1797[/TD]
[TD]663[/TD]
[/TR]
</tbody>[/TABLE]

Any suggestions on how to fix that?
Thank you very much for your help, I don't think I will be able to solve that by myself.

Hi Guys

This is great work by healey21 nice one I like it

Just to take it a step further if you amend the cells in column I as below this will allow you to calculate the value you are expecting to see

All I have done is change the formulas to show row rather than address this gives you just the row numbers this allows us to find the second smallest value in each case if the results must be on different rows

I2=CELL("ROW",INDEX(B1:B4,MATCH(G2,B1:B4,0)))
I3=CELL("ROW",INDEX(C2:C5,MATCH(G3,C2:C5,0)))
I4=CELL("ROW",INDEX(D3:D6,MATCH(G4,D3:D6,0)))

J3=COUNTIF($I$2:$I$3,I3)
J4=COUNTIF($I$2:$I$4,I4)

F2=SMALL(B2:B4,1)
F3=IF(J3=1,SMALL(C2:C4,1),SMALL(C2:C4,2))
F4=IF(J4=1,SMALL(D2:D4,1),SMALL(D2:D4,2))

F6=SUM(F2:F5)

Hope this helps

Kevin

 

[Peter_SSs]

Perhaps you would consider a user-defined function (UDF)? This one is suitable for a 3x3 range only.
The first parameter in the function is the (3x3) range to look at.
The second parameter is 1 to get the minimum total and 2 to get the cell addresses.

To implement ..

1. Right click a sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formulas as shown in the screen shot below.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled file (*.xlsm)

Function GetMin(r As Range, Itm As Long)
  Dim a
  Dim Result(1 To 2)
  Dim i As Long, j As Long, k As Long, z As Long, MinTot As Double, tmp As Double
  
  If r.Rows.Count = 3 And r.Columns.Count = 3 And (Itm = 1 Or Itm = 2) Then
    MinTot = 10 ^ 300
    a = r.Value
    For i = 1 To 3
      For j = 1 To 3
        For k = 1 To 3
          If i <> j And i <> k And j <> k Then
            z = z + 1
            tmp = a(i, 1) + a(j, 2) + a(k, 3)
            If tmp < MinTot Then
              MinTot = tmp
              Result(1) = MinTot
              Result(2) = _
                r.Cells(i, 1).Address(0, 0) & ", " & _
                r.Cells(j, 2).Address(0, 0) & ", " & _
                r.Cells(k, 3).Address(0, 0)
            End If
          End If
        Next k
      Next j
    Next i
    GetMin = Result(Itm)
  Else
    GetMin = CVErr(xlErrNA)
  End If
End Function

Excel Workbook
	A	B	C	D	E	F	G	H
1
2
3			A	B	C		Min Total	Cells used
4		D	984	970	1048		1978	C5, D4, E6
5		E	404	1454	396
6		F	663	1797	604
7
Min Total (2)

 

[LBala]

The UDF provided achieves everything I'm looking for but the problem is that I have multiple tables with multiple formats (from 3x3 tables up to 50x50) to work with, that's why I think a UDF is not the best solution... I am really looking for a formula to get the result and maybe another way (or code) to get the location of the values in the table...

I am open to any other practical suggestion for multiple table formats though.


Thanks for the nicely made function.

 

[RoryA]

Cross-posted here: Formula to find the minimum value with constraint

Please read this: http://www.excelguru.ca/content.php?184

 

[XOR LX]

May I ask why my post has been removed?

 

[LBala]

Thanks for the explanation, I am new to forums like this, sorry if I have offended anyone for cross-posting.