Allocated amount rounded to nearest .25 without exceeding total
 

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.

Allocated amount rounded to nearest .25 without exceeding total
 

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
---------------
Jacob Skaria


"sheri" wrote:

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.

Allocated amount rounded to nearest .25 without exceeding total
 

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

On Aug 24, 5:24*pm, sheri wrote:
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.

Allocated amount rounded to nearest .25 without exceeding tota
 

Good point. Feeling dumb now but thanks
"Pete_UK" wrote:

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

On Aug 24, 5:24 pm, sheri wrote:
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.

Allocated amount rounded to nearest .25 without exceeding tota
 

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"sheri" wrote:

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.

Allocated amount rounded to nearest .25 without exceeding tota
 

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
---------------
Jacob Skaria


"sheri" wrote:

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"sheri" wrote:

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.

Allocated amount rounded to nearest .25 without exceeding tota
 

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

Pete

On Aug 24, 6:07*pm, sheri wrote:
Good point. *Feeling dumb now but thanks



"Pete_UK" wrote:
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

On Aug 24, 5:24 pm, sheri wrote:
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.- Hide quoted text -

- Show quoted text -