Find the nearest Number

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

 

[Frank Kabel]

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

 

[Ron Rosenfeld]

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

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

 

[Dana DeLouis]

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

 

[Harlan Grove]

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}))

....

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