software

=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 N11gt;W11 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($N11gt;$W11,$X11=0),0,IF($X11=0,MROUND($W11-$N11,4).......................

Biff

quot;BSantosquot; gt; wrote in message
...
gt; =MAX(IF(AND($U11=0,$V11=0),MROUND($S11-$X11,4),IF($X11=0,MROUND($W11-$N11,4),MROUND($W11-$X11,4))))
gt;
gt; This formula works. But I am missing something in it. Because when
gt; cell N11 is greater/more than W11. I get the #NUM! error. Otherwise the
gt; calculation is right.
gt;
gt; Anyone out there than can help me add the argument that if N11gt;W11 give me
gt; ZERO as will as all the above.
gt;
gt; 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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=502687quot;BSantosquot; wrote:
gt; =MAX(IF(AND($U11=0,$V11=0),MROUND($S11-$X11,4),
gt; IF($X11=0,MROUND($W11-$N11,4),MROUND($W11-$X11,4))))
gt;
gt; This formula works. But I am missing something in it. Because
gt; 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 quot;4quot;. 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.

gt; Anyone out there than can help me add the argument that
gt; if N11gt;W11 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 quot;outerquot; 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...
....
gt;That is because $W11-$N11 becomes negative, which differs
gt;from the sign of quot;4quot;. See the last example on the MROUND
gt;help page. If you wanted to make it work, you could change
gt;it to:
gt;
gt;=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].quot;Harlan Grovequot; wrote:
gt; wrote...
gt; gt;=MROUND($W11-$N11,4*SIGN($W11-$N11))
gt;
gt; Probably better to change it to
gt; =SIGN($W11-$N11)*MROUND(ABS($W11-$N11),4)

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

gt; and it's a mystery why MROUND does this

Y'get no argument from me on that one.

wrote...
gt;quot;Harlan Grovequot; wrote:wrote...
gt;gt;gt;=MROUND($W11-$N11,4*SIGN($W11-$N11))
gt;gt;
gt;gt;Probably better to change it to
gt;gt;=SIGN($W11-$N11)*MROUND(ABS($W11-$N11),4)
gt;
gt;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)