Formula to calculate values - range of quantities

Question

[ANNUAL PTO DAYS - ALLOWED]IF [YEARS EMPLOYED]&lt;.75, 0,IF [YEARS EMPLOYED]&gt;.7501 and less than 3.0, 5,IF [YEARS EMPLOYED]&gt;3.01 and less than 8.0, 10,IF [YEARS EMPLOYED]&gt;8.01 and less than 10, 15, IF [YEARS EMPLOYED]&gt;10.01 , 20Thank you for your assistance...------------------------------BuildPro------------------------------

buildpro · Answer

Thank you...I thought I had entered this same formula, but I must have missed something somewhere. It works now.------------------------------BuildPro------------------------------

justintorrence · Answer

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

IF(
    [YEARS EMPLOYED] &lt; .75, 0,
    [YEARS EMPLOYED] &gt; .7501 and [YEARS EMPLOYED] &lt; 3.0, 5,
    [YEARS EMPLOYED] &gt; 3.01 and [YEARS EMPLOYED] &lt; 8.0, 10,
    [YEARS EMPLOYED] &gt; 8.01 and [YEARS EMPLOYED] &lt; 10, 15,
    [YEARS EMPLOYED] &gt; 10.01, 20
)
​------------------------------Justin TorrenceQuickbase Expert, Jaybird Technologiesjtorrence@jaybirdtechnologies.comhttps://www.jaybirdtechnologies.com/#community-post------------------------------

brianseymour · Answer

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 (&nbsp; &nbsp; [Years Employed] &gt; 10.00, 20&nbsp; &nbsp; [Years Employed] &gt; &nbsp;8.00, 15,&nbsp; &nbsp; [Years Employed] &gt; &nbsp;3.00, 10,&nbsp; &nbsp; [Years Employed] &gt; &nbsp;0.75, &nbsp;5,&nbsp; &nbsp; // Default&nbsp; &nbsp; 0)
Or consider refactoring to using the Case formula too.------------------------------Brian------------------------------

donlarson · Answer

BP,I always do these from the low end so that the final answer is the max value allowed.&nbsp; Another business case is looking up Commission RatesIf (&nbsp; &nbsp; &nbsp;[Years Employed] &lt; 0.75, 0,&nbsp;  &nbsp; &nbsp;[Years Employed] &lt; 3, 5,&nbsp; &nbsp; &nbsp;[Years Employed] &lt; 8, 10,&nbsp; &nbsp; &nbsp;[Years Employed] &lt; 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 · Answer

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

Forum Discussion

Formula to calculate values - range of quantities