if statement in worksheet

[Guest]

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.

 

[Guest]

Looking at your example I'm having a really hard time seeing how the
'difference' is $2927.48.

 

[Guest]

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

 

[Guest]

Carol -

So..if N2 is > than ANY of the other values, you want the difference between
N2 & the highest other value, right? That's a start. What if N2 = one of
the other values, or if it is less than any of the values?

If you simply want the difference between N2 and the highest value in O
through R, then

=N2-MAX(O2:R2)

 

[Guest]

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

 

[N Harkawat]

=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.