Help

Scoring system where number of criteria scored is variable

Topic Labels: Data Formulas
Solved
Jump to Solution
269 4
cancel
Showing results for 
Search instead for 
Did you mean: 
annedoughertynm
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,

We have a scoring system that rates functionality in an interface. So as not to bias the total score, if the functionality isn't present we ask evaluators to give it a -1.

In Excel, the final score is determined by a formula that:

  1. Counts the number of criteria where the score is >= 0
  2. Determines a max possible score based on the number of criteria scored (e.g., there are 10 criteria in a category but only 9 of them got scored the max possible score is 9*3=27 (9 criteria scored * highest possible score))
  3. Adds the values for each of the criteria scored to get the actual total

I'm able to use sum to get the total score but I'm not sure how to get the number of criteria scored without using countif which Airtable doesn't seem to have.

Is there a way in AT to do what I'm trying to do? Any help is most welcomed.

 

1 Solution

Accepted Solutions

Ah!  Sorry, try this:

IF(
  {Score 1} > 0, 3
) + 
IF(
  {Score 2} > 0, 3
) + 
IF(
  {Score 3} > 0, 3
) 

Screenshot 2024-03-16 at 12.29.57 AM.png

See Solution in Thread

4 Replies 4

Hmm, what if you did a formula with a bunch of IFs in it?

Screenshot 2024-03-15 at 11.19.12 AM.png

IF(
  {Score 1}, 3
) + 
IF(
  {Score 2}, 3
) + 
IF(
  {Score 3}, 3
) 

If your data's set up differently could you provide a screenshot with some example data?

Hum...it's a good start and not working exactly the way I need. 

Formula in the Max Possible Score: VTG field is:

 
IF(
{5.1 Content uses voice principles appropriate for the audience}, 3
) +
IF(
{5.2 Content uses a tone that matches the user's situation}, 3
) +
IF(
{5.3 Content follows existing editorial standards}, 3
)

 

 

But it's not coming up with the right answer. Based on the values in fields 5.1, 5.2, and 5.3 (image), the Max Possible Score: VTG field should be 6 not 9, because we're not counting the criterion that got scored -1 because the functionality isn't present.

Screenshot 2024-03-15 at 10.40.08.png

I also need to look at my score formula (Score: Voice, Tone, ...) but that's on me.

Any additional thoughts are most welcome. Thank you 😁

 

Ah!  Sorry, try this:

IF(
  {Score 1} > 0, 3
) + 
IF(
  {Score 2} > 0, 3
) + 
IF(
  {Score 3} > 0, 3
) 

Screenshot 2024-03-16 at 12.29.57 AM.png

Thank you, Adam! That works exactly the way I wanted it to.  💝