Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sorry for confusing you. Let me state the problem again. I have got two worksheets, let say datasheet and calculation sheet. In datasheet, i have 750 rows and 50 columns. In calculation sheet , i have got 50 rows with one column. Now i need to select 500 rows from the datasheet, i.e a matrix of 500 x 50 dimension. Note, this matrix should be from row 250 to 750 in first case. I need to multiply this matrix with the matrix of calculation sheet of dimension 50x1 and paste the result in the calculation sheet in a column. Again, this calculation is to be repeated, but with a difference. This time , we need to select rows from 249 to 749 in datasheet to get a new matrix with 500 x 50 dimension. This new matrix is to be multiplied with the matrix of calculation sheet, i.e 50 x 1 dim. These steps need to be repeated 100 times, in each step , we need to select 500 rows from the datasheet, with one row from the bottom deleted and one row at the top to be added for the new calculation. I guess, we need to make a macro for that, instead of using a excel function, since we need to run it for 100 times.Kindly help.

share|improve this question

1 Answer 1

I had to read that 5 times to understand what you are trying to do... and I'm still not sure I get it.

If I understand what you're saying, you'll need the SUMPRODUCT function. I'm going to assume the following:

  1. Your data runs from A1 to C750
  2. Column A contains your dates
  3. Column B and C contain the values you need to multiply
  4. Your calculation needs to go in Column D
  5. Your first calculation will go in cell D750

Given these assumptions, the formula in D750 would be:

 =SUMPRODUCT(($B251:$B750)*($C251:$C750))

(251 to 750 = 500 rows)

Now, just copy this cell and paste in each cell in Column D where you need to run your calculation. The target range will adjust accordingly. You can check this by selecting the cell doing the calculation and pressing F2 (the keyboard shortcut equivalent of a double-click on a cell).

share|improve this answer
    
+1 for the 'read 5 times' bit alone! –  Lunatik Aug 5 '10 at 12:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.