Develop a ranged function that stops at the row above

Mike Broglio

I am trying to develop a function that will collect the maximum value in the same column for all rows above the current row. My initial formula was

=MAX(COLLECT([Sim Exp Yr]$1:[Sim Exp Yr]1, $[Email Address]$1:$[Email Address]1, $[Email Address]@row, $Certification$1:$Certification1, CONTAINS("SimLab", @cell)))

Which runs from the $1 row to the row above the formula row. So in this case, row 2, the formula runs from 1:1. When I copy to row 3 the formula runs from 1:2, etc.

The problem is that when the sheet gets sorted the 1:2 formula can end up on row 100, so it is only looking at 1:2 instead of 1:99. Taking off the specified row numbers includes the formula row in the range and results in a circular reference.

I saw answers to looking up specific values in the row above, but nothing about incorporating the 2 helper columns (autonumber and match) into a range, i.e. going from $[Email Address]$1:$[Email Address]1 to $[Email Address]$1:$[Email Address]RowHelper@row does not work.

Any advice on this?

Paul Newcome

You would use the auto-number helper column and the "Row" helper column that uses the MATCH function to output the row number. Then to incorporate this into your other formula, you would include a range/criteria set looking at the [Row Number] helper column and the criteria being less than [Row Number]@row.

=MAX(COLLECT(………………………………, [Row Number]:[Row Number], @cell < [Row Number]@row))

Mike Broglio

Still getting a circular reference error. Putting the formula into row 3, with RowHelper set to 2 or 3.

=MAX(COLLECT([Sim Exp Yr]:[Sim Exp Yr], $[Email Address]:$[Email Address], $[Email Address]@row, $Certification:$Certification, CONTAINS("SimLab", @cell), RowHelper:RowHelper, @cell < RowHelper@row))

Paul Newcome

You have to put the formula into a column that is not being referenced by the formula.