A roblem with modifying a formula for determining the closest value in particular range

[Arty_1]

I’ve got the formula which makes a word „Order” (column B) meet the closest value of 35.00 (column A)

How to modify the formula so that ''Order" meets only the first closest value of 35.00 in the range (>=35;<39)

I've been trying to change the value comparisons to set up the range from 35.00 till 39.00.

but encountered a problem that "Order'' often meets two closest values of 35 till 39, e.g. 36.50; 34.00.

"order" must meet only the first closest value of 35.00 in the mentioned range (>=35.00;<39)

Would you be so kind as to give the matter a reasonable consideration?

See the workbook attached.

Thanks in advance

[Bernie Deitrick]

In B2, use the formula

=IF(AND($A2>=$D$2*$C$2,$A2<=$D$2,COUNTIF($B$1:B1,$E$2&"")=0),$E$2&"","")
or
=IF(COUNTIFS($A$2:$A2,">=" &$D$2*$C$2,$A$2:$A2,"<=" &$D$2)=1,$E$2&"","")

[Arty_1]

Using your formula "Order" meets the value of 35.00 just once at the cell B17,

there are about 360 cells down in the workbook with several approximate values of 35

What was your plan to create the range (>=35.00;<39.00)?

[Bernie Deitrick]

I guess your requirements were not clear. I thought you did not want Order repeated more than once?

[Arty_1]

There are about 20 approximate and precise values of 35.00 in the column A which have to be met by "Order".

[Bernie Deitrick]

There are about 20 approximate and precise values of 35.00 in the column A which have to be met by "Order".

In your first post, you wrote

"order" must meet only the first closest value of 35.00 in the mentioned range (>=35.00;<39)

So, what do you want? Post a workbook with the results you want - don't use formulas, just type "Order" in the cells where you want it to appear, and explain your criteria.

[Arty_1]

I agree, I should take my time on detailed explanation of the requirements.

I've attached the workbook with better explanation (Txt & graphic).

[Bernie Deitrick]

Ok, but that still begs the question - why are you talking about 35 to 40, when you are multiplying 35 by 92% to get the lower limit (32.2) and using 35 as the upper limit. How do you want the limits to be related to those values?

[Arty_1]

Thats the question of the thread: A roblem with modifying a formula,-

consequently how to change/substitute the formula, parameters, value comparisons etc. to meet the requirements!

[Bernie Deitrick]

I understand that, but you need to explain how 35, 92%, and 35 to 40 are related.