Skip to main content

Hi everyone, 

I need some help with calculating scores. 

I have a form with 26 categories, each category has about 7-12 choices column with correct and incorrect choice. I would like the scoring to be calculated. User will be doing one category at a time. 

what would the best approach to avoid 300+ rules, as i tried Calculated column within SharePoint but i have hit a limit of number of column reference within the formula. 

this is what i did in SharePoint calculated column but it has reached a limit, so can't use it anymore

TotalCorrect:

+IF(SA1="Correct",1,0)+IF(SA2="Correct",1,0)+IF(SA3="Correct",1,0)+IF(SA4="Correct",1,0)+IF(SA5="Correct",1,0)+IF(SA6="Correct",1,0)+IF(SA7="Correct",1,0)+IF(SA8="Correct",1,0)+IF(SA9="Correct",1,0)+IF(SA10="Correct",1,0)+IF(SA11="Correct",1,0)+IF(SA12="Correct",1,0)


TotalInCorrect:

+IF(SA1="InCorrect",1,0)+IF(SA2="InCorrect",1,0)+IF(SA3="InCorrect",1,0)+IF(SA4="InCorrect",1,0)+IF(SA5="InCorrect",1,0)+IF(SA6="InCorrect",1,0)+IF(SA7="InCorrect",1,0)+IF(SA8="InCorrect",1,0)+IF(SA9="InCorrect",1,0)+IF(SA10="InCorrect",1,0)+IF(SA11="InCorrect",1,0)+IF(SA12="InCorrect",1,0)

Total Score:

=TotalCorrect/(TotalCorrect+TotalNotCorrect)

 

please advise?

You should just be able to do this using one Calculated Control I’d imagine. Can you provide a few additional details though? 

 

  • Are you using a Classic Form or something else? This is important to know!
     
  • Do the amount of Choice Controls change based on the “category”? If so, are they disabled / enabled based off of the category?
     
  • Do the Choice Controls actually just say “Correct” and “Incorrect” or do they contains answers that are determined to be correct or incorrect via a rule or some other method?
     
  • Can you provide a screenshot of what this looks like?

 

 


I am using New Responsive form. It will be a manual selection. the choice does say “Correct” and “Incorrect”. 

 


Sorry, one last thing, is this SharePoint online or SharePoint on Prem?


Sharepoint online 


Simple answer if you wish to use SharePoint calculated columns - split your calculation into 3 columns each covering a 3rd of the IF statements,  Then have a final calculated column that adds up the others, etc.

Like column TotalCorrect would be TotalCorrectPartA+TotalCorrectPartB+TotalCorrectPartC

Column TotalNotCorrect would be TotalInCorrectPartA+TotalInCorrectPartB+TotalInCorrectPartC

And your TotalScore set to =TotalCorrect/(TotalCorrect+TotalNotCorrect) 


Reply