0

I am not sure where can I ask my question, but after a small research I decided to ask here:

If I had a question with Excel, which site would I use? [duplicate]


I have a question regarding the following table:

Empty table

In Input A — Cell B4 we put a positive number and in Input B — Cell B7 we put also a positive number. Regarding the pregiven table Value A, Value B and Value CArray D3:I9 with all of the so depicted values (these values can be random, in my case I just used the prime numbers), I need a way to generate an output in Output C — K6.

The relations to produce a value for the Output is the following: The number in Input A — Cell B4 is related to Value A — Array D3:D7 and the number in Input B — Cell B7 is related to Value B — Array E3:I7. The combination between the Value A and Value B is related with Value C — Array E9:I9.

(1) The Input A generates Value A by picking from Array D3:D7 the smallest number bigger than the value in B4.

(2) The Input B can only be consequent to Input A. When Value A is generated, Value B is on the same row as Value A, at the same time Value B is the biggest number in Array E[ROW_VALUE_A]:I[ROW_VALUE_A].

(3) The Output C can only be generated after we generate the Value B. The Value C is the only number that is found on the same column as Value B.

(4) Output C reads Value C.


Since this is a little complicated to explain, I feel like giving an example will make things smoother:

(1) If we put number 2,6 in B4, the Value A has to go Cell D4 — 3, because 3 is the smallest number in Array D3:D7. Stage 1

(2) This therefore leads us to narrowing down our array to pick Value B from Array E4:I4. In this array the smallest value bigger than Input B — B7 is 79 — Cell H4. 79 — Cell H4 will be our Value B. Stage 2

(3) Since 79 is in Cell H4, the only number from the column, that goes in Values C — Array E9:I9 is 139 — H9. Stage 3

(4) Obviously we print H9 to K6. Stage 4


If somebody asked me how to achieve this, I will not be very encouraged, so I will show you my progress so far:

(1) I managed to pick the smallest value from array Value A — D3:D7, bigger than Input A — B4. The formula is the following:

=MIN(IF((D3:D7>=B4)*(D3:D7);(D3:D7)))

Which has to be applied with ctrl+shift+enter, as it is an array formula.

Regarding (2) and (3) I don't know how to force the above formula over the array determined by the row of the Value A.

If I manage to read the coordinates of the cell to the Value A — Array D3:D7, this problem is probably solved.

Here I apply the file, so if anybody is interested, could give it a try: The Exel File


I prefer not to do it with coding in Macros, but with an "in-cell" formula typed in Output C — K6.

Thank you for your attention and good luck! :)

0

Use the following formula for your exact example material. Naturally, you might alter it to suit larger tables or perhaps you might use Named Ranges for portions of it.

It is an array formula, the CSE variety, so I'll present it WITH the brackets, but naturally only copy and paste what is in-between:

{=INDIRECT(ADDRESS(9,MATCH(MIN(IF(INDIRECT(CONCATENATE(ADDRESS(MATCH(MIN(IF(D3:D7>B4,D3:D7)),D3:D7,0)+2,5),":",ADDRESS(MATCH(MIN(IF(D3:D7>B4,D3:D7)),D3:D7,0)+2,9)))>B7,INDIRECT(CONCATENATE(ADDRESS(MATCH(MIN(IF(D3:D7>B4,D3:D7)),D3:D7,0)+2,5),":",ADDRESS(MATCH(MIN(IF(D3:D7>B4,D3:D7)),D3:D7,0)+2,9))))),INDIRECT(CONCATENATE(ADDRESS(MATCH(MIN(IF(D3:D7>B4,D3:D7)),D3:D7,0)+2,5),":",ADDRESS(MATCH(MIN(IF(D3:D7>B4,D3:D7)),D3:D7,0)+2,9))),0)+4))}

(I left the cell references in it relative to make it easier to read. You might $$ them, or prehaps replace them with Named Ranges for each.)

It can be extended by adjusting the cell and range references. 2,000 rows? Then ":D2002" rather than ":D7" and so on. If using Named Ranges for those items, you only need make one alteration for each range reference (all the "D3:D7" bits in the formula), and similarly if you move the input cells (B4 and B7).

Basically you:

1) Use:

=MIN(IF(D3:D7>B4,D3:D7))

to find the proper result given Input A. Easy enough, but where is it in the range?

2) Use MATCH() to locate its row in range D3:D7. Perhaps the last row, row 5 of that range. Add 2 to it since there are 2 rows above it, to get the row of the spreadsheet (not the range) it is in. You already knew the columns are 5 and 9 (E and I) and now you know the row. Put that all together to get the range you want to examine using Input B.

3) In the formula above, I used the ADDRESS function to build the range to examine against Value B to illustrate the bigger picture: you might need to create these all dynamically. I balked at completely doing so and used what I knew for figuring the columns, but if the table would never grow, or only every blue moon, you could replace what is inside the INDIRECT() functions with INDIRECT() wrapping a simple concatenation of the row learned in 2) and the two column letters rather than all the ADDRESS() effort.

4) Either way, having found the value inputs A and B led to, use MATCH() to find its column and build one last INDIRECT(ADDRESS()) portion to create the address in the Values C that you need.

5) That returns the value you are seeking, so as long as you remember to CSE it, you are in business.

Personally, I hate complexity in spreadsheets so I use Named Ranges as mentioned earlier (clarity AND easier to extend as the spreadsheet is used) and I would also put the raw bits of this formula into Named Ranges so that I could easily upgrade them as I use the spreadsheet over time. Then instead of having a nightmare to piece apart and re-understand before I could upgrade or extend it, I could do the parts bit by bit in their Named Ranges. If your tables could ever grow, especially if they could grow all the time and unpredictably, that approach would really help you manage the process.

Source: I took the formula in 1), above, from this site:

https://www.extendoffice.com/documents/excel/4279-excel-find-closest-nearest-value-greater-than-less-than.html

0

This was an excellent reply, thank you — it solved my issue.

Since it was written with commas and my Excel due to some Windows setting prefers semicolons, I will use your solution and convert it with semicolons, so users like me can copy the solution directly:

=INDIRECT(ADDRESS(9;MATCH(MIN(IF(INDIRECT(CONCATENATE(ADDRESS(MATCH(MIN(IF(D3:D7>B4;D3:D7));D3:D7;0)+2;5);":";ADDRESS(MATCH(MIN(IF(D3:D7>B4;D3:D7));D3:D7;0)+2;9)))>B7;INDIRECT(CONCATENATE(ADDRESS(MATCH(MIN(IF(D3:D7>B4;D3:D7));D3:D7;0)+2;5);":";ADDRESS(MATCH(MIN(IF(D3:D7>B4;D3:D7));D3:D7;0)+2;9)))));INDIRECT(CONCATENATE(ADDRESS(MATCH(MIN(IF(D3:D7>B4;D3:D7));D3:D7;0)+2;5);":";ADDRESS(MATCH(MIN(IF(D3:D7>B4;D3:D7));D3:D7;0)+2;9)));0)+4)) 

Do not forget to apply the formula in the cell with ctrl+shift+enter!

I am now about to go trough the explanation of the formula, since you put some emphasis for us to understand it.

Once again — thank you for your help and keep up with the good work!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.