6 Replies Latest reply on Sep 23, 2015 2:59 PM by TSGal

# Rounding calculation fields to the nearest 500

### Title

Rounding calculation fields to the nearest 500

### Post

I am a new user and a complete novice. I am using FileMaker as to keep track of real estate sales (I am an appraiser) and I have calculation fields set up to automatically calculate % of sale prices (e.g. 2%, 2.5%, 4%, 5%, 6%, etc.). I am looking to have the calculated results round to the nearest 500. Please advise and keep in mind that I am a beginner, lucky to have figured out the calculation fields....

• ###### 1. Re: Rounding calculation fields to the nearest 500

When you say round to the nearest 500, do you mean up, down or up or down

11400=11500

11300=11000, or 11500

• ###### 2. Re: Rounding calculation fields to the nearest 500

This is ugly, but it works.  I'm sure Phil, and especially Mr. Raybaudi know of a better way.  There may even be a custom function.

It will round up as follows:

11000=11000

11001=11500

11500=11500

11501=12000, but 11500.49 =11500, where 11500.5 =12000

Let([
hund=Right(Round(Service::odometer;0);3); //Substitute your number field for Service::odometer
rnd=Case(
hund  >  500;0;
hund  =   500;-500;
hund  ≥ 1 ; 500;
0

)  //end of case
];  //end of Let variable declarations
Round(Service::odometer;-3)+rnd
)

• ###### 3. Re: Rounding calculation fields to the nearest 500

What do you know, found a custom function:

//This custom function is called SuperCeiling, submitted by Ibrahim Bittar Torres on briandunning.com
Let ( [ Num = Service::odometer /500 ; //change Service::odometer to your field-500 is the precision
Integ = Int ( Num ) ;
Frac = Num - Integ ] ;

( Integ + Ceiling ( Frac ) ) * 500 )

• ###### 4. Re: Rounding calculation fields to the nearest 500

Like you asked in your first post "round up, down or closest?" Peter asked for "closest" and the above calculation rounds 5100 to 5500. That doesn't seem "closest" to me.

Perhaps there's a more elegant way, but this calculation:

Let ( [ Rgt = Mod ( Value ; 1000 ) ;  // substitute your field for 'value'
Lft = Div ( Value ; 1000 )
] ;
Case ( Rgt ≥ 750 ; (Lft + 1 ) * 1000 ;
Rgt < 250 ; Lft * 1000 ;
Lft * 1000 + 500
) // Case
) // Let

will round 5100 to 5000 rather than 5500

• ###### 5. Re: Rounding calculation fields to the nearest 500

I saw 'nearest' not closest :)...lol  We'll never know until Peter comes back.

• ###### 6. Re: Rounding calculation fields to the nearest 500

Another possible option to round to the nearest 500 would be:

Round ( ( <NumberField> * 2 ) / 1000 ; 0 ) * 500

TSGal

FileMaker, Inc.