Find first value greater than a certain number in Excel

Question

Hello, good night

I wish my friends here could help me with this question. I have an Excel spreadsheet for calculation of steel reinforcements for concrete structures. By calculation, I find a value of steel area per m². From this value, I want to find in a list of predetermined values the one that suits me, that is, the first value greater or equal to the found. I can find the value with the SE function, only it gets huge and with several conditional, given the large amount of values. So if anyone knows in a simpler way, I’d appreciate it. Below follows images for better understanding:

P.S.: Remember that as I choose the steel gauge, I want a function that looks in a specific column. As I chose 10mm, the value found should be in Column S

Answer 1

SOLUTION FOUND: INDEX + CORRESP

Thank you very much to the friends who tried to solve my problem, but I managed to unravel, after a lot of breaking my head, since I had already tried to solve this same problem in other spreadsheets of mine and in more complicated tables than this, with several entries. The secret of the solution lies in the use of the function CORRESP, because with it, I can find in which line is the lowest value of steel area greater than calculated, simply using the TYPE OF CORRESPONDENCE -1(IS GREATER THAN), in the last argument of the formula. That way I use such value in the function INDEX, in which I have as matrix all my data, the line the value given by CORRESP and the column the value given by the cell linked to the Form Control, where the gauge is selected by the user, plus 1 unit

DETERMINATION OF THE EFFECTIVE STEEL AREA:

=SEERRO(ÍNDICE($N$5:$T$34;CORRESP(I21;SE(U12=1;$O$5:$O$34;SE(U12=2;$P$5:$P$34;SE(U12=3;$Q$5:$Q$34;SE(U12=4;$R$5:$R$34;SE(U12=5;$S$5:$S$34;SE(U12=6;$T$5:$T$34))))));-1);U12+1);"Selecione uma bitola de aço maior")

In function CORRESP of this formula, the user chooses a gauge, which corresponds to a number of the linked cell of the Form Control (Combobox - Not Control ACTIVEX). Thus, the 4.2mm gauge corresponds to index 1.5mm index 2 and so on. This will define the column in which the CORRESP will search. In general function INDEX, the last argument of the function that requests the column, where it contains the data that I want, is added a unit, because the user’s chosen gauge corresponds, in the table, to its index plus 1, that is, 4.2mm is index 1, but in the table is column 2, 5mm is index 2, but in the table is column 3 and so on (COLUMN=index+1). The function SEERRO was used for the case of the largest slatted steel area of the selected gauge is lower than the calculated one, asking the user to choose a larger gauge. The function CORRESP me, for the values highlighted below, the lines 9 and 13 of the table, respectively.

Of course that Table Auxiliary will not be viewed by the user, as well as the index of the gauge selection. I put for better understanding of what I did. I opted for the function INDEX at the beginning of PROCV because it allows me to search for data to the left and thus automatically determine the spacing of the bars (Column 1), which is another data that also interests me. Thus were used the same function above, with only modification of the last argument of the formula, which is now Table Column 1 (Spacing).

SPACING:

=SEERRO(ÍNDICE($N$5:$T$34;CORRESP(I21;SE(U12=1;$O$5:$O$34;SE(U12=2;$P$5:$P$34;SE(U12=3;$Q$5:$Q$34;SE(U12=4;$R$5:$R$34;SE(U12=5;$S$5:$S$34;SE(U12=6;$T$5:$T$34))))));-1);1);"─")

*OBS.: The formulas got big because I chose to put the formula CORRESP within the formula INDEX. But I could have done as I did initially, calculating the CORRESP in another cell and entering the function INDEX only with its numerical value.

Answer 2

I think this formula meets your need.

I created an auxiliary calculus cell so that the search is done correctly.

Auxiliary formula

=SE(+INT(X2)-X2<-0,5;ARRED.PARA.CIMA(X2;0);X2)

Search formula

=+PROCV(Y2;N:T;CORRESP(W2;N$2:T$2;0);3)

Note: I did some tests and got the correct values, in case there is any error says to correct.

Answer 3

The solution works, I do not know if it is with the best Golf Code possible. There is room to decrease this formula.

But with the data as in the following picture:

Given a gauge and minimum value

In cell M6 the column with the gauge data (1 to 6 in row 4) and in cell M7 the calculation value (in example 6,1).

With the formula =MÍNIMO(SE(INDIRETO(ENDEREÇO(4;14+CORRESP(M6;O4:T4;0))&":"&ENDEREÇO(18;14+CORRESP(M6;O4:T4;0)))>=M7;INDIRETO(ENDEREÇO(4;14+CORRESP(M6;O4:T4;0))&":"&ENDEREÇO(18;14+CORRESP(M6;O4:T4;0))))) inserted with Ctrl+Shift+Enter

The result is to find in the column with the value of gauge 4, the lowest value greater than 6.1. That is 6.5.

Given a calculation, find the gauge

=INDIRETO(ENDEREÇO(4;MÍNIMO(SE(O5:T18>=M7;COL(O:T))))) inserted with Ctrl+Shift+Enter

Find only in the column

To find in column S the calculation value in cell M7

=MÍNIMO(SE(S:S>=M7;S:S)) inserted with Ctrl+Shift+Enter

Answer 4

A matrix formula can be used to make all SE'If you didn’t have to write it all down, it would look like this:

{=minimo(se("16,00":"2,42">"8,86";"16,00":2,42"))}

Obs.: Replace the values with the corresponding cell without quotation marks, and use Ctrl+Shift+Enter to insert {}.