Allocated amount rounded to nearest .25 without exceeding total

[sheri]

I'd like to be able to allocate an amount to the nearest .25 without
exceeding the total being allocated. How do I do this? I'm working with
hours and allocating overtime between projects. example: Project 1 hours =
40 Project 2 hours = 8 OT is 5.25. I need to allocate the 5.25 to each of
the projects based on their ratio without exceeding 5.25. Currently using
ROUND((C5*F6)/0.25,0)*0.25 where c5 is the OT (5.25) and F6 is % of project
time to total time (ex. 40/48). The results are Project 1= 4.5 and Project 2
= 1 but this exceeds the total of 5.25.

 

[Jacob Skaria]

Try the below..
=ROUND((C5*F6)/0.25,2)*0.25
which brings to exactly 5.25

OR
=ROUND((C5*F6)/0.25*0.25,2)

If this post helps click Yes

 

[Pete_UK]

Instead of having the same formula for the second time allocation,
just subtract the first calculated time from the Overtime - then they
will always add up to the total.

Hope this helps.

Pete

 

[sheri]

Good point. Feeling dumb now but thanks

Instead of having the same formula for the second time allocation,
just subtract the first calculated time from the Overtime - then they
will always add up to the total.

Hope this helps.

Pete

 

[sheri]

Neither worked, both game out with fractions not rounded to nearest .25
Thanks tho.

 

[Jacob Skaria]

I mean to say you get the different because you are using Round...which
rounds up both calculation and the sum is more than 5.25

If this post helps click Yes

 

[Pete_UK]

You're welcome, Sheri - thanks for feeding back.

Pete