Using minimum value to get column #

[Danish_dan]

A am doing a queuing problem, with a single queue and multiple servers. I am currently looking for a function which will find the smallest number in a matrix and return the corresponding column number.

I want to find the server to take on the next customer, so I need to find the lowest "end service" time and get a corresponding server number, this could be the column.

Hope you can help

 

[XOR LX]

Hi,

Will the smallest number in that range be unique? If not, what should be the outcome?

Regards

 

[Danish_dan]

Yes the smallest number in the matrix will be unique, so the formula should return the value or name of the column for the cell which includes the smallest value.

 

[XOR LX]

In that case, with e.g your matrix in A1:J10 (with headers in row 1):

=INDEX(A1:J1,SUMPRODUCT((A2:J10=MIN(A2:J10))*((COLUMN(A2:J10)-MIN(COLUMN(A2:J10))+1))))

Regards

 

[Danish_dan]

Thank you for the reply I cannot seem to make it work what we have so far:
Col1 Col2 Col3 Col4 Col5
0 0 0 0
10 0 0 0
0 20 0 0
0 0 25 0
0 0 0 30

So what we would like in Col5 would be the returning number of the matrix for where we have come to, so in Col5 Row2 it should find the min number and give the corresponding value of the matrix A2:D2, so in this case it would not matter which one it chose since they are all 0. In Col5 Row 3 it should chose the lowest number in the matrix A2:D3 and give either the value 2, 3 or 4 as they are the lowest (value 0)

 

[XOR LX]

Hi again,

Not sure I understand. You said "Yes the smallest number in the matrix will be unique" but now you seem to be presenting an example in which zero appears many times. Can you clarify?

Also, you say that the result entered in row 2 should be for A2:D2, and that for row 3 for A2:D3 (not A3:D3, as I might have guessed). Is this correct? So the result for row 4 should be based on A2:D4, that for row 5 on A2:D5, etc.? The first reference always remains fixed at 2?

What are your expected results for each of the rows in the example you give?

Regards

 

[Danish_dan]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Rows/col[/TD]
[TD]Col1[/TD]
[TD]Col2[/TD]
[TD]Col3[/TD]
[TD]Col4[/TD]
[TD]Col5[/TD]
[/TR]
[TR]
[TD]Row1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row3[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row4[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]25[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row7[/TD]
[TD]25 (10+15)[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Yep sorry the smallest value will not be unique as it is 0, sorry about that. The numbers in the matrix resemble service times/or server finish time, so the first customer arrive in time 0 and go to counter 1 and take the time of 10. The second customer go to counter 2 and is finished in time 20.
So in col 5 I would like to get the corresponding col to the smallest value. In row5col5 it should be the value 4 (as counter 4 has the lowest value which is 0, where as the others have a value of 10, 20 and 25).

Hope it is clear and you still have time to help

 

[XOR LX]

Sorry. Still not clear! Could you tell me all of your expected results in column 5 of that table?

Regards