=MAX(IF(AND($U11=0,$V11=0),MROUND($S11-$X11,4),IF($X11=0,MROUND($W11-$N11,4),MROUND($W11-$X11,4))))

This formula works. But I am missing something in it. Because when
cell N11 is greater/more than W11. I get the #NUM! error. Otherwise the
calculation is right.

Anyone out there than can help me add the argument that if N11W11 give me
ZERO as will as all the above.

Help! Bonnie

Hi!

Try adding to this portion:

IF($X11=0,MROUND($W11-$N11,4)

IF(AND($N11$W11,$X11=0),0,IF($X11=0,MROUND($W11-$N11,4).......................

Biff

"BSantos" wrote in message
...
=MAX(IF(AND($U11=0,$V11=0),MROUND($S11-$X11,4),IF($X11=0,MROUND($W11-$N11,4),MROUND($W11-$X11,4))))

This formula works. But I am missing something in it. Because when
cell N11 is greater/more than W11. I get the #NUM! error. Otherwise the
calculation is right.

Anyone out there than can help me add the argument that if N11W11 give me
ZERO as will as all the above.

Help! Bonnie

=MROUND(x,y) gives a #NUM! error if in if one of x or y is negative and
one positive - to prevent that use

=MAX(IF(AND($U11=0,$V11=0),MROUND(MAX($S11-$X11,0),4),IF($X11=0,MROUND(MAX($W11-$N11,0),4),MROUND($W11-$X11,4))))

....although is this the exact formula you're using? Because the MAX
function at the start is superfluous, this is sufficient

=IF(AND($U11=0,$V11=0),MROUND(MAX($S11-$X11,0),4),IF($X11=0,MROUND(MAX($W11-$N11,0),4),MROUND($W11-$X11,4)))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=502687

"BSantos" wrote:
=MAX(IF(AND($U11=0,$V11=0),MROUND($S11-$X11,4),
IF($X11=0,MROUND($W11-$N11,4),MROUND($W11-$X11,4))))

This formula works. But I am missing something in it. Because
when cell N11 is greater/more than W11. I get the #NUM! error.

That is because $W11-$N11 becomes negative, which differs
from the sign of "4". See the last example on the MROUND
help page. If you wanted to make it work, you could change
it to:

=MROUND($W11-$N11,4*SIGN($W11-$N11))

But I have no idea if that fits with your logic.

Anyone out there than can help me add the argument that
if N11W11 give me ZERO as will as all the above.

It is not clear to me where that fits in the logic that you have
above. Perhaps it would help if you modified the following
description, based on what you have above:

If U$11=0 and $V11=0, MROUND($S11-$X11,4)
else if $X11=0, MROUND($W11-$N11,4)
else MROUND($W11-$X11,4)

Not sure what you intend to MAX() to do, since there is
only one argument. Is that what you are asking for: a
second argument that depends on N11 and W11?

Even so, it is not clear to me what you intend the logic
to be. As a wild guess, I wonder if you want:

else if $X11=0, MROUND(MAX(0,$W11-$N11),4)

and get rid of the "outer" MAX(...). In other words:

=IF(AND($U11=0,$V11=0),MROUND($S11-$X11,4),
IF($X11=0,MROUND(MAX(0,$W11-$N11),4),MROUND($W11-$X11,4)))

Caveat emptor: GIGO.

wrote...
....
That is because $W11-$N11 becomes negative, which differs
from the sign of "4". See the last example on the MROUND
help page. If you wanted to make it work, you could change
it to:

=MROUND($W11-$N11,4*SIGN($W11-$N11))
....

Probably better to change it to

=SIGN($W11-$N11)*MROUND(ABS($W11-$N11),4)

[and it's a mystery why MROUND does this: it should only choke when 2nd
arg = 0, since MROUND(x,y) could be defined as ROUND(x/y,0)*y].

"Harlan Grove" wrote:
wrote...
=MROUND($W11-$N11,4*SIGN($W11-$N11))

Probably better to change it to
=SIGN($W11-$N11)*MROUND(ABS($W11-$N11),4)

I'll bite: why? If there is a difference, please provide an example.

and it's a mystery why MROUND does this

Y'get no argument from me on that one.

wrote...
"Harlan Grove" wrote:
wrote...
=MROUND($W11-$N11,4*SIGN($W11-$N11))

Probably better to change it to
=SIGN($W11-$N11)*MROUND(ABS($W11-$N11),4)

I'll bite: why? If there is a difference, please provide an example.
....

Since MROUND(x,0) always returns 0 (which I only just now confirmed), I
suppose your formula with just two function calls would be more
efficient. However, better to eliminate the ATP dependency and use

=4*ROUND(($W11-$N11)/4,0)