• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with list, checking total

S

Scouse

New Member
Hi,


I am having a problem with my list of data. Here is the back ground the figure provided in column E is the total number of leaflets that I received , i want to ensure that no individual (listed in column A) gets less than 5. I also want to ensure that the overall total of column B is never a negative figure, it can equal zero.


In column C I have a weighting which is used to ensure that more important persons receive more items than lesser mortals.


Here is my formula that I am using in column B


Code: 
=(ROUND(SUM(E$3 * C5),0)*2)


The formula rounds up the answer so that it is an even number.


http://cid-4afa362f91d4ccc5.office.live.com/view.aspx/Public/Leaflets%20Distribution%20List.xls
 
I am stumped.. you have a total of 150 leaflets. and more than 30 people in column A. That means some of them are going to get less than 5.


Also, column b cant be negative if you use a formula like above. I am not sure if I understood your question.


Can you rephrase it or let us know where you are struggling...
 
Scouse

The Total of your weighting's adds up to 0.463, should it be 1.00?


If you pro-rata your 150 items across the weightings using

B5: =+ROUND((C5/SUM($C$5:$C$46))*$E$3,0)

and copy down, that will distribute 148 of the 150 items


But as Chandoo pointed out you can't have more than 5 per person with 150 items between 40 people, you need at least 200 items without taking into account weighting


In fact you need 318 items to ensure that using the weightings each person gets at least 5 items.

(Do a Goal Seek with the above formula in Column B)
 
You must log in or register to reply here.
Back
Top