Forum Discussion
Marshall1210
Aug 01, 2023Copper Contributor
Help with Blank or Zero if no Data for Formula.
Hello I have attached a file so that someone can see what I'm referring to.
I have two tables, the one on the left has values in the red column from data I place in the green row. The table on the right has values in the red column even though I have not entered data in the green row.
Can I get the red column to show a zero or blank if no data is entered in the grow row.
I think my problem is I have to make make multiple cells to formulate the end value I'm looking for and I'm sure most all of you can get the same value with one formula string in one cell.
Either way what I would like is all zeros or all Blank in the Red column when no Data is entered in the Green row.
Thanks for any help and all the help the community has given me in the past!
=IF(AND(ISBLANK(N4),ISBLANK(P4)),"",ROUNDUP(O12,0))
You can try formulas like this which check if the corresponding cells in row 4 are blank.
- mtarlerSilver Contributor
Marshall1210 So what I think you want is to add an IF statement around the formula like this:
=IF(Q$4="","",ROUNDUP(O6,0))
The $ before the 4 means always use row 4 even if you copy it to another cell but without the $ it means when you copy the formula adjust that value to be relative (if you copy 1 column to the right the Q will become R because there is no $ before it)
FYI - I highly recommend avoiding all those merged cells. you can change row and column heights/widths and you can use a neat trick to center text across multiple cells using: highlight the cells and go to cell formatting and under horizontal placement the drop down has an option to center across selection. This will not "merge" the cell but only center text across the selection. By merging you will have issues with trying to copy or fill or many other potential options.
- OliverScheurichGold Contributor
=IF(AND(ISBLANK(N4),ISBLANK(P4)),"",ROUNDUP(O12,0))
You can try formulas like this which check if the corresponding cells in row 4 are blank.
- TerryECCopper ContributorA quick and dirty way could also be (taking Shingles as an example)
=IF(COUNTBLANK(M4:V5)=20;0;ROUNDUP(O6;0))
But do you want it to be 0 when EVERY column on the green row is empty, or when ANY is ?