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

# Custom Formula Help

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

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

Hi,

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

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

• ###### 3. Re: Custom Formula Help

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

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

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

• ###### 6. Re: Custom Formula Help

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

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

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

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

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

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

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

dude,

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

• ###### 14. Re: Custom Formula Help

Thanks.