I was wondering if there is a worksheet function to apply the same set of mathematical transformations involving three separate cells, but every 5th row down. An example of this in Excel would be, in a new column, say column I:
H5 -(C5 * H8)followed byH10 -(C10 * H13)in the next cell down, thenH15 -(C15 * H18)and so on
Those (H and C) are the only two columns I am wanting to apply this exact formula to, but for hundreds of iterations down the spreadsheet. Those are just the first three samples. Imagine that formula extending all the way up through H500 - (C500 * H503), for instance. I would like to just apply this formula to a few iterations and then drag-to-fill the rest of the column, but this does not work.
2 Answers
Enter =H5 -(C5 * H8) in cell I5.
Leave cells I1:I4 blank.
Select cells I1:I5 and drag/fill down as far as you need.
Result:
In cell I1:
=INDEX(H:H,ROW()*5)-(INDEX(C:C,ROW()*5) * INDEX(H:H,(ROW()*5)+3))Then copy down. If you start from a cell below I1 (eg I2 or I5) you will need to adjust the formulas to account for the offset eg (ROW()-1)*5
