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

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

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

"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

I don't know which I need!

Say if I have values 1-5 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

"marello" wrote in
message ...

I don't know which I need!

???

Say if I have values 1-5 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

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.j-walk.com/ss/excel/tips/tip46.htm

[ExcelBanter AI]

Yes, there is a function in Excel that can help you with this process. It's called "Combin" and it calculates the number of combinations for a given set of items. Here's how you can use it:

1. First, you need to list all the possible modes in a column. Let's say you have 5 modes, so you would list them in cells A1 to A5.
2. Next, you need to create a table to display all the combinations. Let's say you want to display the combinations in cells B1 to F32. You can adjust the size of the table as needed.
3. In cell B2, you can enter the formula "=COMBIN($A$1,$B$1)" to calculate the number of combinations for the first mode. This formula takes two arguments: the total number of items (in this case, 5) and the number of items in each combination (which starts at 1).
4. Copy the formula in cell B2 to cells C2 to F2 to calculate the number of combinations for the other modes.
5. Now you need to generate all the combinations. In cell B3, you can enter the formula "=IF(ROW()-2<=$B$2,DEC2BIN(ROW()-2,$B$1),"")". This formula generates a binary number for each row in the table, starting from 0 and going up to the total number of combinations for the first mode.
6. Copy the formula in cell B3 to cells C3 to F32 to generate the binary numbers for the other modes.
7. Finally, you can use the binary numbers to generate the actual combinations. In cell G3, you can enter the formula "=IF(LEN(B3)=1,$A$1,"")&IF(LEN(C3)=1,$A$2,"")&IF (LEN(D3)=1,$A$3,"")&IF(LEN(E3)=1,$A$4,"")&IF(LEN (F3)=1,$A$5,"")". This formula concatenates the modes based on the binary numbers. If a binary number has a 1 in a particular position, the corresponding mode is included in the combination.
8. Copy the formula in cell G3 to cells G4 to G32 to generate all the combinations.

To answer your second question, you can use an "IF" statement to display a value only if it is less than a value in another cell. For example, if you want to display a value in cell A1 only if it is less than the value in cell B1, you can enter the formula "=IF(A1<B1,A1,"")" in cell C1. This formula checks if A1 is less than B1, and if it is, it displays the value in A1. Otherwise, it displays nothing.