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 N11W11 give me ZERO as will as all the above. Help! Bonnie |
Formula nesting
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 |
Formula nesting
=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 |
Formula nesting
"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. |
Formula nesting
|
Formula nesting
"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. |
Formula nesting
|
All times are GMT +1. The time now is 02:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com