7 Replies Latest reply on Feb 25, 2010 1:34 PM by mrvodka

    URGENT!! Help On Rounding Numbers

    dekade

      Title

      URGENT!! Help On Rounding Numbers

      Post

      I have the following:

       

      1    A field called "Finished Length"; It might currently have a number value of 13.0248

      2    Then I have a field called "Rounded Off Finished Length"

      3    If the right 5 digits of the "Finished Length" field are LESS THAN or EQUAL TO .3125 then the "Rounded Off Finished Length" will display 13

      4    If the right 5 digits of the "Finished Length" field are GREATER THAN .3125 but LESS THAN or EQUAL to .5000 then the "Rounded Off Finished Length" will display 13.5

      5    If the right 5 digits of the "Finished Length" field are GREATER THAN .5000 then the "Rounded Off Finished Length" will display 14


       

       

      I cannot figure out the calculation for that for the "Rounded Off Finished Length" field.

       

      Can someone help me write that calculation as soon as possible.

       

      Thanks,

       

      Dekade 

        • 1. Re: URGENT!! Help On Rounding Numbers
          mrvodka
            

          Try:

           

          Let ( m = Mod ( Finished Length; 1 );
                  Case ( m > .5; 14;
                             m > .3125; 13.5;
                             13
                          )
                )


          • 2. Re: URGENT!! Help On Rounding Numbers
            ninja
              

            Howdy,

             

            never used the MOD() function myself, but it seems appropriate here.  Others will correct me if I get it screwy...

             

            Case ( Mod(FinishedLength,1) <= 0.3125 ; FinishedLength - Mod(FinishedLength,1) ;

            Mod(FinishedLength,1) <= 0.5000 ; FinishedLength + 0.5 - Mod(FinishedLength,1) ;

            FinishedLength + 1 - Mod(FinishedLength,1)

            )

             

            I think I have the syntax right...but you should see the approach at least..

             

            Edit: Mr. V beat me to it...I'll have to get more comfortable with the "Let"...that approach is much cleaner.

            • 3. Re: URGENT!! Help On Rounding Numbers
              comment_1
                

              I believe it should be:

               

              Let (

              r = Mod ( FinishedLength ; 1 )
              ;
              Int ( FinishedLength ) + Case (
              r > .5 ; 1 ;
              r > .3125 ; .5
              )
              )

               

               

              Note that this assumes FinishedLength is not a negative number.



              • 4. Re: URGENT!! Help On Rounding Numbers
                mrvodka
                  

                Apologies. I misunderstood your post.

                 

                Try:

                 

                Let ( [ m = Mod ( Finished Length; 1 );

                          n = Int ( Finished Length )

                        ]; n + Case ( m > .5; 1; m > .3125; .5 )

                      )

                 

                 

                 

                * Edit - Didnt see comment's post

                • 5. Re: URGENT!! Help On Rounding Numbers
                  dekade
                    

                  To Mr Vodka and Comment:

                   

                  I thank you so very much. It looks like the first reply from mr vodka only returned the answers 13, 13.5 etc. As you both went further down the post we ended up with a formula that allows the "Finished Length" field to be absolutely 'any'; not specifically 13.xxxx.

                   

                  Am I reading the both of you correctly? If so I am ready to continue my project. If I'm wrong please advise me. Otherwise I hope that someone can help both of you someday the way you both have quickly helped me. THANKS

                   

                  Dekade

                   

                  OOPS! JUST NOTICED SOMETHING.

                   

                  Let ( [ m = Mod ( Finished Length; 1 );

                            n = Int ( Finished Length )

                          ]; n + Case ( m > .5; 1; m > .3125; .5 )

                        )

                   

                  In the 3rd line of the calculation doesn't 'm' need to be greater than .3125 but less than .5?

                   

                  If so how do you then write the third line?

                   

                  DEKADE

                   

                  • 6. Re: URGENT!! Help On Rounding Numbers
                    comment_1
                      

                    dekade wrote:

                    As you both went further down the post


                    I posted only once ...

                     

                    My calculation allows FinishedLength to be any number, except a negative one - so 2.3 will be rounded to 2, 19.4 to 19.5 and 123.9 to 124.


                    • 7. Re: URGENT!! Help On Rounding Numbers
                      mrvodka
                        

                      dekade wrote:

                      In the 3rd line of the calculation doesn't 'm' need to be greater than .3125 but less than .5?



                       

                      No. In a case calculation it exits out once a criteria is met.

                       

                      We do not have to check if its less than .5 in the second test of the Case () statement because it was already checked to see if it were greater than .5 in the first part. If it had past that test it would have resulted in 1 and skipped the rest of the evaulations. Therefore, we already know that it cant be > .5. The second test checks to see if its > .3125. So that condition would be met if the value is > .3125 up to and including .5