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

Michael Yohe

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?

Paul Newcome

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))

Paul Newcome

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))

Michael Yohe

Thanks, Paul. That worked!

Michael Yohe

Actually, if I try to use this more than once in a sheet I get a #CIRCULAR REFERENCE error.

So this works fine:

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

But in that same sheet if I change the name to "Ringo Starr" it throws a #CIRCULAR REFERENCE error, which is puzzling. After some troubleshooting I narrowed it down to the initial range of IIQ:IIQ. If I change this to be a different column it works. Unfortunately, the idea was to use this formula to average up many different people. Any ideas?

Paul Newcome

Are you able to provide a screenshot?

Michael Yohe

Sure thing, Paul. I hope this helps:

Paul Newcome

My suggestion would be to move the summary to another sheet. I would suggest leveraging hierarchy, but since new rows are being added via a workflow, you would still have to go in and manually indent each of the new rows.