
1. Re: Custom Formula Help
TSPigeon Nov 14, 2016 8:46 AM (in response to 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 Nov 14, 2016 9:14 AM (in response to johnw54)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 Nov 14, 2016 9:16 AM (in response to David Moyer)1 of 1 people found this helpfulactually, if you always want to round up ...
= 5 * Ceiling(table::aNumber / 5)

4. Re: Custom Formula Help
TSGal Nov 14, 2016 10:03 AM (in response to David Moyer)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 Nov 14, 2016 10:06 AM (in response to TSGal)I was thinking of rounding to the next largest absolute value, for some reason.

6. Re: Custom Formula Help
Malcolm Nov 14, 2016 6:16 PM (in response to David Moyer)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 Nov 14, 2016 6:18 PM (in response to David Moyer)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 Nov 14, 2016 6:21 PM (in response to Malcolm)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 Nov 14, 2016 7:30 PM (in response to johnw54)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 Nov 15, 2016 3:03 AM (in response to johnw54)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 Nov 15, 2016 3:27 AM (in response to Malcolm)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 Nov 15, 2016 7:08 AM (in response to David Moyer)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.

