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 127 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 28100
Row 101 is created and it counts 28101
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 127 instead?
Best Answer

You would want to insert an autonumber column with no special formatting. Next insert a text/number column (called "Row Number") with the following column formula:
=MATCH([AutoNumber]@row, [AutoNumber]:[AutoNumber], 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

You would want to insert an autonumber column with no special formatting. Next insert a text/number column (called "Row Number") with the following column formula:
=MATCH([AutoNumber]@row, [AutoNumber]:[AutoNumber], 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))

Thanks, Paul. That worked!

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?

Are you able to provide a screenshot?

Sure thing, Paul. I hope this helps:

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.
Help Article Resources
Categories
Check out the Formula Handbook template!