Dynamically Scale a Range of Numbers

[qube99]

There are times when you will have a range of values in a field across a found set of records that you would like to scale to another range. Common ranges to scale are 0-1, 1-10, 1-100 etc. The input values could be anything of any range.

 

To do this start by creating 4 global fields called oldMin, oldMax, newMin and newMax. oldMin and oldMax are the smallest and largest values in your found set. newMin and newMax are the smallest and largest values you scale the range to. Go ahead and populate the newMin and newMax values. Let's use 1 and 100 for example.

 

To populate oldMin and oldMax I simply sort the records ascending by myValue field, go to first record and get the value for oldMin, then go to the last record and get the value for oldMax. We now have all 4 global fields populated.

 

Create a calculation field, let's call it Ranking. Use this formula:

((myValue - oldMin) / (oldMax - oldMin) * (newMax - newMin)) + newMin

 

All the values will now be ranked 1-100 in the Ranking field.

 

To make this dynamic so that it changes as new records alter your oldMin and oldMax, just add the sort, first record, last record routine to the end of your create new record script after myValue has been populated.

 

This works with found sets so there's some versatility there. You are also not restricted to any predefined range for the scaling. Just set newMin and newMax to be whatever you'd like for your scale.

 

The attached image shows myValue scaled to Ranking (1-100)

 

[comment]

I believe you could replace the oldMin and oldMax global fields, as well as the script that populates them, with a couple of summary fields defined as Minimum of [myValue] and Maximum of [myValue]. That way the calculation will work "dynamically" indeed- with no need for a script at record creation.

[qube99]

Oh that's better way indeed. Thanks!