Making Negative Results Default to Zero
 

I have an Excel application involving several worksheets and numerous
lengthy formulas, with results that are passed on to subsequent
calculations in other sheets. Due to the nature of the data, any
results less than zero must be set to zero for calculations that
follow. I could of course put in each formula, IF(‘humongous formula’
< 0,0,’humongous formula’), but this would require considerable effort
across all the sheets, and there are other constraints as well, so it
would become an exercise in IF, ELSE, ELSE, ELSE…..etc. I was
wondering if there is a worksheet or workbook parameter that can be set
that will not allow values less than zero in any cell, so that if a
formula calculation yields a result less than zero, the cell is
automatically set to zero? Would there be a way to do it through VBA
language? Thanks.


--
jcoleman52
------------------------------------------------------------------------
jcoleman52's Profile: http://www.excelforum.com/member.php...o&userid=29498
View this thread: http://www.excelforum.com/showthread...hreadid=495495

Making Negative Results Default to Zero
 

First, you could use

=MAX(0,<humongous formula)

which has the benefit of only calculating the h.f. once. I don't know
how it would affect your other constraints.

If you replace formulae resulting in negative numbers with zeros using
VBA, you'll lose the formulae. In addition, it would likely be very
difficult to get the dependencies right if the formulae are
interdependent. I'm also not sure it would deal with your other
constraints.


In article ,
jcoleman52
wrote:

I have an Excel application involving several worksheets and numerous
lengthy formulas, with results that are passed on to subsequent
calculations in other sheets. Due to the nature of the data, any
results less than zero must be set to zero for calculations that
follow. I could of course put in each formula, IF(‘humongous formula’
< 0,0,’humongous formula’), but this would require considerable effort
across all the sheets, and there are other constraints as well, so it
would become an exercise in IF, ELSE, ELSE, ELSE…..etc. I was
wondering if there is a worksheet or workbook parameter that can be set
that will not allow values less than zero in any cell, so that if a
formula calculation yields a result less than zero, the cell is
automatically set to zero? Would there be a way to do it through VBA
language? Thanks.