I don't want to se any zeros in my spreadsheet in Numbers

Question

Jew2 Author

Level 1

8 points

I don't want to se any zeros in my spreadsheet in Numbers

If the sum of an argument is zero, I want to see an empty field, not the number zero. Any suggestions?

Mac mini, macOS 13.5

Posted on Sep 23, 2023 9:09 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

28,636 points

Sep 23, 2023 1:32 PM in response to Jew2

You have changed the question and that changes the answer. To answer your new question, and assuming "nothing" means a cell is blank:

Say that the three numbers to be added are in cells B2, B3, and B4.

One formula would be =IF(OR(B2="",B3="",B4=""),"", B2+B3+B4)

This one requires all three cells to have numbers before you get any answer

Another formula would be =IF(AND(B2="",B3="",B4=""),"", B2+B3+B4)

This one will give an answer if any one or more of the cells contains a number

If you are actually adding more than 3 cells, at some point expanding this formula gets unwieldy and a different formula would be better.

Reply

Answer 2

Jew2 Author

Level 1

8 points

Sep 23, 2023 11:01 AM in response to Badunit

Thank You Badunit but I'm a bit new to formula collections in Numbers so I probably don't understand what you mean. I'll clarify a bit: nothing+nothing+nothing=nothing but 5+5-10=0 that should say zero.

Could it be added to the formula that, when something is written in a certain field, the SUM command is activated?

Reply

Answer 3

Badunit

Level 7

28,636 points

Sep 23, 2023 5:55 PM in response to Jew2

Here is another method if you mean you are summing 98 cells in one formula, not 14 formulas each doing a row of 7 cells:

This one will give a result as soon as one cell in the range is filled in

=IF(COUNT(B5:H18)=0,"",SUM(B5:H18))

And this one requires them all to be filled in before it gives a result

=IF(COUNT(B5:H18)=ROWS(B5:H18)×COLUMNS(B5:H18),SUM(B5:H18),"")

or

=IF(COUNT(B5:H18)=98,SUM(B5:H18),"")

You can use this same general idea if you have 14 different formulas each summing the 7 cells from its row. Just change the ranges in the formulas to be the 7 cells of the current row (and you won't need the ROWS function in the one formula).

Reply

Answer 4

Jew2 Author

Level 1

8 points

Sep 24, 2023 5:04 AM in response to Badunit

Thanks, this first code was much easier to use and it works perfectly in all my calculations in different variations.

In my case, it was:

The sum of each row is in column I.

The sum of I5-I18 is in I19 but I couldn't use that, I got an error which I did not have the knowledge to analyze.

I had to take the sum of B5-H18 instead in I19.

But now with the new code i could use the sum of I5-I18 to I19.

It's a whole new and exciting world that opens up with coding in "Numbers".

The risk is that I become a bit "nerdy".

What I'm working with in this case is a work schedule where each row is an employee and the 7 cells in the row are days of the week for a week. It would be nice if I could go ahead with that if a cell is filled in with a number greater than 8 eg 11 then the number 3 would go to column J (overtime compensation) but if it is filled in numbers in cell Saturday or Sunday (column G or H) it goes to column K (overtime compensation weekend).

I'll see if I can figure it out. Thank you.

Reply

Answer 5

SGIII

Level 8

36,146 points

Sep 23, 2023 8:19 PM in response to Jew2

Jew2 wrote:

I want to see an empty field, not the number zero.

Consider creating a Custom Format. That way you don't need to touch your formulas.

Result:

SG

Reply

Answer 6

Badunit

Level 7

28,636 points

Sep 23, 2023 9:18 AM in response to Jew2

Create a custom format

Reply

Answer 7

Jew2 Author

Level 1

8 points

Sep 23, 2023 4:11 PM in response to Badunit

Yes, yes, Thank You Badunit. You made my day but it was a huge number of codes. I have B5-H5 to B18-H18. 98 cells! Thank You again. I used the second advise.

Reply

Answer 8

Jew2 Author

Level 1

8 points

Sep 24, 2023 4:16 AM in response to SGIII

Thanks SGIII, now I understand how "creating a Custom Format" is used but since my question was a bit incomplete I can't use that command in this particular case, but thanks anyway.

Reply

I don't want to se any zeros in my spreadsheet in Numbers

Similar questions