Applying formula of consecutive data to non-consecutive columns?

laonglaan 

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).