Compare number to range; find highest positive difference

  • Thread starter Thread starter RBW
  • Start date Start date
R

RBW

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.
 
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.
 
Not sure, but this array formula might be another option.
It assumes your input data is in the range 1-9.

=MIN(IF(C1:C4>A1,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
 
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.
 
Back
Top