Change specific number of columns when copying

[laptopbob]

I have this formula:
X6=SUM($BC$2:$BC$5000)/SUM($BA$2:$BA$5000)

When I copy it to Y6 I would like it to read:
=SUM($BF$2:$BF$5000)/SUM($BH$2:$BH$5000)

So the culumn number should change 5 from BA to BF and BC to BH.

I have tried with indirect but can't get that to work. Any ideas?

 

[Eric W]

Possibly:

=SUM(OFFSET($BC$2:$BC$5000,0,(COLUMNS($X6:X6)-1)*5))/SUM(OFFSET($BA$2:$BA$5000,0,(COLUMNS($X6:X6)-1)*5))

I think you reversed some of your references, but see if that works.

 

[laptopbob]

Possibly:

=SUM(OFFSET($BC$2:$BC$5000,0,(COLUMNS($X6:X6)-1)*5))/SUM(OFFSET($BA$2:$BA$5000,0,(COLUMNS($X6:X6)-1)*5))

I think you reversed some of your references, but see if that works.

You are correct. I made a mistake. It had to be

When I copy it to Y6 I would like it to read:
=SUM($BH$2:$BH$5000)/SUM($BF$2:$BF$5000)

I will just test the formula. I should be able to keep copying it to further cells, right? E.g. Y6, Z6 etc?

 

[Eric W]

Correct, the column references will go up by 5 columns for every one column you copy the formula over.

 

[laptopbob]

Correct, the column references will go up by 5 columns for every one column you copy the formula over.

It works perfectly. Thank you very much!

 

[Eric W]

Glad to help!