Help

Calculating '0' - how NOT to consider it a blank?

Topic Labels: Formulas
Solved
Jump to Solution
1987 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Omer_Nesher
6 - Interface Innovator
6 - Interface Innovator

Hey all,

I’m tracking scores of sports events.
The main fields are “Home Score” (for how much the home team scored) and “Away Score” (you guessed it…)

In soccer matches it’s common for a team to score nothing. i.e. “0”.

I’m calculating:
IF(AND({Home Score}, {Away Score}), IF({Home Score} = {Away Score}, “Draw”, IF({Home Score} > {Away Score}, {Home Team}, {Away Team})))

The issue is, if I input “0” Airtable ignores it. it’s a blank for calculating consideration.

So, field {winning team} remains empty.
The workaround is to input a “1” score in case of actual “0” and to give the other team a +1 on their actual score.
i.e. if a game’s score is actually 0:1 I’d input it as 1:2 .

Is there a way around it? I want to preserve the information integrity.

Thanks.

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

This is one of the rare situations where the BLANK() function comes in handy. Formatting your formula like this should work:

IF(AND({Home Score} != BLANK(), {Away Score} != BLANK()), IF({Home Score} = {Away Score}, “Draw”, IF({Home Score} > {Away Score}, {Home Team}, {Away Team})))

With this change, a zero for either the home or away scores will be treated correctly.

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

This is one of the rare situations where the BLANK() function comes in handy. Formatting your formula like this should work:

IF(AND({Home Score} != BLANK(), {Away Score} != BLANK()), IF({Home Score} = {Away Score}, “Draw”, IF({Home Score} > {Away Score}, {Home Team}, {Away Team})))

With this change, a zero for either the home or away scores will be treated correctly.

Thanks!!! I really appreciate this.