formula with a pattern - how to?

[mbeekvelt]

Hi, I am putting a dashboard together and I get stuck with what I thought was a matter of drag down formula once I put some kind of pattern in the rows. Could somebody please explain to me, as I cannot find an answer, on how to copy the following formula pattern down X number of rows?

=SUM(data!O6:O12)/(SUM(data!C6:C12)+SUM(data!E6:E12))
=SUM(data!O13:O19)/(SUM(data!C13:C19)+SUM(data!E13:E19))
=SUM(data!O20:O26)/(SUM(data!C20:C26)+SUM(data!E20:E26))

If I simply drag down these three formulas, they will just add 1 to the number from the previous number in the formula (hope that makes sense). And that is not what I want/ need.

Thank you in advance for your help - I am grateful.

[Yogi52o]

Use the INDIRECT function to allow for formula determinations of your sum ranges. OFFSET could also be used. Once you've got the concept of a formula to set the sum range, I used the ROW() function to act as the counter. In my example, this starts in row 1, so ROW() returns 1 (6 + 7*(1-1)) = 6, and when dragged into second row, would return (6+7*(2-1)) = 13.

Please Login or Register  to view this content.

[mbeekvelt]

Thank you very much @Yogi52o. It seems to work. I will have more of this formulas to put together so it will be a great test and good way to learn something.

Use the INDIRECT function to allow for formula determinations of your sum ranges. OFFSET could also be used. Once you've got the concept of a formula to set the sum range, I used the ROW() function to act as the counter. In my example, this starts in row 1, so ROW() returns 1 (6 + 7*(1-1)) = 6, and when dragged into second row, would return (6+7*(2-1)) = 13.

Please Login or Register  to view this content.