Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here
Apr 25, 2022 07:21 AM
Hello all
I have a single formula field called Threat Score
I have multiple single select fields with answers of Yes,No, Na.
I am trying to have a final threat score after all the single select fields are answered.
For example
Switch({Is there leaves, needles or other vegetation on roofs, gutters, decks, porches, stairways, etc.?},
‘Yes’, 0,
‘No’, 4,
'Na’, 4
)
Switch({Is there any dead and dying trees, branches and shrubs or other plants adjacent to or overhanging buildings?},
‘Yes’, 0,
‘No’, 4
'Na’, 4
)
If the user chooses no for both fields the formula field should have a sum of 8.
How would I combine multiple fields into a single formula to come up with a final sum.
Thank you
Josh
Solved! Go to Solution.
Apr 25, 2022 11:42 AM
Hi @WildfireSnow
Here is a formula to sum the two switch statements
SUM(SWITCH(
{Is there leaves, needles or other vegetation on roofs, gutters, decks, porches, stairways, etc.},
'Yes','0',
'No','4',
'NA','4',
''
)+SWITCH(
{Is there any dead and dying trees, branches and shrubs or other plants adjacent to or overhanging buildings?},
'Yes','0',
'No', '4',
'NA', '4',
''
))
When building out complex formulas it is easier to break it down into a formula for each item, and then combine into one formula once you have your desired result.
If this case I make only the first switch statement. then another formula with the second switch, then a final formula where I sum the two previous formulas. Once that works copy the formula from the first one and replace the reference name in the final formula, repeat. Then you can delete the unnecessary formulas
Apr 25, 2022 11:42 AM
Hi @WildfireSnow
Here is a formula to sum the two switch statements
SUM(SWITCH(
{Is there leaves, needles or other vegetation on roofs, gutters, decks, porches, stairways, etc.},
'Yes','0',
'No','4',
'NA','4',
''
)+SWITCH(
{Is there any dead and dying trees, branches and shrubs or other plants adjacent to or overhanging buildings?},
'Yes','0',
'No', '4',
'NA', '4',
''
))
When building out complex formulas it is easier to break it down into a formula for each item, and then combine into one formula once you have your desired result.
If this case I make only the first switch statement. then another formula with the second switch, then a final formula where I sum the two previous formulas. Once that works copy the formula from the first one and replace the reference name in the final formula, repeat. Then you can delete the unnecessary formulas
Apr 25, 2022 07:03 PM
Thanks Vivid! You rock. That’s the ticket!