Nested IF Statements with Max

AppSheet AppSheet Q&A
1

I am trying to create an objective risk calculator and assigning a score to each response. There is also a maximum score that each question could receive. The scores will then be populated and a risk level assigned.

  1. Network Element Impacted (Select all applicable): (Max 15 points)
  • Transport (15)
  • Core (15)
  • Peering (10)
  • Distribution (5)
  • Site (3)
  • Access (1)

*If transport score is 15, if core score is 15, if peering score is 10, if distribution score is 5, if site score is 3, if access score is 1.
*Some questions may have more than one response that could be selected, so I would want multiple answers recorded where applicable, and also the maximum points assigned to be 15.

Thank you for your help.

2

Assuming you have a table (ELEMENT_POINTS) with fields

  • ELEMENT (key with descriptive text)
  • POINT(number of points corresponding to the ELEMENT),

and each question has (edited below to include max points)

  • ENUMLIST field with reference to the ELEMENT_POINTS table
  • MAX_POINTS

then, the score for each question can be calculated with

MIN(
 SUM(
  SELECT(
   ELEMENT_POINTS[point],
   IN([ELEMENT],[_THISROW].[ELEMENT ENUMLIST])
  )
 ),
 [MAX_POINTS]
)
1 Like