Making Negative Results Default to Zero

[jcoleman52]

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.

[JE McGimpsey]

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