References that are absolute within a group but that change from one group to another

I understand that the way to have, for instance, A1 as an absolute reference is to represent it as $A$1, and if I wanted to only have its column absolute I would represent it as $A1, while if I wanted only its row absolute I would represent it as A$1. I was wondering how I would represent it if I wanted to have it as an absolute reference within a certain group of cells but have the reference change from one group to another assuming the group isn't a row or column. One way would be to manually change it, and this can be done if there's only a few groups to work with, but for many groups manually changing it becomes extremely impractical and greatly increases the chances of a careless mistake along the way.

How can I create references that are absolute within a certain group of cells but not between groups? I have attached a sheet with an example of what I'm talking about.

Have you posted the correct w/book as it only contains columns of numbers and no explanation/ expected results.

Will you always have groups made up of 8 rows of data, with 5 blank rows between groups?

Pete

With the data layout you posted, i.e. 8 rows of data followed by 5 blank rows, you can use the following formulae in the cells stated (though I put them in E1 and F1 for comparison):

B1: =IF(A1="","",SIN(A1)*SUM(INDEX(A:A,INT((ROWS($1:1)-1)/13)*13+1):INDEX(A:A,INT((ROWS($1:1)-1)/13)*13+8)))

C1: =IF(A1="","",COS(A1)*SUM(INDEX(A:A,INT((ROWS($1:1)-1)/13)*13+1):INDEX(A:A,INT((ROWS($1:1)-1)/13)*13+8)))

The only difference is the SIN and COS.

Copy down as required.

Hope this helps.

Pete

I tried the formula and found that it only worked for the first value in each block.

Originally Posted by ancog

... it only worked for the first value in each block ...

You must have typed the formulae incorrectly (probably missed out the $ symbol from the ROWS terms). I've added the formulae to cells E1 and F1 in the attached workbook, and copied down to the bottom of your data - they give exactly the same results as you show in columns B and C.

Hope this helps.

Pete

Thank You Pete!