Compare number to range; find highest positive difference
 

I need to compare the value in a cell to a range of values, and select the
value in the range that has the smallest positive difference. If the index
cell is 4, and the range has 1,2,3 and 9, I need the range to pick 9. I've
tried to use =min(index no. - range), but that doesn't seem to work; vlookup
doesn't seem to work, either.

Grateful for your thoughts.

Compare number to range; find highest positive difference
 

Try this array formula** :

A1 = base number
C1:C4 = range of numbers

=INDEX(C1:C4,MATCH(TRUE,C1:C4-A1=MIN(IF(C1:C4-A1=0,C1:C4-A1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"RBW" wrote in message
...
I need to compare the value in a cell to a range of values, and select the
value in the range that has the smallest positive difference. If the
index
cell is 4, and the range has 1,2,3 and 9, I need the range to pick 9.
I've
tried to use =min(index no. - range), but that doesn't seem to work;
vlookup
doesn't seem to work, either.

Grateful for your thoughts.

Compare number to range; find highest positive difference
 

Biff-

Many thanks- that works perfectly. Impressive coding!

RBW

"T. Valko" wrote:

Try this array formula** :

A1 = base number
C1:C4 = range of numbers

=INDEX(C1:C4,MATCH(TRUE,C1:C4-A1=MIN(IF(C1:C4-A1=0,C1:C4-A1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"RBW" wrote in message
...
I need to compare the value in a cell to a range of values, and select the
value in the range that has the smallest positive difference. If the
index
cell is 4, and the range has 1,2,3 and 9, I need the range to pick 9.
I've
tried to use =min(index no. - range), but that doesn't seem to work;
vlookup
doesn't seem to work, either.

Grateful for your thoughts.

Compare number to range; find highest positive difference
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"RBW" wrote in message
...
Biff-

Many thanks- that works perfectly. Impressive coding!

RBW

"T. Valko" wrote:

Try this array formula** :

A1 = base number
C1:C4 = range of numbers

=INDEX(C1:C4,MATCH(TRUE,C1:C4-A1=MIN(IF(C1:C4-A1=0,C1:C4-A1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"RBW" wrote in message
...
I need to compare the value in a cell to a range of values, and select
the
value in the range that has the smallest positive difference. If the
index
cell is 4, and the range has 1,2,3 and 9, I need the range to pick 9.
I've
tried to use =min(index no. - range), but that doesn't seem to work;
vlookup
doesn't seem to work, either.

Grateful for your thoughts.

Compare number to range; find highest positive difference
 

Not sure, but this array formula might be another option.
It assumes your input data is in the range 1-9.

=MIN(IF(C1:C4A1,C1:C4))

Note that if the input is 3, it rounds up to 9.
This is because you said "the smallest positive difference."
A returned value of 3 is a zero difference is not what you asked.
Perhaps you meant non-negative (0 or better).
- - -
HTH :)
Dana DeLouis


RBW wrote:
Biff-

Many thanks- that works perfectly. Impressive coding!

RBW

"T. Valko" wrote:

Try this array formula** :

A1 = base number
C1:C4 = range of numbers

=INDEX(C1:C4,MATCH(TRUE,C1:C4-A1=MIN(IF(C1:C4-A1=0,C1:C4-A1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"RBW" wrote in message
...
I need to compare the value in a cell to a range of values, and select the
value in the range that has the smallest positive difference. If the
index
cell is 4, and the range has 1,2,3 and 9, I need the range to pick 9.
I've
tried to use =min(index no. - range), but that doesn't seem to work;
vlookup
doesn't seem to work, either.

Grateful for your thoughts.

Compare number to range; find highest positive difference
 

Wow!

You think I made that more complicated than need be? <g

--
Biff
Microsoft Excel MVP


"Dana DeLouis" wrote in message
...


Not sure, but this array formula might be another option.
It assumes your input data is in the range 1-9.

=MIN(IF(C1:C4A1,C1:C4))

Note that if the input is 3, it rounds up to 9.
This is because you said "the smallest positive difference."
A returned value of 3 is a zero difference is not what you asked. Perhaps
you meant non-negative (0 or better).
- - -
HTH :)
Dana DeLouis


RBW wrote:
Biff-

Many thanks- that works perfectly. Impressive coding!

RBW

"T. Valko" wrote:

Try this array formula** :

A1 = base number
C1:C4 = range of numbers

=INDEX(C1:C4,MATCH(TRUE,C1:C4-A1=MIN(IF(C1:C4-A1=0,C1:C4-A1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.


--
Biff
Microsoft Excel MVP


"RBW" wrote in message
...
I need to compare the value in a cell to a range of values, and select
the
value in the range that has the smallest positive difference. If the
index
cell is 4, and the range has 1,2,3 and 9, I need the range to pick 9.
I've
tried to use =min(index no. - range), but that doesn't seem to work;
vlookup
doesn't seem to work, either.

Grateful for your thoughts.

Compare number to range; find highest positive difference
 

Dana-

Appreciate the thought. The actual application is to compare the date an
expense was incurred (the index cell) against the range of dates of invoices
sent to a client (hence the need for only a positive difference- you can't go
back and add to a past invoice :) ). I suppose an expense could be
incurred and billed on the same day (the "0" outcome), but would guess it's
unlikely, so a positive number is probably what the formula needs to find.

A nice enhancement, which I think I can do using the =IF function, would be
to show, for expense dates newer than any invoice, a message along the lines
of, "Not yet billed" or something like that.

FWIW, I'm looked at as understanding Excel pretty well, but every time I
come here, I realize how little I know compared to all of you. Really
appreciate everyone's time in thinking through issues like this.

"Dana DeLouis" wrote:



Not sure, but this array formula might be another option.
It assumes your input data is in the range 1-9.

=MIN(IF(C1:C4A1,C1:C4))

Note that if the input is 3, it rounds up to 9.
This is because you said "the smallest positive difference."
A returned value of 3 is a zero difference is not what you asked.
Perhaps you meant non-negative (0 or better).
- - -
HTH :)
Dana DeLouis


RBW wrote:
Biff-

Many thanks- that works perfectly. Impressive coding!

RBW

"T. Valko" wrote:

Try this array formula** :

A1 = base number
C1:C4 = range of numbers

=INDEX(C1:C4,MATCH(TRUE,C1:C4-A1=MIN(IF(C1:C4-A1=0,C1:C4-A1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"RBW" wrote in message
...
I need to compare the value in a cell to a range of values, and select the
value in the range that has the smallest positive difference. If the
index
cell is 4, and the range has 1,2,3 and 9, I need the range to pick 9.
I've
tried to use =min(index no. - range), but that doesn't seem to work;
vlookup
doesn't seem to work, either.

Grateful for your thoughts.