=N2-LARGE(IF(N2:R2<N2,N2:R2),1)
array entered (ctrl+shift+enter)
irrespective whether the values between O2:R2 are sorted since LOOKUP
expects them to be sorted.


"Duke Carey" wrote in message
...
Carol -
If you want the difference between N2 and the largest value that is
SMALLER
than N2, use

=IF(ISNA(LOOKUP(N2,O2:R2)),0,LOOKUP(N2,O2:R2))

which will give you a zero when N2 is smaller than any of the other values



"CarolC" wrote:

So Sorry... Of course, the answer is $9,000!! Was looking at the actual
spreadsheet when I wrote this! I apologize!!

"CarolC" wrote:

I have a worksheet that I want to perform an if statement on. Cell N
contains an amount. In cell O, P, Q, & R, I have dollar values keyed
(all in
row 2). What I want to do is say, if the amount in Cell N is greater
then O,
and cell N is greater than P, etc. until it tests each of the assigned
cells.
I want to have the result go into cell S!

Example: Cell N = $17,000.00. Cell O = $2000, Cell P = 5,000, with
the
final Cell R= $8000. Since 17000 8000, the result (difference)
should be
$2927.48 in Cell S!
I'm sure I'm making this tougher than it really is!! Thanks, in
advance,
for your assistance.