# Discussions

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

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

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

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

Posted 19 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 19 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 18 days ago
Edited by Brian 18 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 17 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 16 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 16 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 15 days ago
@Don Larson​ they way you are able to visualise the application is nothing short of amazing.

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