How to Round to nearest (up or down) 1/2 inside IF Formula?

[lorne17]

Is there a way to round this formula to nearest .5 decimal with the IF formula I have? I can get it working, but when "PAST" or "NO HRS" comes out of the formula, I then try to devide and multiple and get the #VALUE! error.


How can I round this and still get the "PAST" or "NO HRS" when the IF portion of the formula comes true?


=ROUND(IF(L$4<today(),"past",if(and(l$4>=$C$13,L$4<=$C$14),$Q22/$C$26,IF(AND(L$4>=$D$13,L$4<=$D$14),$Q30/$C$34,IF(AND(L$4>=$E$13,L$4<=$E$14),$Q38/$C$42,IF(AND(L$4>=$F$13,L$4<=$F$14),$Q38/$C$42,IF(AND(L$4>=$G$13,L$4<=$G$14),$Q46/$C$50,IF(AND(L$4>=$H$13,L$4<=$H$14),$Q54/$C$58,"NO HRS")))))))/0.5,0)*0.5


Thanks in advance,
Lorne</today(),"past",if(and(l$4>

 

[shg]

That second line used to say

IF(D$4=$C$13,D$4<=$C$14),$Q22/$C$26,

That's where you were missing the AND.

 

[lorne17]

That second line used to say

IF(D$4=$C$13,D$4<=$C$14),$Q22/$C$26,

That's where you were missing the AND.

I don't meant to keep repeating myself, but my first three posts were not showing the formula that I copied and pasted correctly. Please refer to my latest post that has an image in the message. That's the current state of my formula that is not functioning.

Thanks,
Lorne

 

[shg]

How about ...

=IF(D$4 < TODAY(), "PAST",
 IFERROR(MROUND(
    IF(AND(D$4 = $C$13,  D$4 <= $C$14), $Q22/$C$26,
    IF(AND(D$4 >= $D$13, D$4 <= $D$14), $Q30/$C$34,
    IF(AND(D$4 >= $E$13, D$4 <= $E$14), $Q38/$C$42,
    IF(AND(D$4 >= $F$13, D$4 <= $F$14), $Q38/$C$42,
    IF(AND(D$4 >= $G$13, D$4 <= $G$14), $Q46/$C$50,
    IF(AND(D$4 >= $H$13, D$4 <= $H$14), $Q54/$C$58)))))), 0.5),"NO HRS"))

 

[lorne17]

How about ...

=IF(D$4 < TODAY(), "PAST",
 IFERROR(MROUND(
    IF(AND(D$4 = $C$13,  D$4 <= $C$14), $Q22/$C$26,
    IF(AND(D$4 >= $D$13, D$4 <= $D$14), $Q30/$C$34,
    IF(AND(D$4 >= $E$13, D$4 <= $E$14), $Q38/$C$42,
    IF(AND(D$4 >= $F$13, D$4 <= $F$14), $Q38/$C$42,
    IF(AND(D$4 >= $G$13, D$4 <= $G$14), $Q46/$C$50,
    IF(AND(D$4 >= $H$13, D$4 <= $H$14), $Q54/$C$58)))))), 0.5),"NO HRS"))

That's it! Thank you!

 

[shg]

You're welcome.

 

[MARK858]

Thanks for the tip, you'll see that I did post them in code tags. Still was removing that part of the formula..weird...

Put a space after the < symbol