Formula to calculate values - range of quantities

@BuildPro

Not sure if you are looking for assistance with this formula, but here is a revised version:

IF(
    [YEARS EMPLOYED] < .75, 0,
    [YEARS EMPLOYED] > .7501 and [YEARS EMPLOYED] < 3.0, 5,
    [YEARS EMPLOYED] > 3.01 and [YEARS EMPLOYED] < 8.0, 10,
    [YEARS EMPLOYED] > 8.01 and [YEARS EMPLOYED] < 10, 15,
    [YEARS EMPLOYED] > 10.01, 20
)

​

------------------------------
Justin Torrence
Quickbase Expert, Jaybird Technologies
jtorrence@jaybirdtechnologies.com
https://www.jaybirdtechnologies.com/#community-post
------------------------------

This formula may be potentially simplified by removing the "and ranges" which also guards against edges cases.

For example, 1 year is 365 Days, so 0.01 year presents a 3.65 day window the initial formula which may produce unexpected results?

Consider this logic too!

If (
    [Years Employed] > 10.00, 20
    [Years Employed] >  8.00, 15,
    [Years Employed] >  3.00, 10,
    [Years Employed] >  0.75,  5,

    // Default
    0
)

Or consider refactoring to using the Case formula too.

------------------------------
Brian
------------------------------

BP,

I always do these from the low end so that the final answer is the max value allowed.  Another business case is looking up Commission Rates

If (
     [Years Employed] < 0.75, 0,
     [Years Employed] < 3, 5,
     [Years Employed] < 8, 10,
     [Years Employed] < 10, 10, 20
)

This will give you an answer in four rows and can easily add new values between them when HR policy changes or Compliance forces a different answer.


------------------------------
Don Larson
------------------------------