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
---------------
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.

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.

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.

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.

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.

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 -