Distributing % evenly

  • Thread starter Thread starter Denise Posey
  • Start date Start date
D

Denise Posey

If someone could please tell me how to alter this problem.

Here is what I have created:
Total # of people / Total # of work = # per person.

If someone is out for the day, they will put "OUT" on the form. I used =COUNTBLANK(B7:B55)for my formula (Four people out would give me 30 for the day.

Cell B59 =SUM(B57/B56) 325/30 = 11
IN Column C there is a cell for each person that will be (blank cell) to allow for how much work they will receive that day
=B59*(IF(ISBLANK(B7),1)) ---- 11 put in cell

But my problem is 11 * 30 = 330 not 325.
How do I redistribute the 11 to all cells so that they equal 325. I can't figure out where to take the difference of 5 from.

I appreciate any help that can be offered. I now very little coding. Am at the end of taking class on Simply VB.Net

Denise Posey
Texas
 
Denise, can you explain your problem a bit more clearly?

--

Vasant

If someone could please tell me how to alter this problem.

Here is what I have created:
Total # of people / Total # of work = # per person.

If someone is out for the day, they will put "OUT" on the form. I used
=COUNTBLANK(B7:B55)for my formula (Four people out would give me 30 for the
day.

Cell B59 =SUM(B57/B56) 325/30 = 11
IN Column C there is a cell for each person that will be (blank cell) to
allow for how much work they will receive that day
=B59*(IF(ISBLANK(B7),1)) ---- 11 put in cell

But my problem is 11 * 30 = 330 not 325.
How do I redistribute the 11 to all cells so that they equal 325. I can't
figure out where to take the difference of 5 from.

I appreciate any help that can be offered. I now very little coding. Am at
the end of taking class on Simply VB.Net

Denise Posey
Texas
 
-----Original Message-----
Denise, can you explain your problem a bit more clearly?

--

Vasant

If someone could please tell me how to alter this problem.

Here is what I have created:
Total # of people / Total # of work = # per person.

If someone is out for the day, they will put "OUT" on the form. I used
=COUNTBLANK(B7:B55)for my formula (Four people out would give me 30 for the
day.

Cell B59 =SUM(B57/B56) 325/30 = 11
IN Column C there is a cell for each person that will be (blank cell) to
allow for how much work they will receive that day
=B59*(IF(ISBLANK(B7),1)) ---- 11 put in cell

But my problem is 11 * 30 = 330 not 325.
How do I redistribute the 11 to all cells so that they equal 325. I can't
figure out where to take the difference of 5 from.

I appreciate any help that can be offered. I now very little coding. Am at
the end of taking class on Simply VB.Net

Denise Posey
Texas


.
I didn't think I explained that too clearly. I can send the form to you so you can see what it is I am trying to do. I am trying to determine how to caculate a number that tells me how much a particular person is going to receive in work that day and distribute it evenly so that it adds up to my total number.
Click to expand...

Again if you take 325 / 30 = 10.83 (rounded to 11)
but 11 x 30 = 330. So not all 30 people are going to receive 30 pieces of mail to work. There are 5 (in this case) that are going to receive 1 less. How do you divide & distribute evenly to = your beginning number?
 
You don't. Your first formula:-

Cell B59 =SUM(B57/B56) 325/30 = 11

does not result in 11, or at least not exactly. You must have the cell rounded
up to 0dps (0 decimal places) for that happen, because it really gives you
10.8333' (recurring).

This is also the amount that should show in any of your other cells in Col C, as
all they are doing is pulling in this number from B59. You need to increase
the number of dps for the cells in this area as well.

This all assumes that partial numbers are OK, but if not then you will have to
come up with some way of handling the extra work by having some people do
more/less than the average, eg 25 people do 11 and 5 people do 10.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Newsgroups - Where you really can get a free lunch!!
----------------------------------------------------------------------------



If someone could please tell me how to alter this problem.

Here is what I have created:
Total # of people / Total # of work = # per person.

If someone is out for the day, they will put "OUT" on the form. I used
=COUNTBLANK(B7:B55)for my formula (Four people out would give me 30 for the day.

Cell B59 =SUM(B57/B56) 325/30 = 11
IN Column C there is a cell for each person that will be (blank cell) to allow
for how much work they will receive that day
=B59*(IF(ISBLANK(B7),1)) ---- 11 put in cell

But my problem is 11 * 30 = 330 not 325.
How do I redistribute the 11 to all cells so that they equal 325. I can't
figure out where to take the difference of 5 from.

I appreciate any help that can be offered. I now very little coding. Am at the
end of taking class on Simply VB.Net

Denise Posey
Texas
 
I have it set to 0dps but it still using the rounding in the background. I understand that this is all my dilemna, but can you tell me how to program it so that it will process that way?

Denise
 
You need to be clear about what you are trying to achieve though. It is easy
enough to fix the formula in any of those cells so that it actually rounds it,
and doesn't just round the displayed value, but then you are back to your
original problem.

With the data as it currently stands you have a real answer of 10.83333', and if
you multiply this by 30 you will get 325 - That si a completely even distibution
of the work across your 30 people. If however, you round the data in cell B59
so that you actually get 11, then each person will get 11 instead of 10.83333
and so 30 * 11 = 330 not 325. You can't have it both ways. 325 cannot be
divided equally amongst 30 people and still have every number be an integer.

Stick with your example data and tell me what number you think everyone should
have if not 10.83333, because 30 times that = 325, so the second you add any
more by rounding up, you go above 325. You cannot round your data up keeping
all values the same, and yet keep your total sum the same amount.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Newsgroups - Where you really can get a free lunch!!
----------------------------------------------------------------------------



I have it set to 0dps but it still using the rounding in the background. I
understand that this is all my dilemna, but can you tell me how to program it so
that it will process that way?

Denise
 
Back
Top