Forum Discussion
Jump over a row when dragging down a formula
Hi,
I am trying to put in a formula in excel. The problem is that every 7th row I have a row that I don't want to compute but instead jump to the next row but I still want to have the result beneath the last result so my sheet does not have any blank cells. How can I do that?
Example of data: Formula
A1: 1993 B1: 10 C1: 100 Don't want to compute
A2: 1994 B2: 15 C2: 105 B2/B3
A3: 1995 B3: 12 C3: 100 B3/B4
A4: 1993 B4: 11 C4: 105 Don't want to compute
A5: 1994 B5: 10 C4: 100 B5/C5
Very happy for a response :)
6 Replies
You could simply drag down the formula, and clear the 'unwanted' cells by ctrl-clicking these and press 'delete' once selected. A sum of the range of cells still computes, even if the range contains blank cells.
Is that what you mean?
Regards,
- Thank you. That will work fine :)
Carl Elmesiöö, if you're data and formulas consistently repeat, you might also try dragging a selection.
Selecting cells across multiple rows and then dragging down will copy each row in turn.
E.g. Select A1:A7, grab the drag handle then drag down to A14 (or as far as you need).
A1 is copied to A8
A2 to A9
...
A7 to A14 etc
If A7 was blank or contained text, A14 and every 7th cell will contain the same contents as A7.
Could be
=IF(MOD(ROW()-1,7), <formula to compute>, <blank or another formula>)
Hi Carl,
Could you please clarify a bit
- in your sample you don't compute 4th row, it shall be 7th one instead?
- what is your formula if compute? For row 2 that's B2/B3, for row 5 - B5/C5
- what shall be in row 1?
- if not compute you take result from previous row?
