# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Getting Excel to Calculate All Combinations of a Set of Data?

## marello

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!

----------


## Peo Sjoblom

Go to google.com and look up excel and permutations

--

Regards,

Peo Sjoblom

"marello" <marello.1xzfvc_1131124807.5646@excelforum-nospam.com> wrote in
message news:marello.1xzfvc_1131124807.5646@excelforum-nospam.com...
>
> 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
>

----------


## marello

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
 :Smilie:

----------


## Bruno Campanini

"marello" <marello.1y0vhm_1131191703.0209@excelforum-nospam.com> wrote in
message news:marello.1y0vhm_1131191703.0209@excelforum-nospam.com...
>
> 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

----------


## marello

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.

----------


## Bruno Campanini

"marello" <marello.1y0yym_1131196202.8007@excelforum-nospam.com> wrote in
message news:marello.1y0yym_1131196202.8007@excelforum-nospam.com...
>
> 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

----------


## DOR

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

----------


## marello

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

----------


## Bruno Campanini

"marello" <marello.1y2rry_1131280202.342@excelforum-nospam.com> wrote in
message news:marello.1y2rry_1131280202.342@excelforum-nospam.com...

> 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

----------


## DOR

>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?

----------


## marello

That does the job perfectly thank you so much!!!!! Unfortunately doing that for some reason deleted the rest of the pages in my workbook  :Frown:  Don't know what I did!!!! I'm off to figure out how to copy those data values in numerical order into a table now.

Thanks very much for your help  :Smilie:

----------


## alina

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




THATS A GREAT REPLY, THANKS A LOT!
DO YOU ALSO KNOW IF HOW I CAN MAKE EXCEL/VBA PRODUCE THE SET OF COMBINATIONS ITSELF?

say in your example with 5 digits, how can make excel produce all the sets of 1,2,3,4 and 5 digit combinations?

thanks a lot to anybody who can help!!

----------


## franksgeburt

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



THIS is almost what I need thanks so much!

I have 52 different values and 7 options so i would have done 52*51*50*49*48*47*46 rows in total by hand

(already done 1326 rows with 2 options of 52 values)

It takes the same value twice e.g.

1 1 1 has to be 1 2 3

and 

1 52 51 goes to 
1 52 52 and then comes the next problem

2 1 1 has to be
2 3 4 

And it doesn't matter if 3 2 1 comes 1 2 3 has already been done in the first row... so in what order the combination of values appear is irrelevant..

But you still have saved me a lot of work and probably I can work this out now....

----------


## akhil.nair

Hi All seen some great responses, however i need a little help with this, sorry if this seems silly. 

I have a set of Data as per below :

Column A : Origin Country
Column B : Destination Country
Column C : export rate (doesnt mean actual rate here but an index)


Now i have about 100 country names in column A and the same 100 in column B. 
different values for each lane export rate.

Example China to Russia export rate is 50  but China to russia import rate is 10

I need excel to calculate all the possibilities between all countries in column A and Column B and return the export rate for each depending on the combination. 

The end aim is to be able to select which  combination has the lowest export rate (index) and use that

Is this possible ?

----------


## arlu1201

Akhil,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do.  Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

----------

