I can think of ways to do this using an “IF” statement but my spreadsheet already exists and I would need to construct the IF statement for lots and lots of cells which (trust me) is not as easy as dragging the copy box. The IF statement would definitely work but be a serious hassle to do.
So, wondering if there is a function to make negative numbers be zero. Not display as zero but make the result zero so follow on calculations work out correctly. Following is an example of my cell formula as it currently is:
=Orders!$G4*Data!H4
That formula is copied both down and across making changes…difficult.
Not seeing that function on a search (using Excel 2007). See MAX and MAXA which look like they find the highest value in a list. After that I get MDETERM function on an alphabetical list so no MAXIMUM to be found.
I’d say the same - use the max function.
OTOH, hide the cells you reference, and just replicate the =MAXIMUM(cell, 0) in a new range of cells.
Hiding intermediate results is a time-honoured spreadsheet tradition; by making the hidden cells visible you can verify intermediate results for debugging.
It’s a two number list: the list contains the number you care about, along with 0. MAX(x, 0) is the biggest number in the list [x, 0]. If x is positive, it’s the biggest thing in the list. If it’s negative, then the 0 is the biggest thing in the list.
The OP specifies that IF statements won’t do for his purposes (though I didn’t quite understand his reason why).
Looks like the question’s been answered with the suggestion to use MAX().
My own idea (not being familiar with Excel in particular) would have been to do something like “(X + ABS(X)) / 2”, however you would format that in Excel-speak.
Well, the cells being referenced in the formula do not smoothly increment cell-to-cell. It is a grid with items randomly scattered across it. Some items need A1+B1, the next may need A1+C4, the next D2+E12 and so on.
So it sounds like the problem is you don’t want to create new cells to apply the extra step, is that right? If so, can’t you just incorporate one of the suggestions above into your main formula?
You can also create a custom formatting expression, as long as you don’t plan on relying on the values of the negative cells any further. Then you don’t need to create another cell to convert negatives.
Custom formatting expressions have sections to handle positive, negative and zero values. Right click on the cell that has the negative values you’d like to affect, select format cells. Then click on the number tab and select custom. Your custom formatting expression will be entered in the box at the top.
If your numbers are decimals with say 2 digits of precision after the decimal I would enter: 0.00;“0”
It has to be entered as a custom formatting expression exactly like that
That formatting expression gives you two displayed decimals for positive numbers, but negatives will simply show zero. The ACTUAL value in the cell is still negative but the formatting shows 0.
You can adjust the part of that expression before the semicolor for decimals or whatever format you are using. I’ve used custom formatting for displaying units of measure and all kinds of crazy things and it can be pretty handy.
