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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would want to insert an auto-number column with no special formatting. Next insert a text/number column (called "Row Number") with the following column formula:

    =MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)


    Then you would incorporate this into your formula to only collect those rows where the [Row Number] is greater than or equal to 28 (using references to the entire column for each range).


    =AVG(COLLECT(IIQ:IIQ, [SE Manager]:[SE Manager], "John Lennon", [Row Number]:[Row Number], @cell >= 28))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!