Need to direct value up to next available value

[RayH1]

Hi there, I have a formula to calculate a certain value.
=((((2*F114)*I116)*0,0175)/I120)
The result it produces should direct to next available product on the market. Diameters of: 4, 6, 10, 16, 25, 35, 50, 70
For example: if the formula calculates 3,04 if should refer to 4. If it calculates anything between 4,01 and 6 if should refer to 6. From 7,01 to 10… and so on.

I looked at the Lookup and Vlookup function or the IF-THEN function but those aren’t right for this i.m.o.

Please help to give guidance to right formula to use.
Thank you very much

[RayH1]

@karolus joshua4 @eeigor
Thanks very much for all your input.
I’m gonna try to put your suggestions to work.
Will start with the solution of @karolus.

After that I will see how to work with an index as @eeigor suggested.
However you didn’t actually mentioned where to put the formula. I only see the line of =INDEX({…};MATCH…))

Because the range of available diameters is a little larger actually. So it will be a very long formula in the end. And I can adjust the index overtime without touching the formula.

Thanks

[eeigor]

@RayH1

However you didn’t actually mentioned where to put the formula.

I did.

@eeigor

For simplicity’s sake, let Value be the result of evaluating your formula…

And Diameters are easy to edit, be it a named range or a named formula. The main formula does not need to be edited.

[RayH1]

@eeigor Right you did.
Was in to much hurry to read everything properly earlier.
I can put the formula in place of VALUE.

Okay thanks for your help.

[karolus]

hallo

=INDEX({4;6;10;16;25;35;50;70};MATCH(((F114*I116*0,035)/I120);{0;4;6;10;16;25;35;50}))

[joshua4]

…) The final parenthesis must have gotten clipped in copy. I parsed it backward and was trying to figure the use of Index and realized I was still in Match. Very slick idea.

[karolus]

@joshua4

The final parenthesis must have gotten clipped in copy.

Did not copy&paste, but direct editing the Formula, so I was not able to count the ()pairs

[eeigor]

To avoid shifting positions in arrays as @karolus suggested, you must use the reverse sorting order of the diameter values (the 3rd parameter of the MATCH function is -1).

For simplicity’s sake, let Value be the result of evaluating your formula, and the values ​​are in the range [0;70].
=INDEX({70;50;35;25;16;10;6;4};MATCH(Value;{70;50;35;25;16;10;6;4};-1))

In this case, the arrays are the same, and it is better to create a named range/named formula of diameter values for readability.
=INDEX(Diameters;MATCH(Value;Diameters;-1))

An example of using a named formula:

---

If the values ​​are equal to or greater than 70, then you need to add the largest possible value to the array, or handle the error as you see fit.

For instance (if Value>70 than the formula returns 70):
=INDEX({70;50;35;25;16;10;6;4};IFNA(MATCH(Value;{70;50;35;25;16;10;6;4};-1);1))

NOTE: IFNA(…;1), where 1 is the first position in the array of values equal to 70.

Edit: Fixed if Value>70, but not if Value>=70.

---

match-desc-order.ods (13.0 KB)

[karolus]

…or with ascending limits [0 … 70] in A1:A9:

=INDEX(A$2:A$9 ; MATCH( value ; A$1:A$8 ) )

and there is no need for Error-handling for any value >= 0

[Villeroy]

INDEX( Y ; MATCH( v ; X)) is the same as LOOKUP(v ; X ; Y)

=LOOKUP(((F114*I116*0,035)/I120);{0;4;6;10;16;25;35;50};{0;4;6;10;16;25;35;50})

[Lupp]

In reply to the withdrawn (for what reason?) comment above:
See attached example.

verySpecialRoundUp.ods (11.2 KB)

[eeigor]

@Lupp bravo!
@RayH1: Consider @Lupp’s solution. It looks like it is the best.
=INDEX(Diameters;MATCH(TRUE();Value<=Diameters;0);1)
where
Value is the result of evaluating your formula;
Diameters is {4;6;10;16;25;35;50;70}

This solution does not require shifting the positions in the two arrays, nor the reverse order of the values ​​in the array.

[sokol92]

Just for the fun of it is possible with one array:

=-VLOOKUP(-C1;-{70|50|35|25|16|10|6|4};1;1)

[eeigor]

@sokol92: Idea is clear. Where did you see that, huh?
The point is that an approximate search always selects the largest value less than or equal to the given one, but the OP needs to find the smallest value equal to or greater than the given one. This problem is solved by the trick with changing the sign of numbers. And the previous lower number becomes a larger number, which happens after the sign change. But the choice has already been made.

[sokol92]

@eeigor

Where did you see that, huh?

This is not Poincaré’s hypothesis, perhaps independently …

[eeigor]

This is some kind of reverse consciousness.
However, the search algorithm was tricked. We accept it for service.

[eeigor]

It should be noted that both solutions (@Lupp, @sokol92) were new and interesting for me. Thank you.

[RayH1]

Hello @Lupp
Thanks for your extended answer and example.
It looks like you are big step a head of me when it comes to writing formulas…

And @eeigor don’t worry on my choice. I think I wrote that I’ll go with the solution of @ karolus ‘for now’. ( has been put in place)

But I will look into the other variant with the index or sequence. Not only for this. But I see some value in it for other things to.

So thanks to everyone who contributed to find a workable solution