Formula for numbers of distribution as per %...into specific roundup

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,371
Office Version
  1. 2010
Using Excel 2010

Hello,

Code: 
Formula In cell C10…=RAND() and copied to down C19

Formula In cell D10…=C10/SUM(C$10:C$19) and copied to down D19

Formula In cell E10 =$E$7*D10 and copied to down E19

Here below is a layout without formulas (because as F9 is pressed or any entry done value changes I have put formulas apart sorry for the trouble) in which column “E” cells E10:E19 this is a result of 20 numbers are in the cell E7, as per % of the column “D” cells D10:D19 and it sums total 20 shown in the E9.

What I need A formulas in the cells G, H, And I

1-G10:G19 so the distribution of the 20 numbers could be in (0.20, 0.40, 0.60, 0.80 and 1.0) as shown. Note: the total always must be 20 matches with E7 numbers

2-1-H10:H19 so the distribution of the 20 numbers could be in (0.25, 0.50, 0.75 and 1.0) as shown. Note: the total always must be 20 matches with E7 numbers

3-1-I10:I19 so the distribution of the 20 numbers could be in (0.50 and 1.0) as shown. Note: the total always must be 20 matches with E7 numbers

Is it possible?

Excel Question-New.xlsm
ABCDEFGHIJK
1DistributionDistributionDistribution
2Increase ByIncrease ByIncrease By
30,200,250,50
40,400,501,00
50,600,75
6Distribution0,801,00
720,001,00
8
9NumbersRand %100,00%20,0020,0020,0020,00
1010,8170828413,59%2,722,602,502,50
1120,066590381,11%0,220,300,250,25
1230,462266287,69%1,541,401,501,50
1340,8853985214,72%2,943,003,003,00
1450,46784867,78%1,561,601,501,50
1560,9952014116,55%3,313,303,253,50
1670,6666060611,08%2,222,202,252,00
1780,9514973915,82%3,163,203,253,25
1890,15194922,53%0,510,600,750,50
19100,549376499,14%1,831,801,752,00
20
21
22
Sheet5


Please need help

Thank you all.

Regards,
Moti
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Hello Moti,

My suggested approach:
MrExcel_Formula for numbers of distribution as per percentage into specific roundup.xlsm
ABCDEFGHI
1DistributionDistributionDistribution
2542
3
4
5
6Distribution
720,00
8
9NumbersRand %100,00%20,0020,0020,0020,00
1010,411928358,25%1,651,601,751,50
1120,6264409912,54%2,512,602,502,50
1230,7234451714,48%2,903,003,003,00
1340,355060767,11%1,421,401,501,50
1450,7222230414,46%2,892,802,753,00
1560,8215003216,45%3,293,203,253,50
1670,311355576,23%1,251,201,251,00
1780,027125610,54%0,110,200,000,00
1890,788367615,78%3,163,203,253,00
19100,207817494,16%0,830,800,751,00
Sheet1
Cell Formulas
RangeFormula
E9,G9:I9E9=SUM(E10:E19)
G10:I19G10=TRANSPOSE(RoundToSum($E$10:$E$19*G2,0)/G2)
C10:C19C10=RAND()
D10:D19D10=C10/SUM(C$10:C$19)
E10:E19E10=$E$7*D10
Dynamic array formulas.


A sample file you can find here (download and use at your own risk, but I am using an up-to-date virus scanning program):

Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com

Regards,
Bernd
 
Upvote 0
Solution
Sulprobil said:
Hello Moti,

My suggested approach:
MrExcel_Formula for numbers of distribution as per percentage into specific roundup.xlsm
ABCDEFGHI
1DistributionDistributionDistribution
2542
3
4
5
6Distribution
720,00
8
9NumbersRand %100,00%20,0020,0020,0020,00
1010,411928358,25%1,651,601,751,50
1120,6264409912,54%2,512,602,502,50
1230,7234451714,48%2,903,003,003,00
1340,355060767,11%1,421,401,501,50
1450,7222230414,46%2,892,802,753,00
1560,8215003216,45%3,293,203,253,50
1670,311355576,23%1,251,201,251,00
1780,027125610,54%0,110,200,000,00
1890,788367615,78%3,163,203,253,00
19100,207817494,16%0,830,800,751,00
Sheet1
Cell Formulas
RangeFormula
E9,G9:I9E9=SUM(E10:E19)
G10:I19G10=TRANSPOSE(RoundToSum($E$10:$E$19*G2,0)/G2)
C10:C19C10=RAND()
D10:D19D10=C10/SUM(C$10:C$19)
E10:E19E10=$E$7*D10
Dynamic array formulas.


A sample file you can find here (download and use at your own risk, but I am using an up-to-date virus scanning program):

Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com

Regards,
Bernd
Click to expand...
Hello Sulprobil, thank you for your efforts applying formulas in the cell G10:I19 =TRANSPOSE(RoundToSum($E$10:$E$19*G2,0)/G2) it results #NAME? Not the values.

There should be some error in the formula.

Regards,
Moti
 
Upvote 0
Hello Moti,

RoundToSum is a user defined function, not a built-in Excel function.
You find it in the sample file provided.

Regards,
Bernd
 
Upvote 0
Hello Moti,

I am truly sorry but I am afraid you either face the horrible option to use VBA, or the terrible option to wait for Microsoft to come up with a similar built-in function, or the (awful, I dare say) option to wait for somebody else to offer a worksheet function / lambda expression / whatever other approach.

Regards,
Bernd
 
Upvote 0
Sulprobil said:
Hello Moti,

I am truly sorry but I am afraid you either face the horrible option to use VBA, or the terrible option to wait for Microsoft to come up with a similar built-in function, or the (awful, I dare say) option to wait for somebody else to offer a worksheet function / lambda expression / whatever other approach.

Regards,
Bernd
Click to expand...
Hello Sulprobil, I want to thank you for your solution and I must say it is giving exact output as per my query. (y)

I do use VBA’S but precisely for this work book I need only formula, searching in the Mrexcel I found a formula which works, but the total of the result does not match with selected value which is in the cell E7 might be need twist may someone can improve it let wait.

Here is a worksheet function which I found….

VBA Code: 
For cell G10:I19  =TRANSPOSE(SUM(ROUND($E$10:$E$19*G$2,0)/G$2))

Regards,
Moti
 
Last edited:
Upvote 0
Sulprobil said:
Hello Moti,

My suggested approach:
MrExcel_Formula for numbers of distribution as per percentage into specific roundup.xlsm
ABCDEFGHI
1DistributionDistributionDistribution
2542
3
4
5
6Distribution
720,00
8
9NumbersRand %100,00%20,0020,0020,0020,00
1010,411928358,25%1,651,601,751,50
1120,6264409912,54%2,512,602,502,50
1230,7234451714,48%2,903,003,003,00
1340,355060767,11%1,421,401,501,50
1450,7222230414,46%2,892,802,753,00
1560,8215003216,45%3,293,203,253,50
1670,311355576,23%1,251,201,251,00
1780,027125610,54%0,110,200,000,00
1890,788367615,78%3,163,203,253,00
19100,207817494,16%0,830,800,751,00
Sheet1
Cell Formulas
RangeFormula
E9,G9:I9E9=SUM(E10:E19)
G10:I19G10=TRANSPOSE(RoundToSum($E$10:$E$19*G2,0)/G2)
C10:C19C10=RAND()
D10:D19D10=C10/SUM(C$10:C$19)
E10:E19E10=$E$7*D10
Dynamic array formulas.


A sample file you can find here (download and use at your own risk, but I am using an up-to-date virus scanning program):

Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com

Regards,
Bernd
Click to expand...
Hello Sulprobil, after 90 views as there is no formula response I decide to use your workbook separately for this task so I download your workbook this has got as you said (RoundToSum is a user defined function) after opening I accept to enable contain and macros it enabled with all formulas and results.

But I am not getting the result as per your layout showing in the #Post2 I pressed several time F9 but no luck please can you guide what I am doing wrong?

Here below is the result of workbook what I am getting.

MrExcel_Formula for numbers of distribution as per percentage into specific roundup.xlsm
ABCDEFGHI
1DistributionDistributionDistribution
2542
3
4
5
6Distribution
720,00
8
9NumbersRand %100,00%20,0020,0020,0020,00
1010,5024671510,25%2,052,002,002,00
1120,057831531,18%0,242,002,002,00
1230,246582975,03%1,012,002,002,00
1340,8198638916,72%3,342,002,002,00
1450,5085258310,37%2,072,002,002,00
1560,343326197,00%1,402,002,002,00
1670,6673793713,61%2,722,002,002,00
1780,8107855916,53%3,312,002,002,00
1890,7879839616,07%3,212,002,002,00
19100,159695643,26%0,652,002,002,00
Sheet1
Cell Formulas
RangeFormula
E9,G9:I9E9=SUM(E10:E19)
G10:I19G10=TRANSPOSE(RoundToSum($E$10:$E$19*G2,0)/G2)
C10:C19C10=RAND()
D10:D19D10=C10/SUM(C$10:C$19)
E10:E19E10=$E$7*D10
Press CTRL+SHIFT+ENTER to enter array formulas.


Regards,
Moti
 
Upvote 0
Hello Moti,

Leave the Transpose command out (= do not use it).
You have an older Excel version.

Regards,
Bernd
 
Upvote 0
Sulprobil said:
Hello Moti,

Leave the Transpose command out (= do not use it).
You have an older Excel version.

Regards,
Bernd
Click to expand...
Hello Sulprobil, leaving the “Transpose” command out yes it worked one another question has come up I changed the value in Cell E7=10 and hitting F9 sometimes in the some of the column's cells there is “0.00” result I am not sure whether it can be avoided.

Please can you take a look?

MrExcel_Formula for numbers of distribution as per percentage into specific roundup.xlsm
ABCDEFGHI
1DistributionDistributionDistribution
2542
3
4
5
6Distribution
710,00
8
9NumbersRand %100,00%10,0010,0010,0010,00
1010,047369421,05%0,100,200,000,00
1120,678455215,00%1,501,401,501,50
1230,7587115516,77%1,681,601,751,50
1340,6898604615,25%1,531,601,501,50
1450,5226190611,55%1,161,201,251,00
1560,174739783,86%0,390,400,500,50
1670,0065140,14%0,010,000,000,00
1780,378168048,36%0,840,800,751,00
1890,8515115718,82%1,881,801,752,00
19100,415716969,19%0,921,001,001,00
Sheet1
Cell Formulas
RangeFormula
E9,G9:I9E9=SUM(E10:E19)
G10:I19G10=RoundToSum($E$10:$E$19*G2,0)/G2
C10:C19C10=RAND()
D10:D19D10=C10/SUM(C$10:C$19)
E10:E19E10=$E$7*D10
Press CTRL+SHIFT+ENTER to enter array formulas.


Regards,
Moti
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Count - specific consecutive order
Replies
6
Views
588
Peter_SSs
Peter_SSs
C
  • Question
Total Specific Sections in Auto-filled Spreadsheet
Replies
3
Views
78
Cubist
Cubist
T
  • Question
Time Sheet question
2
Replies
15
Views
581
tfowlerusa
T
U
  • Solved
Index formula with matching - Can it look at rows and columns?
Replies
2
Views
137
usnapoleon
U
R
  • Solved
Football Playoff Summary
2 3 4
Replies
33
Views
1K
smozgur
smozgur

Forum statistics

Threads
1,216,050
Messages
6,128,498
Members
449,455
Latest member
jesski

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top