Help

Adding Formulas to come up with final score

Topic Labels: Formulas
Solved
Jump to Solution
1382 2
cancel
Showing results for 
Search instead for 
Did you mean: 
WildfireSnow
4 - Data Explorer
4 - Data Explorer

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

1 Solution

Accepted Solutions
Vivid-Squid
11 - Venus
11 - Venus

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

See Solution in Thread

2 Replies 2
Vivid-Squid
11 - Venus
11 - Venus

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

WildfireSnow
4 - Data Explorer
4 - Data Explorer

Thanks Vivid! You rock. That’s the ticket!