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!
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?
