Excel Formulas to generate random values adding upto 100

coolguyvarun01

New Member
Joined
Jan 5, 2015
Messages
25
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:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,149
Office Version
2019
Platform
Windows
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.
 

Forum statistics

Threads
1,081,835
Messages
5,361,596
Members
400,640
Latest member
fruitbros

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top