Forum Discussion
Solved
Nested IF
Hello,
I need a little nudge on a formula.
I am working on a spreadsheet that calculates day rates for my business.
Let's say a day is ten hours and the rate for that day is 1000 d...
=IF(C3,C3*F3,IF(D3,D3*F3,(INT(b3)+MAX((MOD(b3,1)>=0.5)*0.65, MOD(b3,1)))*f3))
I didn't know if this meet your needs.
=(INT(quote)+MAX((MOD(quote,1)>0)*0.65, MOD(quote,1)))*rate
quote can be 1,1.5, 1.7, 2, 2.1, etc.
rate should be 1000, or the absolute cell reference of the cell contains the rate
if only those quote > x.5 and < x.65 turn to x.65
=(INT(quote)+MAX((MOD(quote,1)>=0.5)*0.65, MOD(quote,1)))*rate
Willy,
I'm trying to get my head around this. I got it to partially work but it seems to be working opposite.
=IF(C3,C3*F3,IF(D3,D3*F3,(INT(b3)+MAX((MOD(b3,1)>=0.5)*0.65, MOD(b3,1)))*f3))
I didn't know if this meet your needs.
Willy,
Perfect solution!
Works exactly as I wanted it to and preserves the ability to use decimals other than .5.
Thank you!
- Great job Willy!
Thanks, Damien. It is because you asked the sample. If not, I wouldn't know about column C & D.
