14 Replies Latest reply on Nov 15, 2016 10:31 AM by fmpdude

    Custom Formula Help

    johnw54

      I need to create a formula that rounds up to the nearest $5 or $0

       

      $533.35 would round to $535

       

      $537.42 would round to $540

       

      Thank you

        • 1. Re: Custom Formula Help
          TSPigeon

          johnw54:

           

          Thank you for your post!

           

          I am going to move this thread from the FileMaker Community Feedback Space, which is specifically for input on the Community itself, to the Discussions Space where you should receive more views and potentially more feedback on this topic!

           

          TSPigeon

          FileMaker, Inc.

          • 2. Re: Custom Formula Help
            David Moyer

            Hi,

            this doesn't work for negative numbers, but for positive values ...

            = 5 * Round(table::aNumber / 5; 0)

            • 3. Re: Custom Formula Help
              David Moyer

              actually, if you always want to round up ...

              = 5 * Ceiling(table::aNumber / 5)

              1 of 1 people found this helpful
              • 4. Re: Custom Formula Help
                TSGal

                David Moyer:

                 

                Using your first calculation, the negative numbers appear to work for me.

                 

                For example, If I enter -537.42, it rounds upwards to -535.  If I enter -537.51, it rounds down to -540.

                 

                What values are you entering where the calculation fails?

                 

                TSGal

                FileMaker, Inc.

                • 5. Re: Custom Formula Help
                  David Moyer

                  I was thinking of rounding to the next largest absolute value, for some reason.

                  • 6. Re: Custom Formula Help
                    Malcolm

                    Ceiling is not a good solution if you want symmetry between negative and positive values. If the initial value is $6 it rounds to $10. When dealing with negatives you probably want $6 to round to $10. In that way, $6 and -$6 will generate $10 and -$10. If you use Ceiling you end up with $10 and -$5.

                    • 7. Re: Custom Formula Help
                      johnw54

                      Hi David,

                       

                       

                      Thank you so much for your input.....  the formula almost works.... here are my numbers and the formulas I have entered.

                       

                       

                      Stocking Dealer    $1080

                       

                       

                      Wholesale   5 * Ceiling ( (Stocking Dealer * 1.116)/ 5 )  calculated at $1210 which is correct

                       

                       

                      Designer   5 * Ceiling ( (Stocking Dealer * 1.268)/ 5 )    calculated at $1369    I need it to be $1370

                       

                       

                       

                      Retail  5 * Ceiling ( (Stocking Dealer * 3.41)/ 5 )  calculated at $3683   I need it to be $3685

                       

                       

                       

                       

                      I need all calculations to end in either   a  0  or a 5

                       

                       

                      Thanks again for your help

                       

                       

                       

                      Regards,

                       

                      John Williams

                      Tel 909.969.1173

                      Fax 909.266.0364

                      • 8. Re: Custom Formula Help
                        johnw54

                        Hi Malcom,

                         

                         

                        I tried the ceiling function and here is what I got....  Also i will always round up and there will be no negative numbers.

                         

                         

                         

                        Stocking Dealer    $1080

                         

                         

                        Wholesale   5 * Ceiling ( (Stocking Dealer * 1.116)/ 5 )  calculated at $1210 which is correct

                         

                         

                        Designer   5 * Ceiling ( (Stocking Dealer * 1.268)/ 5 )    calculated at $1369    I need it to be $1370

                         

                         

                         

                        Retail  5 * Ceiling ( (Stocking Dealer * 3.41)/ 5 )  calculated at $3683   I need it to be $3685

                         

                         

                         

                         

                        I need all calculations to end in either   a  0  or a 5

                         

                         

                         

                        Can you recommend a solution?

                         

                         

                         

                         

                        Regards,

                         

                        John Williams

                        Tel 909.969.1173

                        Fax 909.266.0364

                         

                         

                         

                         

                         

                         

                        Regards,

                         

                        John Williams

                        Tel 909.969.1173

                        Fax 909.266.0364

                        • 9. Re: Custom Formula Help
                          Malcolm

                          I would use this kind of calc.

                           

                          Let (

                              [

                                  sd = Stocking Dealer

                                  ; rawTotal = sd * 1.268

                                  ; roundDiff = mod( rawTotal ; 5 )

                              ]

                              ; rawTotal + if ( roundDiff ; 5 - roundDiff ; 0 )

                          )

                          • 10. Re: Custom Formula Help
                            David Moyer

                            something isn't right there.  Ceiling should return an integer; and five times any integer ends in zero or five.

                            • 11. Re: Custom Formula Help
                              David Moyer

                              I agree, and that's why I started with the Round function.  But the OP indicated rounding up

                              $537.42 would round to $540

                              So, $15.01 would round to $20.  If the OP wants the other method, he can just use the calculation in post 2.

                              • 12. Re: Custom Formula Help
                                fmpdude

                                Nice.

                                 

                                How did you come up with this function so fast?

                                 

                                Is it one you have researched in the past or did you just "figure it out" on the fly?

                                 

                                Very cool.

                                • 13. Re: Custom Formula Help
                                  David Moyer

                                  dude,

                                   

                                  I churned through it before.  It's a pretty easy way of "slotting" numbers.

                                  • 14. Re: Custom Formula Help
                                    fmpdude

                                    Thanks.