Forum Discussion
Applying formula of consecutive data to non-consecutive columns?
Hi! I'm sure this is fairly easy but I just can't figure out how to do this. I have attached a sample of the worksheet I'm working on.
So I have a data set sorted by day, and I have to get the mean and standard deviation per day. So I've set the formula for Day 0 to reference column C for the mean and standard deviation. However, when I copy the formula over to the Day 1 column, it references column E instead of referencing column D. The same thing happens when I drag the formula over the rest of the days, all the data skips a column, only using column E, G, I etc.
Is there any way I can work around this? Is there a better formula to use?
Thank you so much in advance for your help!
That's because you have relative cell references like "C2:C8". When you drag or copy a formula it automatically changes the column and cell part of the reference in the direction that you copy it. E.g. copy it one cell to the right will make it "D2:D8". One more to the right and it will become "E2:D8". Similarly, copying it down one cell will make it "C2:C9" and so on.
To prevent this you need to insert the $ sign to make part of the reference absolute. "$C2:$C8" will freeze the columns when you copy sideways but not the rows when copying up or down.
In your particular lay-out, though, that doesn't help, since you want ot copy one formula two cells to the right but shift the range only one column. My recommendation would be to change your table in Sheet2 as shown in the attached workbook (rows 8-10).
2 Replies
Is your problem solved. I am facing the same for mine.
That's because you have relative cell references like "C2:C8". When you drag or copy a formula it automatically changes the column and cell part of the reference in the direction that you copy it. E.g. copy it one cell to the right will make it "D2:D8". One more to the right and it will become "E2:D8". Similarly, copying it down one cell will make it "C2:C9" and so on.
To prevent this you need to insert the $ sign to make part of the reference absolute. "$C2:$C8" will freeze the columns when you copy sideways but not the rows when copying up or down.
In your particular lay-out, though, that doesn't help, since you want ot copy one formula two cells to the right but shift the range only one column. My recommendation would be to change your table in Sheet2 as shown in the attached workbook (rows 8-10).
