You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

BLANK CELLS DISRUPT FORMULA

I HAVE A SPREADSHEET CALCULATING FORMULAS FOR BUDGETS. IF WE CLOSE CERTAIN DAYS(SAY SAT/SUN) THE FORMULAS HAVE NOTHING AND RETURN "ERRORS" BECAUSE THERE ARE NO VALUES IN THE CELLS.


IS THERE A WAY I CAN DO SOMETHING IN THE CELLS OR FORMUAL CELLS TO STOP THIS FROM HAPPENING. HERE IN THSI EXAMPLE..THE 2ND WE WERE CLOSED BUT BECAUSE NO DATA WAS ADDED IT RETURNS ERRORS IN COLUMN F

MacBook Air 13″, macOS 10.15

Posted on Dec 5, 2020 1:12 PM

Reply
Question marked as Top-ranking reply

Posted on Dec 5, 2020 2:19 PM

There are multiple ways around it. It may just be one or two of your formulas that will need it. For instance, the first error for the percentage appears to be a divide by zero error.


One way is to use IFERROR. Another is to use IF. I cannot give you formulas that match your spreadsheet because I don't know your formulas but here are examples:


Original =(F3+F5)/F4

Revised =IFERROR((F3+F5)/F4,"")

or

Revised =IFERROR((F3+F5)/F4, 0)


Original =(F3+F5)/F4

Revised =IF(F4<>0, (F3+F5)/F4, "")

or

Revised =IF(F4<>0, (F3+F5)/F4, 0)


I provided two possibilities for each because sometimes it is better to default to a zero instead of a null string "". If your downstream formulas try to do math using a null string, they will also be errors. But if you want the column to look blank when "closed", use the null string and revise all the formulas that need it.


Similar questions

5 replies
Question marked as Top-ranking reply

Dec 5, 2020 2:19 PM in response to saffala

There are multiple ways around it. It may just be one or two of your formulas that will need it. For instance, the first error for the percentage appears to be a divide by zero error.


One way is to use IFERROR. Another is to use IF. I cannot give you formulas that match your spreadsheet because I don't know your formulas but here are examples:


Original =(F3+F5)/F4

Revised =IFERROR((F3+F5)/F4,"")

or

Revised =IFERROR((F3+F5)/F4, 0)


Original =(F3+F5)/F4

Revised =IF(F4<>0, (F3+F5)/F4, "")

or

Revised =IF(F4<>0, (F3+F5)/F4, 0)


I provided two possibilities for each because sometimes it is better to default to a zero instead of a null string "". If your downstream formulas try to do math using a null string, they will also be errors. But if you want the column to look blank when "closed", use the null string and revise all the formulas that need it.


BLANK CELLS DISRUPT FORMULA

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.