Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
abdualmohsn | Excel Discussion (Misc queries) | |||
Negative Results | Excel Discussion (Misc queries) | |||
with formulas that show negative results I want to show zero inste | Excel Discussion (Misc queries) | |||
problem:search results negative in excel docs with macros | Excel Discussion (Misc queries) | |||
Track Changes: Making "All" the default | Excel Discussion (Misc queries) |