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

Calculation of Sum of 17 Variables with multiple attributes

N

Nilesh Parekh

New Member
I wanted to calculate the sum of 17 variables. Each variable has a multiple attribute points ranging from 2 to 6. Each attribute carries a weightage.
For E.g. Variable is Education. Its Attributes are Graduate, Post Graduate, PhD, Professional. The weightage to these attributes are 6, 18, 20, 22 respectively.
The total number of output with all the permutation & combination will be 223,948,800,000.
Is there any way that excel can help in calculating the output. An output which indicates the number of combinations with a Score would also help. For e.g. The score of 400 would be 25000, that with 401 would be 27000
 
Hi Nilesh ,

I have downloaded your file , and am not able to understand head or tail of it ; you give the following example :
For E.g. Variable is Education. Its Attributes are Graduate, Post Graduate, PhD, Professional. The weightage to these attributes are 6, 18, 20, 22 respectively.
Click to expand...
I am not able to find any of these attributes in your file ; all I can see is some numbers 0 , 5 , 10 , 13 , 15.

Similarly , for Gender , there are two values 15 and 0.

If I understand you correctly , you want all combinations of these variables , but which are the variables ? You mention a figure of 17 , but there are 19 columns in your table.

I think having posted the question , and uploaded a sample workbook , more explanations are required.

Can you post at least one sample output , indicating how you arrived at the output ?

Narayan
 
Hi Narayan,

The weightage to the attributes have already been assigned by us. Each person will be measured on all these variables. And the weightage for the variable will be assigned to him on th basis of the attribute that he has. For e.g We can have one person whose educational qualification would be PhD. In that scenario we will assign him a weightage of 20. Similarly we will measure him on all the variables.

We want to arrive at a method to find the total of all the variables with all the permutation combination possible.

I have attached a sample for your reference

Thanks.
 

Attachments

Hi Nilesh ,

It's good that the uploaded file has all the attributes ; one point has been explained.

However , the other point , as to the desired output , is still unclear.

Suppose we take just 2 attributes viz. Nationality and Age ; the possible combinations and their sums are :

Code: 
Nationality          Age
Group 1  45        < 28      0
Group 2  30    >=28 and <33  9
Group 3  25    >=33 and <38  22
Group 4  20    >=38 and <45  25
Group 5   0    >=45 and <52  18
Group 6  15        > 52      9

45 + 0 , 45 + 9 , 45 + 22 , 45 + 25 , 45 + 18
30 + 0 , 30 + 9 , 30 + 22 , 30 + 25 , 30 + 18
.
.
.
15 + 0 , 15 + 9 , 15 + 22 , 15 + 25 , 15 + 18

Do you want that you want all possible combination sums to be listed ? What is the objective of this ?

Narayan
 
Hi Narayan,

Sorry for the delay as I was away on a holiday. We want to add up all the 17 variables and group the totals in the range of 0 to 125, 126 to 225, 226 to 300, 301 to 400 & 401 to 500.
We want to know how many combinations will yield results in the ranges above.

Attached is an updated sample.

Nilesh.
 
Hi Nilesh ,

Your workbook is not really self-explanatory.

You have 19 variables in all ; in your workbook , you have kept the first 17 constant , and varied only the last 2. The number of combinations is therefore only 24 ; these can be listed and the counts , minimum , maximum determined easily.

If you are now looking for a different scenario where all 19 can be varied , firstly listing the combinations is impossible within the limits of an Excel worksheet ; if you do not want a listing , then a minimum and maximum is possible , since we only need to update these two values for every combination. Putting the counts in the different bins may also be possible , but I am not able to understand how this analysis will be used. Suppose we know that the number of combinations which give a total of between 301 and 400 is 1,73,534 ; how will this information be of any use ?

Narayan
 
Dear Narayan,

Yes you are right, there are 19 variables that we have. However, the sheet that you were checking is the one I used to calculate the sum of all the 19 variables. The attributes of these 19 variables are different and the scores attached to the attributes is different. This is provided in the sheet named Variables.
I want to find all the permutation combination of sum of the various attributes of all the 19 variables. The total permutation combination of these variables are close to 224 bn. It is humanly impossible to review each of these. So what I am looking at is an count output of number of combinations which will yield me results in the range specified.

Regards,
Nilesh
 

Attachments

Hi Nilesh ,

I think this is a fruitless exercise ; if you know that the number of comibnations is in excess of 200 billion , then surely the counts in each of your 5 categories will also be in millions , if not in billions ; how is the accurate count really going to help ?

Even if we need the counts in each of the 5 bins , the procedure still has to sum up each combination ; calculating for more than 200 billion items is time-consuming , and I aborted the procedure after it had completed a billion !

Narayan
 
You must log in or register to reply here.
Back
Top