Round to a 5 or 9

[Goetch]

Hi I am a relative newbie with file maker... We quite a few price lists here based on simple calculations .... Example... cost x markup ....

I am trying to get a round function that looks at only the last digit of a number and either rounds up to 5 or rounds up to 9 .. example 532 I want to look only at the "2" and round it up to a 5 .....

Forgive me for my ignorance......... I have no idea how I would go about this as I said I am a newb and any help would be great...

[PotzUK]

Not sure why you would want to do that, but this formula should see you started:

Let(

N = [YOUR NUMBER]

;

Case(

Right( N ; 1 ) < 5 ; Left( N ; Length( N ) - 1 ) & 5 ;

Right( N ; 1 ) > 5 ; Left( N ; Length( N ) - 1 ) & 9 ;

N

)

)

That formula can only handle whole numbers though

[mr_vodka]

I dont think we have enough information for a more finely tuned calculation.

For example what about 0 and what about after the decimal?

Here is another calc that only takes the integer and treats 0 to round to 5. You can tweak it for your rules.

Let ( [ x = Int ( YourField );

        y = Right ( x; 1 )

      ]; x - y +  5 + Case ( y > 5; 4 )



)

[comment]

That formula can only handle whole numbers though

That's the consequence of using text functions to handle numerical operations. Try:

Let ( 

r = Mod ( n + 1 ; 10 )

;

n - r + Min ( 6 * Ceiling ( r / 6 ) ; 10 )

)

[Goetch]

I will give these a try, the reason is for a price list, we have a standard mark up percentage but management would rather have every thing end in a 5 or 9 no exception..... the decimal does not matter since we are only dealing with whole numbers, and as for a 0, i think in the past they would have rounded down but for this I would make it a 5... See currently we may get a price list as a word, pdf or an excel sheet, someone then converts if needed to excel, runs the formula to get the mark up price then changes each price to end with a 5 or 9...... Some price lists have 1000's of items so this is all very time consuming, I figure if I can get FM to do it for us then it will save us a lot of time and effort.... I will let you guys know if any of this works for me, I have had this on the back burner for some time now because I am working on another project..... I hope this information was helpful..... Thank you guys for all of your help!!!