I have encountered what appears to be a significant logical flaw in the calculation engine for custom formula fields, specifically concerning the handling of `NULL` (or blank) values in conditional arithmetic. After a detailed support ticket and escalation, the engineering team's final response was that the behavior is "by design," which I find to be a deeply unsatisfactory resolution that contradicts both mathematical convention and the principle of least surprise in system design.
The issue manifests in a formula intended to calculate a weighted score, where some components may be optional. Consider the following simplified example:
```
IF(AND({Component_A}, {Component_B}),
({Component_A} * 0.7) + ({Component_B} * 0.3),
BLANK()
)
```
The intuitive expectation is that if either `Component_A` or `Component_B` is blank, the `AND` condition fails, and the field returns blank. However, the actual observed behavior is that the formula proceeds to evaluate the arithmetic branch *even when the `AND` condition is false*, leading to erroneous calculations where a blank value is treated as zero in the subsequent multiplication and addition.
This results in nonsensical outputs such as:
* `Component_A` = 100, `Component_B` = `BLANK()` → Expected: `BLANK()`, Actual: **70**
* `Component_A` = `BLANK()`, `Component_B` = 100 → Expected: `BLANK()`, Actual: **30**
The support team's assertion that "all blank values are coerced to zero within numerical operations, regardless of enclosing conditional guards" is the cited design rationale. This creates a critical pitfall for any business logic that depends on conditional presence of data.
From a systems architecture perspective, this is a clear violation of expected evaluation semantics. The `IF` function's predicate should act as a guard clause, and its dependent expressions should only be evaluated *after* the predicate is resolved to `TRUE`. The current implementation appears to be performing a naive pre-processing substitution (blank → 0) across the entire formula parse tree before evaluating the conditional logic. This is reminiscent of early, poorly-optimized query planners that would evaluate scalar functions on all rows before applying `WHERE` clause filters.
I am seeking input from the community on two fronts:
1. **Workarounds:** Has anyone devised a robust pattern to enforce true conditional evaluation? My current, cumbersome solution involves nested redundancy:
```
IF(AND(NOT(ISBLANK({Component_A})), NOT(ISBLANK({Component_B}))),
IF(NOT(ISBLANK({Component_A})),
{Component_A} * 0.7,
0
) +
IF(NOT(ISBLANK({Component_B})),
{Component_B} * 0.3,
0
),
BLANK()
)
```
This is verbose, difficult to maintain, and computationally wasteful.
2. **Platform Implications:** If this evaluation model is indeed a core design tenet of Granola's formula engine, it raises concerns about the reliability of more complex logic involving `SWITCH()`, `CASE()`, or recursive functions. Are there other undocumented evaluation quirks the community has documented?
This behavior undermines data integrity for calculated fields. A platform positioning itself as enterprise-grade should adhere to strict, predictable evaluation models. I am compiling a formal benchmark to compare this behavior against other leading SaaS platforms; any anecdotal or tested comparisons would be valuable.
Oh wow, that's a classic one. The "by design" response for a broken-feeling conditional is so frustrating 😕
What you're describing is a lazy vs. eager evaluation problem. The engine seems to be calculating the entire formula first, then applying the IF, which is backwards. I've hit similar walls with scoring formulas.
Have you tried nesting the logic to force the check? Something like:
IF(NOT(ISBLANK({Component_A})), {Component_A} * 0.7, 0) + IF(NOT(ISBLANK({Component_B})), {Component_B} * 0.3, 0)
It's a messy workaround, but it might get you the right math while you keep pushing them on the core bug.
Docs save time
Exactly. The AND condition isn't short-circuiting. It evaluates the whole expression first, which is why your blank becomes a zero in the math. The workaround is to avoid the conditional branch for the calculation entirely.
Instead of wrapping the whole formula in an IF, build the result to handle blanks directly. This forces the engine to evaluate each component independently.
Try:
```
(IF({Component_A}, {Component_A}, 0) * 0.7) + (IF({Component_B}, {Component_B}, 0) * 0.3)
```
If both are blank, it'll return 0. If you need a true blank, wrap that entire thing in another IF to check for two blanks. Annoying, but it works.
YAML all the things.
Yeah, I've run into this exact "by design" wall before with scoring formulas. The core problem is that the AND function itself doesn't short-circuit - it evaluates its arguments, and a blank field in a numeric context gets coerced to zero *before* the AND even makes its decision.
Your example is perfect for showing why this is so counter-intuitive. The formula engine should, logically, see the false AND and skip to the BLANK() without ever touching the math. But it doesn't work that way.
A pattern I've used is to check for blanks first and treat them as optional by setting their weight to zero. So instead of IF(AND(...)), you'd structure it as:
(IF(NOT(ISBLANK({Component_A})), 0.7, 0) * {Component_A}) + (IF(NOT(ISBLANK({Component_B})), 0.3, 0) * {Component_B})
It's more verbose, but it forces the system to respect the blanks. You still have to wrap the whole thing in a final IF to return a true blank if both components are empty, but at least the math stays correct. Super frustrating when the simpler, cleaner logic is broken.
Clean data, happy life.
They're calling it a "core problem" with AND not short-circuiting because blanks turn to zero. That's letting the vendor off the hook. It's not a quirk of AND, it's a fundamental choice to evaluate *all* arguments in a function call before the function logic runs. A lazy, non-short-circuiting AND is just a badly implemented AND.
The "by design" excuse is the real issue. It's a design that breaks standard logical evaluation for easier implementation.
Prove it
This is exactly why I'm nervous about building anything complex with formulas now. If something this fundamental like an IF statement doesn't work the way every other system does, what else is going to break?
When you said they called it "by design," my immediate thought was: whose design? It feels like they prioritized making the engine simpler to code over making it usable for us. I'm just starting with this platform, and hearing that a core logical function behaves unexpectedly is really discouraging.
Do you think this is the same for the OR function too? If AND doesn't short-circuit, I'm guessing OR probably doesn't either. That seems like a huge trap for beginners.
Oh, I think I just ran into something similar last week. I was trying to build a simple lead scoring field and got a number when I expected a blank. It was really confusing.
So the whole IF statement gets evaluated even when the condition is false? That seems backwards. Does that mean any error in the "false" branch would still break the formula?
Yeah, that's exactly what it sounds like. If there's an error in the "false" part, the whole formula breaks, which is wild. It defeats the whole point of using IF as a safety check.
It's super discouraging as someone just starting out. You think you're building a failsafe, but the logic is backwards. I'm scared to build anything complex now too.
Has anyone found a platform that doesn't have this problem? I'm using Asana and Notion a lot, but I haven't tried their formula stuff yet.
The "principle of least surprise" is a generous way to put it. This is a straight-up bug. Their design is wrong.
You're treating the blank as an optional component, which is correct. Their engine is coercing null to zero before evaluating the IF condition. That's lazy evaluation, but it's also broken for any real-world conditional logic.
The support answer is a non-answer. A design can still be a bad design. You can't fix it with a better formula, only a more verbose and brittle one that works around their mistake.
Don't panic, have a rollback plan.
It's that classic vendor logic where "by design" means "we don't want to fix the underlying architectural choice." It reminds me of some CI/CD systems that would evaluate all pipeline steps for variables *before* execution, even if a step was skipped. You'd get weird failures because a variable only existed in a later job context, but the parser tried to resolve it globally.
>The support answer is a non-answer.
100%. It's a design that makes their implementation easier, but it breaks the intuitive contract with anyone writing formulas. Like you said, you can't fix it, only bury it in verbose guards and hope the next person understands the workaround.
pipeline all the things
Yeah, that's exactly the kind of gotcha that makes you tear your hair out. I've seen this same pattern in other systems where they evaluate the entire IF statement's potential return values upfront, instead of lazily evaluating only the branch that matches the condition.
Your weighted score example is perfect because it shows the practical impact - a blank shouldn't magically become a zero in your weighted average. That's introducing data that doesn't exist.
It's a real shame they're hiding behind "by design" on this one. It feels like an architectural decision they can't or won't unwind, probably because the formula engine parses and resolves all field references in a single first pass before applying any logic. It's the classic trade-off: simpler, faster evaluation for them versus intuitive, predictable behavior for the user.
— francesc
Exactly. That "parse first, evaluate later" approach makes sense for performance, but it breaks the abstraction. If I can't trust IF to work, I have to mentally compile the entire formula tree before writing it.
I've actually built a linter for my team that flags these unsafe patterns in our formulas now. It catches things like unguarded math inside IF branches. The annoying part is we have to write *more* code to get *less* functionality.
You mentioned other platforms - Asana's formulas are more predictable in my experience, but way less powerful. It's always a trade-off.
Clean code is not an option, it's a sanity measure.
That's a fascinating point about building a linter as a defensive measure. It speaks to a community developing its own tooling to compensate for a platform's unexpected behavior.
Your observation about the trade-off between predictable but less powerful formulas and more powerful but unpredictable ones is key. It often feels like platforms either offer a simple, well-defined sandbox or a complex engine with undocumented edge cases. The lack of a middle ground is frustrating, especially when you need both reliability and some advanced functionality.
I wonder if, in some cases, this "parse first" approach is a result of wanting formulas to be statically analyzable for other features, like dependency graphs or real-time validation. But if that's the case, it should be clearly documented so we can understand the constraints we're working within.
Let's keep it constructive
Oh, it absolutely defeats the safety check! I remember trying to guard a division operation with IF for a load average calculation in a monitoring setup, only to still get a divide-by-zero crash because it evaluated the other branch anyway. It's a facepalm moment for sure.
For your question on Asana and Notion - in my experience, Notion's formulas are simpler but generally behave as you'd expect. The trade-off is you can't do as much. Asana's are, honestly, pretty limited. If you're just starting out and need predictable logic, I'd stick with Notion for now to build your confidence. It avoids these kinds of deep traps.
It's the worst kind of bug because it breaks your mental model of how things should work. Makes you second-guess everything.
it worked on my machine
Yeah, that weighted score example perfectly illustrates the real data corruption this causes. A blank field shouldn't be interpreted as a zero in your weighted average - that's not just a logical quirk, it's inventing data.
This "parse first" design means you can't safely use the formula engine for any calculation where fields might be optional or dependent on a workflow state. It forces you into writing those verbose, brittle workarounds that are a nightmare to maintain later. So frustrating when they hide behind "by design" instead of just documenting the limitation clearly.
Trust the data, not the demo.