Forum Discussion

BuildPro's avatar
BuildPro
Qrew Captain
2 years ago

Formula to calculate values - range of quantities

[ANNUAL PTO DAYS - ALLOWED]

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

Thank you for your assistance...

------------------------------
BuildPro
------------------------------
  • @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
    ------------------------------
    • BuildPro's avatar
      BuildPro
      Qrew Captain
      Thank you...I thought I had entered this same formula, but I must have missed something somewhere. It works now.

      ------------------------------
      BuildPro
      ------------------------------
  • 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
    ------------------------------
  • DonLarson's avatar
    DonLarson
    Qrew Commander
    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
    ------------------------------
    • BrianSeymour's avatar
      BrianSeymour
      Qrew Cadet
      @Don Larson Yeah, your version may be a bit more performant, since ​I'd imagine most Employees haven't been with the company 8+ years, so the earlier conditions would be met first, thus, producing results a bit faster. Of course, I don't unfamiliar the data set, that's just a guess and probably a negligible performance boost. 

      I do like putting the default on it's own line though ;)

      Anyway, I think the OP (BuildPro) probably resolved this one.

      ------------------------------
      Brian
      ------------------------------
      • DonLarson's avatar
        DonLarson
        Qrew Commander
        Brian,

        Putting the default and the comment line will pay you back in spades when you revisit it.  I am putting lots of comments in formula fields and structure them by sections for //Variables, //Images, //URLs, //HTML, //Logic and //UI

        It really helps when going back to a formula field that is 20 rows long never mind the 250 plus ones.

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