Forum Discussion
Formula issues, not calculating as wanted and not returning a value, instead showing blank.
I have spreadsheet that I am adding formula's in to calculate length of service and then further v lookup formulas to then pick up sick entitlements based off of the length of service. I have a coupl...
SDTC2023 I believe you make it difficult for yourself by introducing "<1". Better to stay with numbers, and use 0 (zero) for an LOS less then 1.
Then I think you made an error in your example. Why would an LOS of 6 become 4 and 6. I would expect 10 and 16 as 6 years is greater than 5 years, being the maximum LOS in the lookup table.
Anyhow, the attached file contains some formula how I would deal with it.
Hi,
The way the company has done this to date is to show LOS as <1 when under a year so they want to keep that format - I do understand this is probably causing issues with the formula working.
The difference in the calculation that I haven't explained fully is that some employee's sick entitlement stops at the LOS 3 years threshold, others get the 4 and 5 years threshold which is why I have the 4 different formula's at play in the sheet.
Thank you for sending over a sheet, I will have a look at this now and see if it fixes the issues!
The way the company has done this to date is to show LOS as <1 when under a year so they want to keep that format - I do understand this is probably causing issues with the formula working.
The difference in the calculation that I haven't explained fully is that some employee's sick entitlement stops at the LOS 3 years threshold, others get the 4 and 5 years threshold which is why I have the 4 different formula's at play in the sheet.
Thank you for sending over a sheet, I will have a look at this now and see if it fixes the issues!
With regard to the "<1" issue you can use a custom format 0;;"<1" to display zeros as <1, keeping intact the number 0.
Not really sure how to tackle the other issues, but I trust you'll figure that out yourself.