0

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.

1
  • 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

1 Answer 1

0

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

See the sample spreadsheet.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.