Excel Formulas to generate random values adding upto 100

coolguyvarun01

New Member
Joined
Jan 5, 2015
Messages
27
Office Version
  1. 2019
Platform
  1. Windows
Hi Experts,

Need your help in this. My boss wants me to create values for a group of materials bifurcated on basis of pack x Channel, now the values for a pack/channel combination should sum up to 100.


UnitBrandChannelPackValue%
EASTProduct 1InstiMedium
EASTProduct 1InstiSmall
EASTProduct 1RailwaysMedium
EASTProduct 1RailwaysSmall
EASTProduct 1SAMTMedium
EASTProduct 1WSMedium
EASTProduct 1WSSmall
EASTProduct 1TT RetailMedium
EASTProduct 1TT RetailSmall
EASTProduct 1SpokeMedium
EASTProduct 1SpokeSmall
EASTProduct 1SAMTSmall
WESTProduct 2InstiMedium
WESTProduct 2InstiSmall
WESTProduct 2RailwaysMedium
WESTProduct 2RailwaysSmall
WESTProduct 2WSMedium
WESTProduct 2WSSmall
WESTProduct 2TT RetailMedium
WESTProduct 2TT RetailSmall
WESTProduct 2SpokeMedium
WESTProduct 2SpokeSmall
WESTProduct 2SAMTMedium
WESTProduct 2SAMTSmall
NORTHProduct 3InstiMedium
NORTHProduct 3InstiLarge
NORTHProduct 3SpokeExtra Large
NORTHProduct 3TT RetailMedium
NORTHProduct 3TT RetailLarge
NORTHProduct 3TT RetailSmall
NORTHProduct 3TT RetailExtra Large
NORTHProduct 3WSMedium
NORTHProduct 3WSLarge
NORTHProduct 3WSSmall
NORTHProduct 3WSExtra Large
NORTHProduct 3SpokeMedium
NORTHProduct 3SpokeLarge
NORTHProduct 3SpokeSmall
NORTHProduct 3SAMTLarge
NORTHProduct 3SAMTSmall
NORTHProduct 3SAMTExtra Large
NORTHProduct 3RailwaysExtra Large
NORTHProduct 3SAMTMedium
NORTHProduct 3RailwaysMedium
NORTHProduct 3RailwaysLarge
NORTHProduct 3RailwaysSmall
NORTHProduct 3InstiSmall
NORTHProduct 3InstiExtra Large
SOUTHProduct 4SpokeLarge
SOUTHProduct 4SpokeMedium
SOUTHProduct 4SpokeSmall
SOUTHProduct 4SpokeExtra Large
SOUTHProduct 4TT RetailLarge
SOUTHProduct 4TT RetailMedium
SOUTHProduct 4TT RetailSmall
SOUTHProduct 4TT RetailExtra Large
SOUTHProduct 4SAMTMedium
SOUTHProduct 4SAMTSmall
SOUTHProduct 4SAMTExtra Large
SOUTHProduct 4RailwaysExtra Large
SOUTHProduct 4SAMTLarge
SOUTHProduct 4RailwaysLarge
SOUTHProduct 4RailwaysMedium
SOUTHProduct 4RailwaysSmall
SOUTHProduct 4InstiMedium
SOUTHProduct 4InstiSmall
SOUTHProduct 4InstiExtra Large
SOUTHProduct 4WSExtra Large
SOUTHProduct 4InstiLarge
SOUTHProduct 4WSLarge
SOUTHProduct 4WSMedium
SOUTHProduct 4WSSmall

<tbody>
</tbody>
 
Last edited:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Assuming that your example table is in A1:E73, in E2 enter

=RAND()

In F2, enter

=E2/SUMIFS($E$2:$E$73,$C$2:$C$73,C2,$D$2:$D$73,D2)

Fill both formulas down to the end of the table, format column F as percentage.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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