Find the closest meaning without going over
I found similar questions, but they are at least slightly different from my question and have not been able to successfully adapt them, so here is a simplified version of my worksheet:
A B C D E 1 4 4 17 2 6 10 3 2 12 4 7 19 5 4 23
Column A is filled with 1-X integers. Column B is the number of occurrences of column A, and C is the sum of the values to the left and above. D is a random value between 1 and C5. So far, so good. The problem is related to E1. I want it to give the A value that is to the left of the C value that is closest to D without going over.
Example: D corresponds to value 17. The closest value to 17 without transition is 12 (C3). So E is 3. How could I achieve this? I can get the closest value with
, but it comes up to 4 and not 3. How do I get the closest SMALL value? I am guessing that I need to replace the ABS SMALL, but I am not sure how.
source to share
For this you can use the "vector form"
(see the help for the function
). If you search
you will get exactly what you want (the largest value that is less than or equal to
) and then you can define the returned vector as
to get the corresponding value from there
Note. You will get an error for values D1 <4 because there is no "no jump" value in that case.
needs to be sorted in ascending order to work , but this will always be the case in this scenario
Edit: I believe this answers your question as stated, but if you were looking for 17th place then there would be no result 4? If so, then I think you can still use
, but column C must be configured differently
source to share