# simplification of a fomula using offset

I want to use a less complicated formula to achieve the answer to cell D28 in this sample spreadsheet

I was wondering if there was a way to group the offset cells together in a set so I didn't have to repeat the redundant parts of the formula.

The formula is to separate the data from the columns above into six different sections based on the number of rows without blanks. The number of rows is dynamic so that is why I used the technique I did.

I considered using the date column to achieve this but didn't know how. the actual use case has many more rows than the example which is why dividing into six groups is useful.

• Absolutely. Please edit your question and explain the relationship between the formulas in `D28:D41` to each other, as well as the sets of offset cells. The formula can be simplified using named ranges, named formulas, LAMBDAs etc. but we'd need to understand the logic being building the formulas. Mar 21 at 21:09

Use `filter()` to choose which columns and rows to include. Your date criteria is unclear, but you can get the desired columns like this:

``````=let(
numRows, rounddown(count(A1:A23) / 6),
rowOffset, -numRows,
if(
C28,
100 / -(C28 / sumproduct(
if(
filter(offset(B24:BN24, rowOffset, 0, numRows), B25:BN25 = "c"),
filter(offset(B24:BN24, rowOffset, 0, numRows), B25:BN25 = "d")
)
) - 1),
iferror(1/0)
)
)
``````

To choose the right rows, wrap the `if(filter())` above in another `filter()` that gets the right rows based on dates. You can then do away with the offsets. See filter().