Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Computer Program

  1. Jun 16, 2004 #1
    I'm wondering if there is a program that can do this, and if so which one and how.

    What I need is a program in which I can make columns with data in it. One column should have its rows be the sum of the corresponding rows in the other columns. Then I need to calculate the correlation of each column to the column that contains the sums. Excel can do this fine, but the next part is not so easy. I need to have it generate all the possible combinations of 53 numbers taken 6 at a time. When one combination is generated, it should add one to each corresponding row in the last column (that is to say, row 34 in the last column would be say 5, then when a 34 was generated along with 5 other numbers, it would become 6 and likewise the other rows would have their number increase by 1). Once this is simulated, the correlation for the column in question should be checked against a range that is set. So if I set it at between 0.35 and 0.36 and after one combination is run through it is 0.35555 the program would keep the combination generated and then return the cells to the original value and continue running through the remaining combinations. If you need me to clarify something just ask and I will try.

    edit: I put this here because it deals with a probability forecasting model I'm working on and my computer programming skills are not that strong.
  2. jcsd
  3. Jun 17, 2004 #2


    User Avatar
    Staff Emeritus
    Science Advisor
    Gold Member

    I would just write a C (or whatever is your favorite compiler) program using a 2D matrix.

    I've come up with forecasting algorithms that I've been able to execute on Excel...so it looks like you have some sort of weekly data (53 is real close to 52) ? So are there 53 columns or rows ? What do you mean by a "combination" ? How do you get a number from the 6 numbers in the combination - do you add them up ?

    Give me some background, if you will. This is extremely fuzzy at this point, and it's not at all clear what you are trying to do. What is the basis for your forecasting approach ?
  4. Jun 17, 2004 #3


    User Avatar
    Science Advisor
    Homework Helper

    That's X = 22,957,480 combinations!
    Okay, hold on. So there are 53 rows, and let's say all the cells in the last column are zero. If the the first combination generated is {1,3,5,7,23,24} then those cells should all increase to 1? Now, do you need this to happen in order? I'm pretty sure that each of the cells, by the end, would have increased by 2,598,960, or (6/53)X.
    What does this mean?
  5. Jun 17, 2004 #4

    Yes, I know how many combinations that is :smile:

    There are 53 rows, and if we assume all the cells in the last column are zero, then those cells that are in the row number that matches the number generated (i.e. row 1 for 1, row 7 for 7 etc) go up by one, correct. However, if the resulting change in the value for the column puts the correlation cell (say it is 2 rows under these columns) within a desired range, it keeps the particular combination {1,3,5,7,23,24} stored in a text file maybe. Then it resets the sheet back the way it was before that combination ran and does the same thing for the next combination.

    As for that that means, under each column (except for the one with the active sums in it) there is a cell that has the correlation of its column of 53 numbers to the one with the active sum. If I changed the data values in the last column any, then the resulting correlations are going to change. Say before we run this the correlation is 0. Now I say I want a correlation to be somewhere between 0.35 and 0.36, so it runs the combinations and if your example of {1,3,5,7,23,24} makes the column have a correlation of 0.3555 then since it falls in the desired range, it will keep that combination of numbers.

    As I said, I wish I had the computer skills needed to program this, but I don't. I've been on the hunt for programs or addins to excel that will do this, but there isn't much luck.
  6. Jun 17, 2004 #5


    User Avatar
    Science Advisor
    Homework Helper

    I think I can figure out a program for you if I can fully grasp what you want it to do.

    Perhaps in an organized manner you can list out the components of this program and what they do. I understand we have a table of numbers (and I assume these numbers don't change). Then an array, array1, which stores the sum of each column in the table, i.e. the i'th entry in the array stores the sum of the numbers in the i'th column of the table. Now, we also have another array, array2, where the i'th element in the array is a function of the i'th element in array1, and the i'th column in the table.

    We also have something which generates all 23 million combinations, and as each combination is generated, array1 (and thus array2) are updated, and if an element in array2 has a certain value, then the generated combination is stored. Oh wait, no. First, it generates the combination. Then, let's say it checks to see that if upon adding 1 to those 6 spots in array1, then updated array2, it would result in one of those elements of array2 having a value in a special range, then rather than actually updating array1 and array2, it stores the combination, then moves on to the next combination. So it adds 1 to those 6 spots in array1 only if adding 1 would not result in the number in the correlation cell turning into a "desired number." Is this correct?
  7. Jun 17, 2004 #6
    Ok, I will list them out.

    We have a table, much like in a spreadsheet form. Each row represents a number from 1 to n. In this case n = 53.

    Next I enter numbers into the i columns filling up all 53 rows in each column.

    Then I create a column, i+1, such that each cell in it is an active function. That is, the cell in column i+1 in row 4 will be the sum of the values in all the other row 4 cells in the i columns. This value should be able to change, such that if I changed a value in some cell it would change accordingly.

    Next, for sake of this description, say I go down to row 55. Under each column I have the particular cell contain the correlation between the particular column and the i+1th column. So for example, the correlation between column 1 and i+1. Then the correlation between column 2 and i+1. Then so on until column i and i+1. Again, like the cells in the i+1th column, these cells in row 55 should also be active...that is if a cell value is changed it will change the value as well (like what excel does).

    Now, we have a thing that generates all the possible combinations of 53 taken 6 at a time. So we get 23 million sets of unique strings {x,y,z,r,q,s}.

    Now, before this begins, we have to specify a range for ONE column's desired correlation value. Say it is the ith column. Currently row 55 of this column has a correlation value of 0.34. I want it to be between 0.35 and 0.36. So I input that.

    Now the program generates a string of 6 numbers. Say {1,2,3,4,5,6}. Well then in the ith column (because that column is the one with the targeted correlation) will have its data points changed in the following manner: Row 1 of column i will have the value in its cell go up by one. Row 2 of column i will go up by 1, and so on. So if I got a 45 in there somewhere, then the value in row 45 goes up by one. Now when these cell values are changed, then the i+1th column also changes, and this changes all the correlation values in row 55. If the resulting value in the cell belonging to column i, row 55 falls within the desired range, the program saves the string {1,2,3,4,5,6} somewhere, and then resets all the cells to the original state and repeats for the next combination and so on until it has gone through all ~23 million of them.

    I hope that makes more clear sense. If it doesn't, I can try and be more specific about a certain area that doesn't. Thank you for your help too.
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook