Forum Discussion

Qrew Captain

3 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,...

Qrew Elite

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

BrianSeymour
Qrew Assistant Captain
3 years 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
------------------------------
- DonLarson
  Qrew Elite
  3 years 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
  ------------------------------
  - PrashantMaheshw
    Qrew Captain
    3 years ago
    @Don Larson they way you are able to visualise the application is nothing short of amazing.
    
    ------------------------------
    Prashant Maheshwari
    ------------------------------