Calculation with Ranges Involved

[JNJ3120]

Hello everyone,

I am trying to create a calculation that checks to see if the number in a field falls within a certain range and thereby assign it a value based on that. For instance, if the number is 34, then assign it a 3 (to state it simply). I've tried experimenting with the Case function, but to no avail. The problem I am having is setting it up so that it checks for being in a specific range, like 11 to 20, 21 to 30, etc. How would I setup the calculation to check for being within a specific range? And is there a better option than Case for this?

Thanks in Advance,

Joshua

[Lee Smith]

Look at the example here

The case evaluates until it finds a correct answer, so your statement would be something like this.

Case( data >= 40; 4 ;data > 30; 3 ; data >= 20 ; 2; data >= 10 ; 1 ; data < 10 ; 0 )

[comment]

And is there a better option than Case for this?

It depends on [1] what exactly your ranges are and [2] what does the number represent in real life.

[Pavlk]

All answers are right. And if you have difficulties to mention all ranges in Case function (as offers Lee), then you might wanna use this one

Left ( your_number_field ; Length ( your_number_field ) - 1 )

One by dividing by 10 or any other way you'll find.

[LaRetta]

Why not

Int (theNumberField / 10 )

then? If it is a number then best to use a number function. But we do not know the range yet ... As you say, that consistency may not hold. :-)

[Lee Smith]

Left ( your_number_field ; Length ( your_number_field ) - 1 )

This will break if there is a decimal.

[comment]

All answers are right.

All answers are right, but some are more right than others...

We don't know that the ranges follow a uniform pattern. It might be just a coincidence. More importantly, we don't know if the ranges themselves are data or not. If they are, then this should be a lookup, not a calculation.

[JNJ3120]

Thanks for all the replies. I appreciate the help. The ranges represent growth in different performance metrics. The ranges will be numbers up to two decimal places (calculated from other fields). The breakdown will be less than or equal to 0, then 0 to 20, then 20 to 30, 30 to 40, 40 to 50, and then so on up to 100, where it will end in greater than or equal to 100. And you're right, there is not a consistent range per se. 

 

I chose Case just because of prior familiarity. I'm still a newbie with FileMaker. So if there is something better, I'm not exactly sure.

 

I'm just looking for a way to lighten my workload. Prior to this, I just entered the values myself, but on occasion during very busy times I may have missed an update.

 

Again, thanks for the help.

[comment]

The ranges represent growth in different performance metrics.

 

 

I am afraid that doesn't tell me much.

 

 

Prior to this, I just entered the values myself, but on occasion during very busy times I may have missed an update.

 

 

An update of what exactly? This is a crucial point: do the ranges ever change or are they cast in stone? And if they do change, what is supposed to happen to the already existing numbers?

[JNJ3120]

I am afraid that doesn't tell me much.

The performance metrics are metrics like net income growth, operating margin growth, etc. So, they will be in percentage form with two decimal places. The calculated field I am trying to come up with assigns a numerical value based on whatever particular range they fall into.

The ranges will be cast in stone.

[comment]

If the range boundaries are constant, and the interval is not uniform (or not easily computed another way) then Case() is your best choice.

[JNJ3120]

Ok, thanks. I appreciate your help.

[JNJ3120]

Look at the example here

The case evaluates until it finds a correct answer, so your statement would be something like this.

Case( data >= 40; 4 ;data > 30; 3 ; data >= 20 ; 2; data >= 10 ; 1 ; data < 10 ; 0 )

Hey, just wanted to let you guys know I finally got around to and the method posted by Lee Smith is working like a charm. Thanks again!

[LaRetta]

Good to hear!! Come back and see us again! :-)