Search code examples
excelcopyanchorfilllarge-files

Is there a way to copy an Excel formula with an anchor cell and have the anchor update every n rows?

I have a large data set I'm working with where I'm trying to find the smallest n related to a mean of a set of samples that falls below the lower bound of a given confidence interval.

Example: Does the mean of all samples where n = 45 fall below the lower bound of the confidence interval for samples with n = 50?

I'm doing this with the following formula in Excel:

=IF(U2<=$Z$2,D2,"NO") - where U2 = mean, Z2 = the lower bound of the confidence interval, D2 = n if true.

I have Z2 anchored since it is the target lower bound for the first set of 150 rows, but the next 150 rows has a new target (Z152).

Is there a way to copy this formula down the intended column where it will autofill the next anchor cell every 150 rows?

Solution

  • You can use INDEX to reference a cell in a column, and calculate the row number any way you choose.

    Like this

    =IF(U18<=INDEX(Z:Z, (INT((ROW() - 1) / 150) * 150 + 2)),D18,"NO")

    If you want, you can replace 150 with a cell reference containing the row increment value, and/or 2 with cell reference containing the first row number