Another zero and blank issue - calculating if a score has improved, worsened or stayed the same

[Layne]

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?

[augmented · Accepted solution]

Hi Layne. When checking for BLANK against the numeric field, use {KI-2 2022}&"" to convert it to text. A zero will not be blank. Also, do you mean to have your red & green represent improvement and decline, respectively?

I mocked up your issue like this…

[Layne]

Thank you so much augmented! That worked like a charm.

And yes, a lower score is better in this case, so number score decrease = green and number score increase = red.

Again thank you very much!