I understand this is a known issue, but I’ve read several threads on the topic and tried a few solutions but still can’t figure out how to fix this.
I have two fields of scores, one from a 2020 publication and one from an upcoming 2022 publication. I am trying to be able to show if the score is an improvement, worsening, or the same. The issue being that the score “0” is an acceptable input and the formula is recognizing 0 as blank instead.
The formula is currently as follows:
IF(
{KI-1 2022} = BLANK(),
"",
IF(
{KI-1 2020} = BLANK(0),
"
",
IF({KI-1 2020} - {KI-1 2022} = 0,
"
",
IF({KI-1 2020} - {KI-1 2022} < 0,
"🟥",
IF({KI-1 2020} - {KI-1 2022} > 0,
"🟩", "")
)
)
)
)
I have tried COUNTA as well, but nothing changed:
*I tried both COUNTA()=COUNTA() and COUNTA()-COUNTA()=0 but neither scenario worked…
IF(
{KI-2 2022} = BLANK(),
"",
IF(
{KI-2 2020} = BLANK(0),
"",
IF(COUNTA({KI-2 2020}) - COUNTA({KI-2 2022}) = 0,
"",
IF({KI-2 2020} - {KI-2 2022} < 0,
"🟥",
IF({KI-2 2020} - {KI-2 2022} > 0,
"🟩", "")
)
)
)
)
My base currently looks like this:
As you can see, if both 2020 and 2022 scores are “0”, then the formula is blank. I’d like 0 & 0 to show a white square, while an empty 2022 shows an empty cell, signifying that it is waiting for the score to be input… Maybe I’m just not understanding properly how COUNTA() works?
