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
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.
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.
Hi Barry,
Thanks for this. this is the original formula
SUM(E201−E203)÷E202 it will not allow this new formula because I already have a parenthesis
Not so.
=IFERROR(SUM(E201−E203)/E202,"")
or
=IF(E202<>0,SUM(E201−E203)/E202,"")
I should point out that you are using SUM for no reason.
SUM(E201-E203)/E202 is the same as (E201-E203)/E202
thanks so much.
BLANK CELLS DISRUPT FORMULA
