Calculate Percentage, or Leave Blank if Both Column Counts Are 0

I have a percentage formula that works perfectly, except when both columns are empty/the values are 0. When both Published and Rejected are 0, I want the column to be blank rather than show an error.

Here’s the formula that works: CONCATENATE(ROUNDUP((((DIVIDE({Published#Count},SUM({Published#Count},{Rejected#Count}))))*100),1),“”,“%”)

My attempt at an IF formula for this:
IF(AND({Published#Count}=0,{Rejected#Count}=0, " “), DIVIDE((({Published#Count},SUM({Published#Count},{Rejected#Count}))*100),1),”%")

Hey Megan!

Would you be happy to share a screenshot of the columns in your board that are included in your formula? This way we can see the column type and test this on our end? :pray:

Hi Meagan,

The problem with your formula (and why you’re getting errors when the values are 0) is that you’re dividing {Published#Count} by the sum of {Published#Count} and {Rejected#Count}. When both are at 0, the sum is 0, therefore you’re dividing by 0 which is an illegal operation and you get the infamous red icon.

You may think that by using an IF statement checking first that both columns are not 0 you will avoid the problem but, I know this may not seem intuitive, that doesn’t work.

Why?

When you use IF(condition,result_if_cond_true,result_if_cond_false), intuitively you would think that, in the background, monday checks the condition and only calculates result_if_cond_true if the condition is true (and forget about result_if_cond_false, why calculate it, right?), but that’s not the case.

When processing IF statements monday will always calculate result_if_cond_true and result_if_cond_false and return the correct one.

So what is the solution?

You need to make sure that both results can be calculated. In this case, try this syntax (I removed your formatting functions so that it is easier to understand).

DIVIDE({Published#Count},IF(SUM({Published#Count},{Rejected#Count})=0,“1”,SUM({Published#Count},{Rejected#Count})))

The IF statement is nested in the DIVIDE function to prevent dividing by 0. So it checks the sum, if it is 0, it divides by 1, not by 0. If it is not 0, it divides by the sum.

I can’t help saying that by using the Advanced Formula Booster app, it would be a lot simpler to write such a formula :slight_smile:


What if we could break free of the Formula column and write formulas that update any type of columns? What if a formula could update multiple columns at once? This is possible with the Advanced Formula Booster app.

2 Likes

Sure! Acceptance Rate is the formula column, Published and Rejected are both “Connect boards” columns which I’m wanting to count the total number of items added to each.

This formula returns only an error message for me, sadly.

If you copied and pasted, make sure to replace the double-quotes with straight double-quotes

1 Like

Thank you! That did it. I still had to add in my percentage formatting, but it works perfectly now.

CONCATENATE(ROUNDUP(DIVIDE({Published#Count},IF(SUM({Published#Count},{Rejected#Count})=0,“1”,SUM({Published#Count},{Rejected#Count})))*100,1),“”,“%”)

1 Like

Gilles, would you happen to know how to make this formula return “N/A” if the sum of Published and Rejected is 0?

CONCATENATE(ROUNDUP(DIVIDE({Published#Count},IF(SUM({Published#Count},{Rejected#Count})=0,“1”,SUM({Published#Count},{Rejected#Count})))*100,1),“”,“%”)

Thanks so much for your help on this Gilles :muscle:

2 Likes

Hi Meagan,

Glad it worked. To display N/A. simply encapsulate your current formula with:

IF(SUM({Published#Count},{Rejected#Count})=0,“N/A”,
)

like this:

IF(SUM({Published#Count},{Rejected#Count})=0,“N/A”,CONCATENATE(ROUNDUP(DIVIDE({Published#Count},IF(SUM({Published#Count},{Rejected#Count})=0,“1”,SUM({Published#Count},{Rejected#Count})))*100,1),“”,“%”))

Again, check the double-quotes after pasting.


Want to get rid of the formula column and write formulas that support multiple lines, variables and can update various columns at once? Take a look at the Advanced Formula Booster at https://mdboosters.com

1 Like

Perfect! Thank you again!

1 Like