Allocated amount rounded to nearest .25 without exceeding total

  • Thread starter Thread starter sheri
  • Start date Start date
S

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.
 
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
 
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
 
Good point. Feeling dumb now but thanks
Pete_UK said:
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
Click to expand...
 
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
 
Back
Top