Discussions

 View Only
  • 1.  Formula to calculate values - range of quantities

    Posted 12 days ago
    [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
    ------------------------------


  • 2.  RE: Formula to calculate values - range of quantities

    Posted 12 days ago

    @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
    ------------------------------



  • 3.  RE: Formula to calculate values - range of quantities

    Posted 12 days ago
    Thank you...I thought I had entered this same formula, but I must have missed something somewhere. It works now.

    ------------------------------
    BuildPro
    ------------------------------



  • 4.  RE: Formula to calculate values - range of quantities

    Posted 12 days ago
    Edited by Brian 11 days ago

    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
    ------------------------------


  • 5.  RE: Formula to calculate values - range of quantities

    Posted 10 days ago
    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
    ------------------------------



  • 6.  RE: Formula to calculate values - range of quantities

    Posted 10 days ago
    @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
    ------------------------------



  • 7.  RE: Formula to calculate values - range of quantities

    Posted 10 days ago
    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
    ------------------------------



  • 8.  RE: Formula to calculate values - range of quantities

    Posted 9 days ago
    @Don Larson​ they way you are able to visualise the application is nothing short of amazing.

    ------------------------------
    Prashant Maheshwari
    ------------------------------