10 Replies Latest reply on May 27, 2009 5:56 AM by Sendnot

    Help with calculation

    Sendnot

      Title

      Help with calculation

      Post

      I've been trying to write a fairly simple calculation, except that it doesn't work as I'd expect. What I'm trying to do is to calculate a "mid-point" grade; that is, if an average grade is a 1.35, then the "mid-point" grade is a 1.70, a 1.70 becomes a 1.85, the 1.85 becomes a 2.00, etc... on up to 4.01.

       

      Below is what I've written:

       

      Case(Final Average_ALPHA ≥ 1.35 and Final Average_ALPHA ≤ 1.7;"1.70";Final Average_ALPHA ≥ 1.70 and Final Average_ALPHA ≤ 1.85 ;"1.85";Final Average_ALPHA ≥1.85 and Final Average_ALPHA ≤ 2;"2.00" ;Final Average_ALPHA ≥2.15 and Final Average_ALPHA ≤ 2.3;"2.30";Final Average_ALPHA ≥2.30 and Final Average_ALPHA ≤ 2.5;"2.50";Final Average_ALPHA ≥2.50 and Final Average_ALPHA ≤ 2.7;"2.70";Final Average_ALPHA ≥2.70 and Final Average_ALPHA ≤ 2.85;"2.85";Final Average_ALPHA ≥2.85 and Final Average_ALPHA ≤ 3;"3.00";Final Average_ALPHA ≥3.00 and Final Average_ALPHA ≤ 3.15;"3.15";Final Average_ALPHA ≥ 3.15 and Final Average_ALPHA  ≤  3.3;"3.30"; Final Average_ALPHA ≥ 3.3 and Final Average_ALPHA  ≤  3.5; "3.50"; Final Average_ALPHA  ≥ 3.5 and Final Average_ALPHA  ≤ 3.7; "3.70"; Final Average_ALPHA  ≥ 3.85 and Final Average_ALPHA  ≤ 3.98; "4.00"; Final Average_ALPHA  ≥ 3.99; "4.01")

       

      From my point of view, it should work, but a 1.70 produces a 1.70 when I'd like to see 2.0 instead. Please help!

       

      Thanks-:smileyindifferent: 

        • 1. Re: Help with calculation
          mrvodka
            

          You are using less than or equal to and then you are using greater than and equal to.

          Final Average_ALPHA ≤ 1.7;"1.70";Final Average_ALPHA ≥ 1.70

           

          Therefore, when its 1.7, it will display 1.7, since it meets the criteria of the first check that you have.

           

          Second, I am not sure what you are talkign about in a mid-point grade. Your calc can be simplified a lot, but I dont know how to help you because I dont understand what you are trying to get. You said that when the avg grade is 1.7, you expect a result of 2, yet you check for it to be 1.7 and 1.85.

           

          Last, you dont need quotes for numbers.

          • 2. Re: Help with calculation
            LaRetta_1
              

            I'm afraid that your calculation does not translate to what you say you want. Example ...  1.70 should change to 2.00 but your calculation says:

             

            Final Average_ALPHA ≥ 1.35 and Final Average_ALPHA ≤ 1.7;"1.70";

            Final Average_ALPHA ≥ 1.70 and Final Average_ALPHA ≤ 1.85 ;"1.85";

            Final Average_ALPHA ≥1.85 and Final Average_ALPHA ≤ 2;"2.00"

             

            According to your calculation, 1.70 would fit into the first category AND the second and, since Case() stops evaluating at the first true, it will also produce 1.70.

             

            Can you instead give us examples of the number and the translated amount you want, such as if Final Average_ALPHA is:

            27 ... it should be blank

            1.35 ... it should be 1.70

            1.70 ... it should be ... etc

             

            Give us examples throughout your ranges and we can then adjust your calculation accordingly.

             

            HI JOHN!!!! :smileyvery-happy:

            • 3. Re: Help with calculation
              comment_1
                

              LaRetta wrote:
              Give us examples throughout your ranges and we can then adjust your calculation accordingly.

              Better yet: give us the rules.


              • 4. Re: Help with calculation
                Sendnot
                  

                First, thanks for all the response! Calculations have always been a problem for me, so I'm happy when someone helps me 'see the light', so to speak.

                 

                A mid-point grade (as I understand it) is a number that is 'rounded up' from a "Quality Point Designation"; essentially, if a student gets a certain Quality Point Designation, then a 'Mid-Point' grade is awarded. The scale is as follows:

                 

                1.00 to 1.34 = 1.35

                1.35 to 1.69 = 1.70

                1.70 to 1.84 = 2.00

                2.00 to 2.14 = 2.15

                2.15 to 2.29 = 2.30

                2.30 to 2.49 = 2.50

                2.50 to 2.69 = 2.70

                2.70 to 2.84 = 2.85

                2.85 to 2.99 = 3.00

                3.00 to 3.14 = 3.15

                3.15 to 3.29 = 3.30

                3.30 to 3.49 = 3.50

                3.50 to 3.69 = 3.70

                3.70 to 3.84 = 3.85

                3.85 to 3.99 = 4.00

                4.00 and above = 4.01 

                • 5. Re: Help with calculation
                  LaRetta_1
                    

                  And you realize you have a gap?

                   

                  1.70 to 1.84 = 2.00

                  2.00 to 2.14 = 2.15

                   

                  What do we do with 1.85 through 1.99?

                  • 6. Re: Help with calculation
                    comment_1
                      

                    Try something like:

                     

                    Case (
                    Grade ≥ 4 ; 4.01 ;

                    Grade ≥ 3.85 ; 4 ;
                    Grade ≥ 3.7 ; 3.85 ;
                    Grade ≥ 3.5 ; 3.7 ;
                    Grade ≥ 3.3 ; 3.5 ;
                    Grade ≥ 3.15 ; 3.3 ;
                    Grade ≥ 3 ; 3.15 ;

                    Grade ≥ 2.85 ; 3 ;
                    Grade ≥ 2.7 ; 2.85 ;
                    Grade ≥ 2.5 ; 2.7 ;
                    Grade ≥ 2.3 ; 2.5 ;
                    Grade ≥ 2.15 ; 2.3 ;
                    Grade ≥ 2 ; 2.15 ;

                    Grade ≥ 1.7 ; 2 ;
                    Grade ≥ 1.35 ; 1.7 ;
                    Grade ≥ 1 ; 1.35 ;
                    Grade
                    )

                     

                    Note:

                    I have taken into account your lower boundaries only. As LaRetta pointed out, you have a gap.I have also assumed that grades below 1 are unchanged.










                    • 7. Re: Help with calculation
                      Sendnot
                        

                      LaRetta,

                       

                       Doh! You are SO right! Thanks for catching that... I guess that's the problem with multi-tasking...

                       

                      Here's how it should be:

                       

                      1.00 to 1.34 = 1.35

                      1.35 to 1.69 = 1.70

                      1.70 to 1.84 = 1.85

                      1.85 to 1.99 = 2.00 

                      2.00 to 2.14 = 2.15

                      2.15 to 2.29 = 2.30

                      2.30 to 2.49 = 2.50

                      2.50 to 2.69 = 2.70

                      2.70 to 2.84 = 2.85

                      2.85 to 2.99 = 3.00

                      3.00 to 3.14 = 3.15

                      3.15 to 3.29 = 3.30

                      3.30 to 3.49 = 3.50

                      3.50 to 3.69 = 3.70

                      3.70 to 3.84 = 3.85

                      3.85 to 3.99 = 4.00

                      4.00 and above = 4.01

                       

                      That looks better    

                      • 8. Re: Help with calculation
                        LaRetta_1
                          

                        You are spot-on, Comment, but I wrote it this way (and I wonder if it isn't clearer in the meaning this way):

                         

                        Let ( field = final AVERAGE_ALPHA ;

                        Case (
                        field < 1 ; field ;
                        field < 1.35  ; 1.35 ;
                        field <  1.7 ; 170 ;
                        field <  2 ; 2 ;
                        field <  2.15 ; 2.15 ;
                        field <  2.3 ; 2.3 ;
                        field <  2.5 ; 2.5 ;
                        field <  2.7 ; 2.7 ;
                        field < 2.85 ; 2.85 ;
                        field < 3 ; 3 ;
                        field < 3.15 ; 3.15 ;
                        field < 3.3 ; 3.3 ;
                        field < 3.5 ; 3.5 ;
                        field < 3.7 ; 3.7 ;
                        field < 3.85 ; 3.85 ;
                        field < 4 ; 4 ;
                        4.01
                        ) // end case
                        ) // end let

                         

                        Changed the final default result.










                        • 9. Re: Help with calculation
                          LaRetta_1
                             Never mind ... my calc may SEEM easier to understand and read (which maybe is just because *I* created it, ha ha) but it is NOT the more logical in this instance because the request "is a number that is 'rounded up' from a "Quality Point Designation"; essentially..." and your calculation clearly shows the 'round up' effect.
                          • 10. Re: Help with calculation
                            Sendnot
                               Thanks to all that helped me. Mostly the problem was being rushed and losing attention to detail. No wonder it wouldn't work! Thanks again for pointing out the flaws in my formulae- it's due to the great people here that I was able to finish this project.