if or case, range of numbers

[Skipi]

Hi,

I've let my filemaker go bit rusty and I'm driving myself mad trying to remember how to make a calculation that gives a result based on a range of numbers.

ie.

1-10 =10

11-20 = 20

21-30=30 etc

I've tried case (field >1 <10;10 etc etc i've a feeling that I'm missing something between the >1 and <10???

Thanks in advance,

Skip

[LaRetta]

Case (

field < 10 ; 10 ;

field < 20 ; 20 ;

field < 30 ; 30 ;

... continue and then if there is a default result

;

999

)

Since vs. 7, calculations short circuit and stop evaluating when they hit the first true. There is also concept of predicting which groups will have the largest numbers of records returned and planning for it.

For instance, if you know that 80% of your records are above 80 but not over 100 then it works best to reverse the theory as:

Case (

field > 100 ; 999 ;

field > 90 ; 90 ;

field > 80 ; 80 ;

... etc.

Taking advantage of branch prediction will speed up evaluations overall.

Edited November 24, 2008 by Guest
Added explanation

[Skipi]

Thanks very much.

[LaRetta]

Actually, you may not need to list out all your options. Consider this as well:

Ceiling ( field / 10 ) * 10