Results 1 to 6 of 6

Thread: How to calculate together a set of Data in Excel

  1. #1
    Join Date
    Feb 2012
    Posts
    11

    How to calculate together a set of Data in Excel

    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.

  2. #2
    Join Date
    Aug 2011
    Posts
    564

    Re: How to calculate together a set of Data in Excel

    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.

  3. #3
    Join Date
    Feb 2012
    Posts
    11

    Re: How to calculate together a set of Data in Excel

    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 separate cell for each combination add them together to give the value for each combination.

  4. #4
    Join Date
    Aug 2011
    Posts
    580

    Re: How to calculate together a set of Data in Excel

    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.

  5. #5
    Join Date
    Aug 2011
    Posts
    540

    Re: How to calculate together a set of Data in Excel

    I am 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 am 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 am sorry it's hard as I don't quite understand what I want I'm not sure how to ask.

  6. #6
    Join Date
    Aug 2011
    Posts
    695

    Re: How to calculate together a set of Data in Excel

    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.

Similar Threads

  1. How to calculate integrals with Excel
    By Ojsuta in forum MS Office Support
    Replies: 2
    Last Post: 24-02-2012, 07:14 PM
  2. How to calculate ages in Microsoft Excel
    By vALaNCiA in forum MS Office Support
    Replies: 2
    Last Post: 17-02-2012, 06:17 PM
  3. Calculate IRR in Excel
    By superdave1984 in forum Windows Software
    Replies: 3
    Last Post: 01-12-2009, 12:46 PM
  4. How to calculate GPA in Excel worksheet
    By Lambard in forum Windows Software
    Replies: 3
    Last Post: 06-06-2009, 09:29 PM
  5. How to calculate two dates in Excel
    By Ektaa in forum Windows Software
    Replies: 5
    Last Post: 03-03-2009, 05:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,707,922.86020 seconds with 17 queries