14 Replies Latest reply on Jan 12, 2010 11:16 AM by Dr.D

    Building a Formula for Adding Up Scores

    Dr.D

      Title

      Building a Formula for Adding Up Scores

      Post

      I have a formula called Total Score, which sum up the scores to a max of 100, this is working fine... Eg. when the scores sum up as 98, then the person is getting 98% out of the 100 marks... (scenario 1)

       

      But I now need to expand the logic to another scenario, Eg. when the scores sum up as 94 out of 96, the % should yield 97%, which the % is NOT based on 100 points... (scenario 2)

       

      The marking criteria is a set of Radio Button of "Yes - No - N/A":-

      • when "Yes" you get 2 point,
      • when "No" you get 0 point,
      • and when "Not Applicable" is being selected, scenario 2 formula need to be triggered as opposed to scenario 1 which is a simple addition of all of the "Yes"

       

      How do I build the logics for these Specify Calculation?? The formula need to be a format of " If ..., then scenario 1, Or ... then scenario 2 "...  Thanks

        • 1. Re: Building a Formula for Adding Up Scores
          casey777
             The 94 out of 96 result should be calculated as 94/96 = 97.91 percent. You could then write is as Score/TotalPoints where at some point you are inputing the total points available for that scenario.
          • 2. Re: Building a Formula for Adding Up Scores
            Dr.D
              

            Thanks for the reply, that's just an example, I do not care about 97% Vs 97.19%...

             

            But I need to know exactly how to write the formula...

            • 3. Re: Building a Formula for Adding Up Scores
              ninja
                

              Howdy Dr.D.

               

              You would have three fields:

              TotalScore (How many points scored)

              FullScore (Maximum possible score)

              PercentScore (the one you're asking about)

               

              PercentScore would be a calculation field defined as:  (TotalScore / FullScore ) *100

               

              You could then format the PercentScore field on the layout to include as many or as few decimal places as you wish.

               

              Is this what you're after?

              • 4. Re: Building a Formula for Adding Up Scores
                Dr.D
                  

                Thanks for the wisdom, that's look straight-forward, however the trick is "when there is a N/A", the max score is not 100 points... The total max score will become " 100 less all the N/A ", then all "Yes" over that total score = %, and each " N/A " has a different point associates to it...

                 

                 

                • 5. Re: Building a Formula for Adding Up Scores
                  comment_1
                    

                  I am afraid your description is not that clear. It seems you want (in pseudocode):

                   

                  Count of "Yes" / ( 100 - Count of "N/A" )

                   

                  If you know how to count "Yes", then you should also know how to count "N/A".

                   

                   

                   

                  I didn't get this part:


                  Dr.D wrote:

                  each " N/A " has a different point associates to it...


                   

                   


                  • 6. Re: Building a Formula for Adding Up Scores
                    ninja
                      

                     

                    Somehow, you are adding up the Yes and No scores attributing different values to achieve your TotalScore.

                    Use that same method to add up FullScore, attributing 1 to all answers that are NOT N/A, and 0 for all N/A answers.

                     

                    Then use the simple equation.

                    • 7. Re: Building a Formula for Adding Up Scores
                      Dr.D
                         Thanks everyone who has offer the wisdom, give me a few moment, I will try to explain it in a better way...
                      • 8. Re: Building a Formula for Adding Up Scores
                        Dr.D
                          

                        Here is the current formula:-

                        Score CS -

                        Case (Contact Time Met = " Yes " ; 1;

                        Contact Time Met = " No " ;0 ;

                        Contact Time Met = " N/A " ;0 ;)

                         

                        Each Yes has a different score and counting that is not a problem...

                         

                        But as you can see N/A is 0, all N/A is 0, because when it is not applicable, there is no point given...

                         

                        So I need a logic to work as:- for instance

                        Yes = 60 (score 60 points)

                        No = 9 (lost 9 points)

                        N/A = 31 (points, not # of N/A)

                         

                        Then the % is 60 / ( 100 less 31 )...    but N/A = 31 is known only when I manually count them due to the formula set above... Thanks

                         

                        • 9. Re: Building a Formula for Adding Up Scores
                          ninja
                            

                          Dr.D wrote:

                          Here is the current formula:-

                          Score CS -

                          Case (Contact Time Met = " Yes " ; 1;

                          Contact Time Met = " No " ;0 ;

                          Contact Time Met = " N/A " ;0 ;)


                          Make a new "ValidAnswers" field:

                          If (Contact Time Met = " N/A " ;0 ;1 )

                           

                          Total it up into the field "FullScore" the same way you total up your ScoreCS currently.

                           

                          Use this total as the denominator when calculating your percentage score.

                           

                           


                          • 10. Re: Building a Formula for Adding Up Scores
                            comment_1
                              

                            I am stil not sure I see the full picture, but here's one way to look at it:

                             

                            Case (

                            Field = "Yes" ; 1 ;

                            Field = "No" ; 1000 ;

                            Field = "N/A" ; 1000000

                            )

                             

                            Sum the results, and use Div ( sum ; 10^6 ) to get the count of "N/A" and Mod ( sum ; 10^3 ) to get the count of "Yes".

                            • 11. Re: Building a Formula for Adding Up Scores
                              casey777
                                

                              Dr. D, in clarifying your input method, are you entering the values for "Yes", "No", and "N/A" by creating new records for each or by activating a script which counts the totals and displays the results, or by some other method?

                               

                              Maybe you could walk us through the process. 

                               

                               

                              • 12. Re: Building a Formula for Adding Up Scores
                                Dr.D
                                  

                                Hi Ninja, I think your last suggestion may work, let's expand it further...

                                 

                                So I go:-

                                 

                                The field " Score CS ", has this calculation behind it:-

                                Case ( Contact Time Met = " Yes " ; 3 ;

                                Contact Time Met = " No " ; 0 ;

                                Contact Time Met = " N/A" ; 0 )

                                 

                                Set up a new field " A" , and post the following formula:-

                                If ( Contact Time Met =  " N/A " ; 0 ; 3), so on to create " B " & " C ", then add up A + B + C, right??, I think I am getting it...  

                                (*this formula mean, when N/A = 0, it gets 3 points deduct from the total max points )

                                • 13. Re: Building a Formula for Adding Up Scores
                                  ninja
                                    

                                  There is inconsistency your description of scoring.  Please clarify it.

                                   

                                  Is the following correct or not?

                                  1. Score is made up of 1 point for every "Yes" answer, no points for any "No" answer and no points for any "N/A" answer.

                                  2. The number of questions is always 100.

                                  3. The questions to be considered in a percentage score are those that do not have an "N/A" answer.

                                  4. All questions are answered with "Yes", "No" or "N/A" and none are ever left blank.

                                   

                                  Thus in 100 questions, with 10 "no" answers and 20 "N/A" answers, the final score would be 70pts out of 80.

                                   

                                  If this is true, we should ignore that "Yes" = 2pts in your original post, and that "Yes" = 3pts in your most recent post, and simply accept that "Yes" = 1pt.

                                   

                                  So that leaves us with....is this scenario that I've described in this post accurate to your situation or not?

                                  • 14. Re: Building a Formula for Adding Up Scores
                                    Dr.D
                                       I think I have figure out the solution, thanks everyone for your assistance, it is greatly appreciated !!!