Find the nearest Number

  • Thread starter Thread starter [G]rumpy [O]ld [D]uffer
  • Start date Start date
G

[G]rumpy [O]ld [D]uffer

I have a table that, as an example, shows the following:-

Volume > 1000 2200 2500 3300 5000
Operators > 5 7 8 10 16

Is there a function/formulae that will give me the nearest number of
'Operators' based on a 'Volume' being entered.

i.e. if I enter a Volume of 1500 the nearest matching 'Operator' is 5,
if I enter 1650 the nearest matching 'Operator' is 7.


HLOOKUP without the 'false' doesn't do it!!
 
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(A2:E2,1,MIN(IF(ABS(A1:E1-F1)=MIN(ABS(A1:E1-F1)),COLUMN(A1:E1))))
F1 contains your value to search for
 
I have a table that, as an example, shows the following:-

Volume > 1000 2200 2500 3300 5000
Operators > 5 7 8 10 16

Is there a function/formulae that will give me the nearest number of
'Operators' based on a 'Volume' being entered.

i.e. if I enter a Volume of 1500 the nearest matching 'Operator' is 5,
if I enter 1650 the nearest matching 'Operator' is 7.


HLOOKUP without the 'false' doesn't do it!!
Click to expand...


With your data set up as above:


=HLOOKUP(INDEX(Volume,1,MATCH(A6,Volume)+
(ABS(A6-INDEX(Volume,1,MATCH(A6,Volume)))>=
ABS(A6-INDEX(Volume,1,1+MATCH(A6,Volume))))),Table,2)

On my worksheet,


Operators =Sheet1!$B$2:$F$2
Table =Sheet1!$A$1:$F$2
Volume =Sheet1!$B$1:$F$1



--ron
 
If I'm not mistaken, this might be another option. It's only valid between
1000 & 5000.

=SUMPRODUCT({5,2,1,2,6},--(A1>={1000,1600,2350,2900,4150}))

HTH.
Dana DeLouis
 
Dana DeLouis said:
If I'm not mistaken, this might be another option. It's only valid
between 1000 & 5000.

=SUMPRODUCT({5,2,1,2,6},--(A1>={1000,1600,2350,2900,4150}))
Click to expand...
....

Your formula is valid for A1 values >= 1000. If A1 > 5000, then all
conditions in the second term above would be satisfied, so the formula would
return 16. But that makes sense - all A1 values above 5000 are always closer
to 5000 than any of the other values. Where it breaks down is that any value
less than 1000 is closer to 1000, so when A1 < 1000, the formula should
return 5 rather than 0.

This may be the minimal formula, but there are too many implicit calculated
differences. Calculating the sequential pairwise differences in the top row
is sufficient.

=LOOKUP(A1-0.0000000001,{-1E+300,1600,2350,2900,4150},{5,7,8,10,16})

But there's no reason to calculate the midpoints for Excel. Excel could do
this itself.

({-2E300,1000,2200,2500,3300}+{1000,2200,2500,3300,5000})/2

and if these values were in a range,

(IF(COLUMN(Rng)>CELL("Col",Rng),OFFSET(Rng,0,COLUMN(Rng)-2,1,1),
-2E+300)+Rng)/2
 
Back
Top