Setting Excel to only allow natural numbers

[JTzara]

Hi All

I have completed a spreadsheet with many interdependent cells, but I want to set it so that all negative numbers are evaluated as (i.e. do not only _appear_ as) 0.

I know how to do this for a single cell, using a formula like

=IF(A1>=0,A1,0)

But I have a lot of cells to reformat, and don't want to do them one at a time!

Any ideas?

Thanks

[madadd]

Maybe someone else has a better solution....but I'd suggest making another column. Insert a new column next to the one you're evaluating and then use an if statement. You can evaluate that new column (maybe a sum or whatever you're wanting all non-negatives for) and then hide the column.

There are lots of times where I have to create entire arrays of data way off in like the AA-AZ columns, but I just hide them.

[TechRetard]

not sure what your spreadsheet looks like...


can you replace all "-" with nothing?

or...


put -1 in any cell and copy it
then select all your negative numbers (assuming this isn't too hard), edit, paste special, select values and multiple, hit ok

[6StringJazzer]

The only way to make them actually be 0 (and not merely appear as 0) is to add a column and use a formula like the one you've shown.

If you have numbers all over the place, not just in one column, create a new worksheet and put the formula there. For example, in Sheet2!A1 put

=MAX(0,Sheet1!A1)

and copy the formula throughout Sheet2 to match the entire range of cells used in Sheet1.