Wednesday, November 21, 2012

How to Make Negative Numbers = 0 in Excel

In Formulas
1. Write a formula to evaluate data. For example, if you're subtracting A1 from B1 and showing the results in C1, type '=(B1-A1)', minus the quotes, into cell C1.
2. Add 'MAX' to the formula to designate the maximum answer allowed: '=MAX(B1-A1)'. This is only part of the formula; if you press 'Enter' at this point, you will get an error message.
3. Insert the amount for the maximum number allowed as the formula result -- in this case, zero: '=MAX(0,B1-A1)'. Any results that would normally be negative become 0, not just as display text, but as value. If the formula result of B1-A1 is -2, once you use the MAX function, it actually becomes zero, and if you add 5 to it, the result will be 5, not 3.
4. Enter all data as usual, including negative numbers, or open a worksheet containing the data you want to use. Select all the cells you want to display only as positive numbers or zeros.
5. Click on the number format drop-down and choose 'More Number Formats....'
6. Choose 'Custom' in the left-hand pane.
7. Type '##;'0';0', without the outer quotation marks, into the field labeled 'Type:', overwriting any symbols already in that field. Click 'OK' to return to the spreadsheet, where all negative numbers will now display instead as '0' while retaining their actual values. If a cell value is -2, it will display as 0, but if you add 5 to it, it will become 3, not 5.

Blogger news