I have many formulas that reference a large list and at the time I just have them to 1000 to make it easy. There will come a day that I will surpass 1000 and I don't want to have to continuously update the function but at the same time I don't want to have an excessive range. The following function is in cell CM4 and I have data in cells CM11:CM52 and each day one more gets added at the bottom. What kind of function could I use to just take the average of CM11:CM52 that will reference CM11:CM53 tomorrow and so on and so on? The following formula is a simple one that is in my worksheet: =AVERAGE(CM11:CM1000)
Many formulas do not have a cost to using full column references. AVERAGE() is one of them so there is no problem using
=AVERAGE(A:A)
Or if you want to start on a certain row:
=AVERAGE(CM11:CM1048576)
For those formulas that do matter ie Array Types then we can make it dynamic with INDEX/MATCH
=AVERAGE(CM11:INDEX(CM:CM,MATCH(1E+99,CM:CM))
If the column in question is text then use "zzz" instead of 1E+99