Formula to output a "0" when Column 'x' is empty

Platform discussions
,
1

Dear Community,

I’m trying to make a formula work and it keeps giving me an error.

What I’m trying to achieve is to make the formula output a “0” when the column {aantal} is empty/null and when the column is not empty perform a calculation.

The formula is as follows:
IF({Aantal}=“0”, “”,
IF({Volt} = “230”,
({Vermogen per aansluiting} * {Aantal}) / {PF}, IF({Volt} = “400”,
({Vermogen per aansluiting} * {Aantal}) / (sqrt(3) * {PF})
)))

Scherm­afbeelding 2024-05-05 om 17.03.20
Scherm­afbeelding 2024-05-05 om 17.03.201562×236 17.8 KB

As you can see the formula works when there’s data in the given columns, but when I want the KVA column to be 0, it gives me an error.

Thanks for any help in advance!

2

Hi Joey,

The problem is with {PF}: you can’t divide by {PF} when {PF}=0. I know you believe that you took care of this by having the IF({Aantal}=0 condition but this is not the case. If you want to understand why, look at this article: A Simple Way to Handle Empty Columns and Avoid Errors in your Formulas | mdBoosters Blog (the explanation is in the paragraph titled “Eager vs Lazy Approach in IF Statements”).

It is not always easy to find a workaround, but in this case, it is relatively simple: replace {pf} by IF({pf}>0,{pf},1).
{pf}>0 will handle {pf}=0 or {pf} is empty.

IF({Aantal}="0", "",
IF({Volt} = "230",
({Vermogen per aansluiting} * {Aantal}) / IF({PF}>0,{PF},1), IF({Volt} = "400",
({Vermogen per aansluiting} * {Aantal}) / (sqrt(3) * IF({PF}>0,{PF},1))
)))

Hope it helps.

Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

  • Create formulas without using the Formula column (and avoid its limitations)
  • Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.

1 Like
3

Thanks for the quick fix!

That indeed eliminates the problem I had!
I will definitely read the article!

I got a pretty complicated board that’s running a LOT of power calculations. The article will help for sure!

Thanks again!