Geek Answers Handbook

A program that will return the cell address of the minimum value in a row?

So I have a chart that looks something like this. Suppose the top left value, 1, is in cell A1:

x= 1 2 3 4 5 6 7 8 4 3 2 1 2 3 4 5 9 8 7 6 7 8 9 10 8 7 6 5 4 3 2 1 Sum= 21 18 15 12 13 14 15 16

There are x values ​​from 1 to 8 and three columns of values ​​obtained due to his use of the equation or something below. The sum is the sum of three values ​​below their corresponding x value.

I was stuck trying to figure out something that would go through the line of sums, find the smallest value, and then assign the variable the x value of the variable. I also need to assign values ​​to the left and right of this x value to other variables.

For this particular chart, 12 is the smallest of the sums, so I would assign variable1 = 4 , since that is the column corresponding to the x-value. Then my second variable, called lowerbound , will be 3, since it is located to the left of x = 4, and my third variable, which is called upperbound , will be 5, since it is to the right of x = 4.

If I could return the cell address of the x-value that corresponds to the smallest sum, then I could assign it to a variable, and then just shift from that cell to assign other variables. Even if I could create a program that returns me the cell of the minimum value of the sum, I could shift to the x-line and go from there.

How do I do something like this?

TL: DR: ask more clearly, as it is a lot of words: what does a program look like that determines the smallest value in the sum line and returns the cell address of this value?

The length of the rows is unknown and varies a lot, but the length of the columns is given. They vary depending on the problem, but they will always be known. Therefore, I will always know how many rows are in a column, but I do not know how many columns are in a row.

This is the most mysterious thing I've ever written in my whole life, but I hope I explained it well enough to make sense.

You guys are really amazing, by the way. I still understood this program, and all this is due to how useful you are. I honestly think that I would still be stuck in the beginning with you guys! You are ready to endure the ongoing questions of beginners.

+4
source share
3 answers

I assume the amount is in A4:H4 . Please change if applicable

You can use a formula like

 =CELL("address",INDEX(A4:H4,MATCH(MIN(A4:H4),A4:H4,0)))

If you want to use VBA, you can use this

 Sub Sample() MsgBox Application.Evaluate("=CELL(""address"",INDEX(A4:H4,MATCH(MIN(A4:H4),A4:H4,0)))") End Sub
+10
source

Using your example, the following formula returns the address of the cell in row 1, whose value in row 5 is the lowest:

=ADDRESS(1,MATCH(MIN(A5:H5),A5:H5,0))

And if you want this cell value, use INDIRECT . It takes the address as a string.

=INDIRECT(ADDRESS(1,MATCH(MIN(A5:H5),A5:H5,0)))

+7
source

If you sum the columns, taking the sum of the array. Here is the VBA version:

 For j = 1 To 8 For i = 1 To 3 sum(j) = sum(j) + Cells(i + 1, j + 1) Next i Cells(5, j + 1) = sum(j) Next j
+2
source

More articles:


All Articles