Formula with range that goes from a certain row to whatever the current bottom row is

My formula:

=AVG(COLLECT(IIQ28:IIQ99, [SE Manager]28:[SE Manager]34, "John Lennon"))

First, I cannot use the entire column. There is data in rows 1-27 that can not be applied to my average.

I want the range to go all the way to the bottom of the sheet, because this sheet is regularly populated by a workflow from another sheet. So saying "IIQ99" doesn't work once row 100 is created.

Is there a way to have a formula select a range between a certain row (in this case 28) and dynamically change whatever the limit of the range is based on the current last row?

Row 100 is created and it would could 28-100

Row 101 is created and it counts 28-101

and so on?

If these is not a way to automatically alter the range, can I have my formula select the entire column but exclude rows 1-27 instead?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!