How do I disallow negative numbers in a selected group of cells (i.e. only allow values greater than or equal to zero)?

Nicholas,

The way I read your question, the answer is "you can't. Each cell displays whatever value is calculated by the formula within that cell. Formatting only changes how that value is displayed, but can't change the value.

A simple Expression will do the action that you want, but it will have to reside in each and every cell where you want this to happen. My current favorite way to prevent values from going negative is the following:

=MAX(Your_calculation, 0)

If your_calculation is less than zero, zero will be displayed.

Jerry

Maybe there's another way to solve your problem. Can you modify the expressions that use the data/calculations in your table rather than change the entire table?

Jerry

Hi Nicholas,

"Conditional Formatting" does exactly what its name implies—it sets the format in which a value is displayed to a particular format depending on a stated condition being met. It does nothing more or less than that.

To change the value itself, you will need to make a change to the formula that generates that value.

If the values for which you want to set zero as the minimum are all created by the same formula, filled down a column, then there's very little tedium involved in editing the first example to enclose it in MAX(0,formula) then fill it down the column.

An alternative, where is a variety of formulas creating those values, would be to do the 'conversion' in a separate column, replacing formula above with a reference to the cell containing the original calculated value.

A third possibility is to leave that set of calculated values as they are, allowing them display as negative values where that's the result, and to do the adjustment/correction in the formulas that reference the cells containing those values. Depending on the formula(s) referencing the cell, you might be able to use Find/Replace with a small enough set of runs to make this an efficient method.

MAX does appear to be the most efficient tool to use for this process. how to apply that tool depends on the details of your situation.

Regards,

Barry

Nicholas,

Let's assume that your data is in a separate table named "Data", and the the table "Data" has one Header Row, no Header Column and no Footer Row.

The most efficient solution from the point of view of your time to implement would be to Duplicate that Data table, creating a new table of just the right size. Then write this expression in A2 of your new table:

=MAX(Data::A2, 0)

Fill this expression Down and Across so that every Body Cell in the new table has this same expression, undusted for its relative address.

Now apply your long, animal-modeling, expressions to this new, interposing, table rather than the original.

Regards,

Jerry

A step toward simplifying the application of MAX to the issue, Jerry.

This part, though:

Now apply your long, animal-modeling, expressions to this new, interposing, table rather than the original.

may still leave several references to be change from the original data table to the new one.

One way to get around that is to use the Duplicate ("DATA-1) as the new table for raw data, and the Original (DATA) as the interposing table, using the formula =MAX(DATA-1::A2) as above, starting in DATA::A2.

This way, the long expressions could continue to reference the original table (with its content now modified).

ALTERNATE process:

Find/Replace could also be used to speed the process of reassigning the modeling expressions to the duplicate table, as suggested by Jerry. But some cautions apply here.

Find/Replace can be limited to Formulas only, but not to Some formulas only.

Find/Replace can be limited to the Current Sheet only, but this can't be combined with Formulas only.

More on this later, when I've had a chance to check some possibilities.

Regards,

Barry