Forum Discussion
Finding a value In a column between two numbers
I think I have interpreted that you want to measure the degree of proximity of each of these numbers with the range 68-128, or some other range.
I think it's a very interesting topic; I have applied the following formula:
= IF (AND (G4> = 68; G4 <= 128); 1; IF (G4> 128; G4-128; SI (G4 <68; 68-G4; 0)))
This formula assigns 1 to the values that are within the range.
To the values that are above the range, I attribute the value of the subtraction between the number and the maximum value of the range; and to those who are below the range, I attribute the subtraction between the minimum and the number.
The lower the number, the closer to the range it will be.
In the example, I used 68 and 128; but these values can be written in two cells and you can refer to them.
Hope this helps!
- SergeiBaklanOct 28, 2019Diamond Contributor
What do you mean exactly under matched number, matched with what? If that's the maximum number in the range (A1:A16) which is less than 128 and more than 68, when Subodh_Tiwari_sktneer formula is correct, it returns 74.4. And since it's the only number in the range, it simultaneously the smallest one. Another variant of non-array formula without MAXIFS is
=IFERROR(AGGREGATE(14,6,1/($A$1:$A$16<=128)/($A$1:$A$16>=68)*$A$1:$A$16,1),"no such")
Or you mean something more complex?