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

Distribution of Values

fareedexcel

fareedexcel

Member
Hi Users,

I need to distribute 10000 points between different rows (Names) where each should get atleast minimum of 150 points.
 

Attachments

  • Distributions.xlsx
    11.2 KB · Views: 11
bosco_yip said:
Just guessing,

In F3, formula copied down :

=IF(E3>=I$3,ROUND(E3/SUMIF(E$3:E$51,">="&I$3)*(I$2-(COUNTIF(E$3:E$51,"<="&I$3)*I$3)),0),I$3)
Click to expand...
I tried this formula. But the values are not getting equal distributed most of the cells having the value as 150.

The value to be distributed according to the Numbers proportion. If Column Numbers has only 1 FTE, then the minimum value to be 150
 
You do realise that this can't be solved using a linear distribution?

Firstly there are 35 records with a value less than 123.9, that is records whose value is less than a value to get 150 points
That uses up 5,250 points leaving 10,000-5,250=4,750 to be distributed to the remaining 13 values

Now due to the range of the remaining 13 values, you need 6,706 points to distribute accross those 13 values.
Problem is that now some of these 13 values are very large and that means the remaining values get less than 150 points

So you have already used 5,250+6,706=11,956 points, Fail
 
fareedexcel said:
Any help ?
Click to expand...
Patience. This is a FREE forum manned by volunteers all around the planet which might be sleeping while you are asking questions.
Usual consensus is waiting 24 hrs before bumping. Putting members under pressure might deter them form answering

To get fast answers lots of pay sites are available
 
Thanks for your comment. In my original data set, the total points provided is 210,000 which needs to be distributed across each groups.
The groups values are dynamic
So if the group value has only 1 FTE then minimum point of 150 to be allocated to that group
If Group value is 0 FTE, then that particular group to be excluded and the distribution to be done based on other group FTEs.
 
pecoflyer said:
Patience. This is a FREE forum manned by volunteers all around the planet which might be sleeping while you are asking questions.
Usual consensus is waiting 24 hrs before bumping. Putting members under pressure might deter them form answering

To get fast answers lots of pay sites are available
Click to expand...
Thanks for your message.
 
You must log in or register to reply here.
Back
Top