Discussions

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

    Posted 01-19-2023 14:01
    [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 01-19-2023 15:02

    @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 01-19-2023 15:23
    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 01-20-2023 11:46
    Edited by Brian Seymour 01-20-2023 14:13

    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 01-21-2023 16:11
    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 01-21-2023 20:44
    @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 01-22-2023 11:12
    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 01-23-2023 02:09
    @Don Larson​ they way you are able to visualise the application is nothing short of amazing.

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