MROUND giving a #NUM! error as result is a minus number

[Allen_Mead]

So, am working out spare capacity of staff to the nearest half day and am using the following formula:

=MROUND(IFERROR((H8-SUM(H10))/8,""), 0.5)

where;
H8 = 40 (number of contract hours per week)
H10 = XX (number of hours booking per week)

the 8 in the formula above equates to the number of hours per day.

So, in the instance I am getting the error, H10 - 42 hours, 2 hours over the contracted hours per week. I'd like to be able to show this as a negative figure and not an error. If I change the formula to 'ROUND' and the number of places to '1' there's no error but then I get for example 4.3 days.

Many thanks

 

[Kerryx]

Round negative

 

[Kerryx]

Using Mroud your calculation will have to pos , pos or neg neg can't have a pos , neg so you need to work in a solution if value <0 e.g.
=MROUND(A1,IF(A1>0,0.05,-0.05))

 

[Allen_Mead]

Round negative

Aaaaahh hhaaa! That is so useful. Thank you for the link. Works perfectly.

I was ignorant regarding the use of MROUND

 

[Fluff]

How about

=LET(hr,(H8-H10)/8,MROUND(hr, 0.5*SIGN(hr)))

 

[Allen_Mead]

How about

=LET(hr,(H8-H10)/8,MROUND(hr, 0.5*SIGN(hr)))

Works perfectly thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.