Scoring system where number of criteria scored is variable

[annedoughertynm]

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.

[TheTimeSavingCo]

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

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?

[annedoughertynm]

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.

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 😁

[TheTimeSavingCo · Accepted solution]

Ah!  Sorry, try this:

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

[annedoughertynm]

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