Distribution of Values

fareedexcel

Member
Hi Users,

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

Attachments

• 11.2 KB Views: 11

Any help ?

bosco_yip

Excel Ninja
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)

fareedexcel

Member
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)
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

Hui

Excel Ninja
Staff member
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

pecoflyer

Active Member
Any help ?
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

fareedexcel

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

fareedexcel

Member
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