Home 
Search 
Today's Posts 
#1




Getting Excel to Calculate All Combinations of a Set of Data?
I only have a very very basic knowledge of Excel so I hope I make enough sense! Anyway I've found myself having to use a spreadsheet to calculate some acoustics problems, (problematic frequencies, called modes, in orders ranging from 0,1,2,3....). Ideally what I want to be able to do is simply to get Excell to calculate all the possible combinations of these modes. A combination could be: 000 011 101 110 111 012 143 etc. Obviously this is a nightmare to input by hand, is there any kind of function I can use to help with this process? Also these frequencies only need to be calculated up to a certain point, so is it possible I could also instruct the cell to only display a value if it is less than a value in another cell? Many thanks, I hope I haven't been too confusing!  marello  marello's Profile: http://www.excelforum.com/member.php...o&userid=28566 View this thread: http://www.excelforum.com/showthread...hreadid=482262 
#2




Getting Excel to Calculate All Combinations of a Set of Data?
Go to google.com and look up excel and permutations
 Regards, Peo Sjoblom "marello" wrote in message ... I only have a very very basic knowledge of Excel so I hope I make enough sense! Anyway I've found myself having to use a spreadsheet to calculate some acoustics problems, (problematic frequencies, called modes, in orders ranging from 0,1,2,3....). Ideally what I want to be able to do is simply to get Excell to calculate all the possible combinations of these modes. A combination could be: 000 011 101 110 111 012 143 etc. Obviously this is a nightmare to input by hand, is there any kind of function I can use to help with this process? Also these frequencies only need to be calculated up to a certain point, so is it possible I could also instruct the cell to only display a value if it is less than a value in another cell? Many thanks, I hope I haven't been too confusing!  marello  marello's Profile: http://www.excelforum.com/member.php...o&userid=28566 View this thread: http://www.excelforum.com/showthread...hreadid=482262 
#3




Getting Excel to Calculate All Combinations of a Set of Data?
Thanks for the pointer but I didnt manage to find much on permutations. What I did find implied that a permutation function just calculates how many different combinations there can be of a set of numbers. I want to actually be able to calculate each combination. I'd be very grateful for an explanation! thanks  marello  marello's Profile: http://www.excelforum.com/member.php...o&userid=28566 View this thread: http://www.excelforum.com/showthread...hreadid=482262 
#4




Getting Excel to Calculate All Combinations of a Set of Data?
"marello" wrote in
message ... Thanks for the pointer but I didnt manage to find much on permutations. What I did find implied that a permutation function just calculates how many different combinations there can be of a set of numbers. I want to actually be able to calculate each combination. I'd be very grateful for an explanation! thanks Are you talking of Permutations or Combinations? They are two different things! Do you want a definition and a formula to calculate how many Perms/Combs are available given n objects OR do you want some code to write all Perms/Combs from n objects? Bruno 
#5




Getting Excel to Calculate All Combinations of a Set of Data?
I don't know which I need! Say if I have values 15 I want excel to be able to calculate all combinations of them and then in seperate cell for each combination add them together to give the value for each combination.  marello  marello's Profile: http://www.excelforum.com/member.php...o&userid=28566 View this thread: http://www.excelforum.com/showthread...hreadid=482262 
#6




Getting Excel to Calculate All Combinations of a Set of Data?
"marello" wrote in
message ... I don't know which I need! ??? Say if I have values 15 I want excel to be able to calculate all combinations of them and then in seperate cell for each combination add them together to give the value for each combination. These are five values: 1 2 3 4 5 and these all the possible Combinations: C(5,1) = 5 1 2 3 4 5 C(5,2) = 10 12 13 14 15 23 24 25 24 35 45 C(5,3) = 10 123 124 125 134 135 145 234 235 245 345 C(5,4) = 5 1234 1235 1245 1345 2345 C(5,5) = 1 12345 Now what do you want to do? Bruno 
#7




Getting Excel to Calculate All Combinations of a Set of Data?
It seems that people cannot determine whether you are looking for
permutations or combinations ... If you have the digits 1 to 5, 120 different *permutations* can be generated, consisting of the 5 digits arranged in different sequences, e.g. 12345 12354 12435 12453 .. .. etc. down to 54321 These digits will all add up to the same value of 15. On the other hand, you can generate 10 *combinations* of 2 digits each from this set of digits, e.g. 12 13 14 15 23 24 25 34 35 45 or 10 combinations of 3 digits each 123 124 125 134 135 145 234 235 245 345 or 5 combinations of 4 digits each 1234 1235 1245 1345 2345 or 1 combination of 5 digits 12345 or 5 "combinations" of 1 digit each 1,2,3,4,5 (if needed!), making a total of of 31 *combinations* of from 1 to 5 digits. The combinations will tend to add up to different numbers, although some combinations will add up to the same value as others, e.g. 34 and 25. What people need to know in order to help you, is, when you have 5 objects, do you want to generate all 120 *permutations* of the 5 objects or all 31 *combinations* of size 1 to 5, as described above, or a subset of combinations of N objects each. Permutations are all about sequence; combinations are all about selecting subsets, sequence is not important. In answering, you need to use the words permutations and combinations as they are used above. If it is permutations you want, look here http://www.jwalk.com/ss/excel/tips/tip46.htm 
#8




Getting Excel to Calculate All Combinations of a Set of Data?
I'm sorry to be causing so much confusion! Let me write my problem out properly and try and see if that makes things clearer. This spreadsheet is calculating problematic frequencies in a room. The modes are multiple of each other, and each wall length causes a mode. So..... Mode number x frequency y frequency z frequency 0 0 0 0 1 22 50 33 2 44 100 66 3 88 200 132 As well as each wall causing a mode, x,y and z can interact with each other and sum their own frequencies to cause a new one. i need to be able to calculate all possible combinations of x,yand z. eg. 011 = 83 111= 110 321 = 221 I have a feeling I'm wanting combinations? But will combinations be enough to instruct the spreadsheet to instruct the spreadsheet to sum each possible value of the combination together and list all the results? I have a feeling that was clear as mud, I'm sorry it's hard as I don't quite understand what I want I'm not sure how to ask! Many thanks for all your help  marello  marello's Profile: http://www.excelforum.com/member.php...o&userid=28566 View this thread: http://www.excelforum.com/showthread...hreadid=482262 
#9




Getting Excel to Calculate All Combinations of a Set of Data?
"marello" wrote in
message ... I'm sorry to be causing so much confusion! Let me write my problem out properly and try and see if that makes things clearer. This spreadsheet is calculating problematic frequencies in a room. The modes are multiple of each other, and each wall length causes a mode. So..... Mode number x frequency y frequency z frequency 0 0 0 0 1 22 50 33 2 44 100 66 3 88 200 132 As well as each wall causing a mode, x,y and z can interact with each other and sum their own frequencies to cause a new one. i need to be able to calculate all possible combinations of x,yand z. eg. 011 = 83 111= 110 321 = 221 Why not: 011 = 41 111= 253 321=67 Or: 011=51 111=166 321=15 ??? Bruno 
#10




Getting Excel to Calculate All Combinations of a Set of Data?
From what you have shown, it appears to me that what you want is this:
Given frequencies x, y, and z, generate all values pqr, where p can vary from zero to p_max, q can vary from zero to q_max, and r can vary from zero to r_max. Then generate the value p*x+q*y+r*z. If this is true, try this; it may be what you need: Put the values for x, y, and z, in A1, B1 and C1. (22, 50, and 33 in your example) Put the maximum values you want for p, q, and r in A2, B2, and C2. Put zeros in A3, B3, and C3. Put the following formulas in their respective cells: A4: =IF(AND(B4=0,C4=0),A3+1,A3) B4: =IF(C4<0,B3,IF(B3<$B$2,B3+1,0)) C4: =IF(C3=$C$2,0,C3+1) D4: =A4&B4&C4 E4: =SUMPRODUCT($A$1:$C$1,A4:C4) Now copy/drag these five formulas down until you have reached values equal to p_max, q_max and r_max in each of columns A, B and C. If p,q and r = 9 this will be about 1000 rows, if they each equal n, it is about (n+1)^3 rows. That should produce the appropriate sums of the three frequencies where the value in column D represents the mode, although you may not need this column, since cols A, B, and C provide the same information. Is this close to what you want? 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How to import data from a password protected Access DB into Excel.  Excel Discussion (Misc queries)  
How do I import formmail data to a custom excel template?  Excel Worksheet Functions  
Linking the data from one excel to another  Excel Discussion (Misc queries)  
Importing xml Data into Excel 2002  Excel Discussion (Misc queries)  
Will not calculate average/median formulas;acts like no data in c.  Excel Worksheet Functions 