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

lorne17

New Member
Joined
Mar 28, 2017
Messages
15
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>
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Welcome to the forum

MROUND will allow you to round to a custom number (0.5, in your scenario)


Excel 2010
ABCD
1NumberDivisorResultMround
2741.752
374.11.7073171.5
Sheet1
Cell Formulas
RangeFormula
C2=A2/B2
D2=MROUND(C2,0.5)
 
Upvote 0
Maybe ...

Code:
=IFERROR(MROUND(
       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)))))), 0.5), "No Hours")
 
Upvote 0
I see what you're after, however I still need the first part that reads "PAST" if the L$4 is older than the current date.

How might that work? I tried this and there are too many arguments:

Code:
=IFERROR(MROUND(
IF(D$4<TODAY(),"PAST",
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")
 
Upvote 0
Whoops... I meant I tried this:

Code:
=IFERROR(MROUND(
IF(D$4<TODAY(),"PAST",
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")
 
Upvote 0
Why does it keep hiding the portion of the formula that is my first line?? IF ( D $ 4 < T O D A Y ( ) , " P A S T ", I had to add spaces between each so the forums didn't delete it? It has in ALL my posts so far!

Capture.png
[/URL][/IMG]
 
Upvote 0
Because the board treats < when immediately followed by text as an html tag and gets all confused.

Post your formula in code tags (see examples in this thread)
 
Upvote 0
You're missing an AND function in the second line.
 
Upvote 0
Because the board treats < when immediately followed by text as an html tag and gets all confused.

Post your formula in code tags (see examples in this thread)

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

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top