Formula nesting

=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 N11>W11 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)))

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" <[email protected]> wrote in message
news:[email protected]...
> =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 N11>W11 give me
> ZERO as will as all the above.
>
> Help! Bonnie

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

[email protected] 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:
> [email protected] 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.

[email protected] wrote...
>"Harlan Grove" wrote:
>>[email protected] 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)