Make a cell in Numbers register zero

Question

Level 1

8 points

Make a cell in Numbers register zero

Hi all I have a formula that is running in cells that will only populate when the data is entered in a nother cell.

There is a lot of calculations running in other tables which is taking these empty cells into consideration but because there is an error the cells are showing errors.

Is there a way of saying:

If there is nothing entered into a cell then dont run a calculation on all other cells that relate to it.

Please see screen shot to help explain this better.

So If i enter a name into A11 it bring up the data and populate across the table - but when nothing is entered it is still looking for the data so showing the red box - this effects other tables as well so basically need it to show zero.

Thanks for your help

MacBook Pro 15", macOS 10.14

Posted on Jun 8, 2019 3:01 AM

Reply

Answer 1

Best reply

SGIII

Level 8

35,462 points

Jun 8, 2019 11:52 AM in response to martynodell

If you're simply trying to hide the warning triangles in the table then one thing you can do is simply "wrap" your existing lookup formula in IFERROR. =IFERROR(<your original formula>,"")

Or if you need 0's in B thru G you could use =IFERROR(<your original formula>,0). Then use a Custom Format to "hide" the zeros.

Whether that is a good solution, though, depends on knowing more about how your calculations work.

SG

Reply

Answer 2

martynodell Author

Level 1

8 points

Jun 8, 2019 10:17 AM in response to Yellowbox

Hello @yellowbox

So my best explanation is this:

I have got the box in Row A to work that when i type in a name of an ingredient it auto populates the whole row which then does the maths.

This table talks to the ingredients table on a different sheet.

Hope this makes sense?!?! Pretty new on Numbers so may be doing something very backwards

Reply

Answer 3

Barry

Level 9

59,885 points

Jun 8, 2019 10:37 AM in response to martynodell

Hi Marty,

"If there is nothing entered into a cell then dont run a calculation on all other cells that relate to it."

A formula controls its own actions and the result of those actions. It cannot (directly) control actions of 'all other cells that relate to it,' but it can control what those downstream cells and their formulas find when they reference the cell containing the formula.

Wrapping your B3 formula in an IF statement can provide a switch that places a specific value in B3 if A3 is 'empty' and calculates the value shown in your example if A3 contains "apple" (or a different value is A3 contains a value other than "apple".

IF(LEN($A3)<1,"",formula)

where formula is the formula you currently have in B3.

LEN returns the LENgth (measured in characters) of the value in A3. IF the lenght is less that 1, IF returns a null string (a text value, with a length of zero). Replace "" with 0 (zero) if you want those cells to show 0 rather than appear empty.

Note that text values, including "", will be ignored by some functions (eg. SUM, MAX, AVERAGE, COUNT), but will cause an error if presented to one of the arithmetic operators (+,-,*,/). AVERAGE will include 0 in a list of numbers to be averaged, as will COUNT in a list to be counted.

Regards,

Barry

Reply

Answer 4

martynodell Author

Level 1

8 points

Jun 8, 2019 10:47 AM in response to Barry

Hi Barry,

Thanks for your reply! I have a very basic understanding of Numbers and what you wrote I really do not understand.

Maybe if I Explain my initial intentions it may be better than trying to polish my code that I have attempted.

On my table I want

Collumn A - which will only ever have ingredients names to be a type box so I can write for example Apple.

(This is why I used the look up formula)

And it will auto populate the same row with data from another table which holds all of the data. So Collumn B-G will be populated from another table holding the data.

The reason there will be some blank spaces is because I want to be able to type in an ingredient and the maths still works for other tables.

So i f i can get the unpopulated boxes to read 0 then all of the maths will function. But at the minute with my code it is searching for a number which isnt there.

Hope that is a little clearer?

Thanks again

Martyn

Reply

Answer 5

Yellowbox

Level 7

20,471 points

Jun 8, 2019 9:05 AM in response to martynodell

Hi Marty,

What formula in column B? Tell us the formula in B3 to help us help you.

Regards,

Ian.

Reply

Answer 6

martynodell Author

Level 1

8 points

Jun 8, 2019 1:19 PM in response to SGIII

Thankyouuuuuuuu so much!

That worked a treat! I was doing the right thing but putting all the brackets in the wrong place.

Thankyou

Reply

Make a cell in Numbers register zero

Similar questions