Rounding/Formula problem

[JB]

I have rounding/formula problem.

The user inputs the number of whole hours to complete a task. This
number is then allocated to milestones by a formula. The milestone
must be in whole hours. So, the allocation is 25% for each milestone
of 4 total milestones. When I do the calculation using Round or other
rounding functions I get 3 hours at each milestone and of course the
total is 12 hours using +( (round #,0)+ (round #,0) (round #,0) (round
#,0)). Is there a formula or function to the get the milestones in
this instance to be 3,2,2,3 say or some other combination that total
to 10?
Thanks
JBESr

 

[Guest]

JB
You could use the ROUNDUP function on two cells, and the ROUNDOWN function on the other two

Good Luck
Mark Graesse
(e-mail address removed)

----- JB wrote: ----

I have rounding/formula problem

The user inputs the number of whole hours to complete a task. Thi
number is then allocated to milestones by a formula. The mileston
must be in whole hours. So, the allocation is 25% for each mileston
of 4 total milestones. When I do the calculation using Round or othe
rounding functions I get 3 hours at each milestone and of course th
total is 12 hours using +( (round #,0)+ (round #,0) (round #,0) (roun
#,0)). Is there a formula or function to the get the milestones i
this instance to be 3,2,2,3 say or some other combination that tota
to 10
Thank
JBES

 

[J.E. McGimpsey]

One way:

Say A1 contains the total hours and B1:B4 the hours per milestone.
To allocate "extra" hours to the milestones in the order 1, 4, 2, 3,
(which is one pattern that satisfies your 3,2,2,3 criterion):

B1: =ROUNDUP(A1/4,0)
B2: =ROUNDDOWN((A1+1)/4,0)
B3: =ROUNDDOWN(A1/4, 0)
B4: =ROUNDDOWN((A1+2)/4,0)

 

[JB]

The roundup and rounddown works for this one allocation. However
there are many allocations for the milestones depending on the type of
task. To clarify, some of the allocations are as follows and roundup
and rounddown methodolgy does not work for them. I was hoping to be
able to use one formula or function for them all.

Milestone _1__ _2__ __3_ _5__
Task 1 15% 0% 85% 0%
Task 2 15% 30% 25% 30%
Task 3 5% 0% 95% 0%

Thanks for your ideas.

JB

 

[Guest]

JB,
For the example you gave you should be able to use the ROUNDUP and ROUNDDOWN functions. If you make milestones 1 and 2 ROUNDUP and milestones 3 and 5 ROUNDDOWN the total should always be 100%. The reason this will work is because you will always have the same number of calculated milestones since each task has either 2 or 4 calculated values. If you have other instances with an odd number of calculations I can see that this won't work.

One other option is to lump all of the extra (or take out the overage) from one milestone. Instead of directly calculating the percentage for milestone 3 you could use something like:

=1-SUM(all other milestone percentages)

However, in your original scenario you will end up with 3,3,1,3 instead of 3,2,2,3.

Are there other percentage breakdown then the ones you gave as examples? Are the percentages always in all milestones, and 1 and 3 only? A little more info on the possible percentage breakouts and I think I can put together some IF statements which might do what you need.

Regards,
Mark Graesser
(e-mail address removed)

----- JB wrote: -----

The roundup and rounddown works for this one allocation. However
there are many allocations for the milestones depending on the type of
task. To clarify, some of the allocations are as follows and roundup
and rounddown methodolgy does not work for them. I was hoping to be
able to use one formula or function for them all.

Milestone _1__ _2__ __3_ _5__
Task 1 15% 0% 85% 0%
Task 2 15% 30% 25% 30%
Task 3 5% 0% 95% 0%

Thanks for your ideas.

JB