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.

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

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

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.

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)


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

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.

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