how to return the minimum value on every nth cell

Question

I'm looking for a simple formula to compare and return with the minimum value of every nth cell.

My first value is in cell C26 and there are expected to be an undefined amount of values on the every 47th cell of that column after that.

I've been searching around and generalized a formula as follows:

=min(mod(row(C26),47)=0)

or

=min(offset($C$26,(row()-1)*47,0))

But obviously, one of them are giving me the result i wanted (the value in the cells are dates)

I'm hoping that someone would be able to assist?

Thank you!

Answer 1

For some reason, when returning an array of numeric values from the OFFSET function, the N function makes it work.

If there are no blanks in the range to be tested, you can use something like:

=MIN(N(OFFSET($C$26,(ROW(INDIRECT("1:10"))-1)*47,0)))

entered as an array formula by holding down ctrl+shift while hitting enter

If there might be blanks in the range, then you can try something like the following, also entered as an array formula:

=SMALL(N(OFFSET($C$26,(ROW(INDIRECT("1:100"))-1)*47,0)),1+SUMPRODUCT(N(N(OFFSET($C$26,(ROW(INDIRECT("1:100"))-1)*47,0))=0)))

In this case, the first N immediately after the SUMPRODUCT coerces the Boolean result to 1 or 0.