| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 52218 | 2004-12-13 09:13:00 | Excel formula | bpt1 (419) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 302611 | 2004-12-13 09:13:00 | If 54000 data points, with an adjacent column of 0s and 1s. I want to reduce the count down to a total of 5400 data points, So in a second column I want the first cell to be the sum of the first 10 cells in column 2, and the second cell to be the sum of the next ten cells in column 2 (ie cells 11 - 20). I just can't work out how to copy the formula so that it goes from: (cell 1 =SUM(B1:B10) (cell 2 =SUM(B11:B20) If i copy the formula in cell one and paste it in cell 2 I only get =SUM(B2-B11) Advice appreciated |
bpt1 (419) | ||
| 302612 | 2004-12-13 10:44:00 | If you want it in Row 2 you'll have to type it. If you paste it in Row 11 it would be ok. The auto part of it counts the Rows, and/or columns, moved, not the Row no's ref'ed to in the formula. Don |
donread (6401) | ||
| 302613 | 2004-12-13 10:47:00 | Any formula that would copy to sum the next 10 cells in the previous column? | bpt1 (419) | ||
| 302614 | 2004-12-13 11:07:00 | Not sure what you're asking Don |
donread (6401) | ||
| 302615 | 2004-12-13 11:31:00 | I want to sum cells A1:A10, then A11:A20, A21:A30 etc up to A53991:A54000 by copying the formula into cells b1 to b 5400. | bpt1 (419) | ||
| 302616 | 2004-12-13 18:20:00 | Hi unfortunately Excel works well with some series but not others. You need a minimum of three cells before dragging down but in your case even that wont work :-( Presuming your putting your formula in a cell in row 1 then this should work. This looks at the current row your on (thats the Row() part) and then does some calculations to determine the row and hence the range. Indirect allows the address to be used in the sum formula. Just enter this cell in say C1 and drag down. =SUM(INDIRECT(ADDRESS((ROW()*10)-9,2)& ":" & ADDRESS(ROW()*10,2))) |
parry (27) | ||
| 302617 | 2004-12-13 22:06:00 | Nice formula parry, but it only works for data in Column 2 (B) [b]bpt1[\b] - for data in Column A, change both 2's in the formula to 1's. |
Russell D (18) | ||
| 302618 | 2004-12-13 23:59:00 | Hi Russell, yup you may also need the Column() formula to calculate the column depending upon what your doing. Just a thing to note about using Indirect is that it looks at an absolute address, regardless of whether you insert rows/columns or not. So normally a formula that had =A1 would change to =B1 if a row was inserted in row 1, where Indirect("A1") would still look at A1 and wont change the relative reference of the formula. So you should refrain from inserting/deleting rows above your formulas/data is or inserting/deleting columns to the left of the formulas/data. If you try www.mrexcel.com you may get other suggestions to resolve your problem. cheers Parry. |
parry (27) | ||
| 1 | |||||