Forum Discussion
Adding amounts to the total based on rules for decimals?
I'm trying to figure out a way to write a function that would take a value with up to 4 decimal places and either add 0, 1, 2, or 3 to the end total depending on the following criteria:
If the amount that has a decimal and it is less than x.3333 then add 0, if the value is between x.3333 and x.4166 then add 1, if the value is between x.4167 and x.4999 then add 2, and finally if the value is greater than x.5 then add three.
Here's some examples of what I trying to do:
(72/24)*3=9 therefore the answer I want is 9
but when I have something different:
(74/24)*3=9.25 I need the answer to still say 9
However when I have:
(82/24)*3=10.2500 I need the answer to say 11 or;
(89/24)*3=11.1250 I need the answer to say 12
I know I need the function to look at the first calculation and that's where I'm stuck.
Calculations are about start and stop times on a 24 hour cycle. The variances come from either 8, 10, or 12 hours past the first 24 hours. It would be great if they stopped working at the same time they started by they don't. Nor do they work the same number of days in a row so that varies as well.
I'm not very good with Excel as I'm basically self taught for most of it. I do not know the words that describe what I'm looking for so I couldn't google it. I really don't want to figure this out manually anymore so I'm not ready to give up and go back to pencil and notepad 🙂
Thanks for any help you could provide.
4 Replies
Let's say the values such as 72, 74, 82 and 89 are in D2 and down.
In another cell in row 2:
=LET(val, D2, base, 3*QUOTIENT(val, 24), mod, MOD(val, 24), extra, (mod>=12)+(mod>=10)+(mod>=8), base+extra)
Fill down.
Thanks for your help. It does seem my home office version does not support LET function (2016).
But I can use this at my office.
I did not think this formula would be so complicated. I honestly thought I was missing something simple! Thank you again 🙂
Here is a formula that should work in Excel 2016 too:
=3*QUOTIENT(D2, 24)+(MOD(D2, 24)>=12)+(MOD(D2, 24)>=10)+(MOD(D2, 24)>=8)
