0

I am using Apple Numbers and find it frustrating that cell arguments containing empty text (and sometimes contain numbers from other sheets) are not converted to a number of value 0, when referenced by a function that expects a numeric value.

For example, a function in cell:

=MAX(I5,0.5×J5)

with J5 being empty, yields an error message

The operator “*” expects a number but found “.”

So I tried to encase J5 with VALUE(J5), and got another message:

VALUE requires a string specifying a number, but found “” instead.

Is there an efficient way to treat all spaces as 0 for the countless cells that may have that situation?

1
IFERROR(VALUE(<cell>),0) 

would return 0 for an empty cell (or any string that can not be converted into a value). I tried something like:

= MAX (I5, (0.5 x (IFERROR(VALUE(J5),0))) 

and my initial tests were working.

Hope this helps

  • Although this may work, it is a rather long solution for every argument of a function in every cell that may have the empty string (not entered value) as a parameter. Still looking for a more global solution. – elomage Feb 24 '15 at 21:39
1

A further suggestion: Select the entire table and set the cell Data Format to "Automatic". Numbers will then determine the data type which may reduce import errors due to cell formatting. You can also force formatting in the same manner.

Cell Format Setting in Inspector

I pasted your formula with these settings and got the following result:

Formula result

If you are uncomfortable/don't like/prefer, you can change the formula to text.

Formula editing

Text only formula

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .