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

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

### 7 Replies

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