Polynomial fit data across multiple sheets?

I can't possible go through the 5M pixels per image * X images and pull all of the individual cells element by element onto a single sheet.

My experience is that LINEST() really just does not play nice with discontinuous ranges, and it does not support 3D referencing, so one way or another, you've got to extract the data from the all of the separate sheets and combine them into one range/array. When you say that you cannot possibly do this, are referring only to the time and tedium that comes with doing such a task manually, or is there some other restriction that prevents you from extracting data from all of the sheets and combining them together in a single spot?

If it is just the difficulty of doing it manually, I'm sure we can come up with a programmatic way of extracting and compiling the data. For example, I could use the INDIRECT() function to process your 5 sheet example. =INDIRECT("'sheet "&ROW(A2)&"'!$A$2") copied down would extract the 5 values in A2 of those 5 sheets. At which point you could easily use that helper range in your LINEST() function. If you pay attention to relative and absolute references and all the other stuff that goes into spreadsheet programming, this kind of thing can work fairly well for the simple example you give in the OP. The main downside is that INDIRECT() is a volatile function, so, on a large spreadsheet (5 MP sounds like it could be pretty large), it might slow Excel down significantly. If that becomes a concern, there are ways to mitigate the computation delays.

And that is likely not the only possible way. It could probably be done with lookup functions or even a utility like power pivot (get and transform) could extract the needed data. Or you could do this in VBA (or any other programming language that can extract data from a spreadsheet) where VBA extracts each value from each sheet and combines them into an array, then feed that array into the LINEST() function.

But if there is some broader restriction that prohibits you from extracting data from the sheets, then that is a bigger problem.

My recommendation is to use Get & Transform to combine/transform data from multiple sheets.

Return it as table in a sheet and use that as basis of your calculation.

Alternately, you can use xlwings to integrate Python and use numpy's polyfit.
xlwings: extension that allows use of python powered analysis in Excel
https://www.xlwings.org/

How to run/set up python powered udf in Excel (Windows only).
https://docs.xlwings.org/en/stable/udfs.html#udfs

polyfit
https://docs.scipy.org/doc/numpy/ref...nomial.polyfit

EDIT: Remembered the excellent article by Doug Jenkins at Newton Excel Bach.
https://newtonexcelbach.com/2018/05/...l-and-xlwings/

Please note: there are 1,048,576 rows in Excel 2016.

Since 2007, Excel has been limited to 1E6 rows and 16k columns, so your 2500x2000 and 12500x2000 data sets should easily fit within one Excel sheet. If your limitation is just about manual processing, then we should be able to figure out how to programmatically combine, extract, filter, otherwise manipulate the data so that you don't need to manually manipulate the data.

I'm sure there are enough Python programmers out there who dislike spreadsheets that you could easily find someone who believes you should just move all of your data analysis into Python and abandon spreadsheets altogether. I expect that doing this task in Python will involve all the same steps (extract data for each regression, perform regression, apply regression). At this point, you have phase 3 completed in the spreadsheet, you know how to do phase 2 in the spreadsheet, and you are up against learning how to do phase 1. That feels to me like a much smaller learning curve than starting over at square 1 with a new programming language. The only reason I could see in converting everything to Python (or other language) is if you felt the investment in learning a new language was going to be valuable in the long run.

I gave one simple example of how one might do the data extraction/manipulation step. How well did it work? If you help us understand exactly what needs to happen at the data extraction/manipulation step, we should be able to help you get an Excel based solution to that step, which, if I understand, should allow you to finish the task with the rest of the spreadsheet that you already have.

... not spend the time integrating it into Excel?

Depends on your need. If integrated and packaged with your solution, you can actually share it with others.

Also, Excel can serve as GUI/Userform for python code. While python does have GUI frameworks, I find it easier just to use Excel in many cases.

Added benefit, is that you have access to Excel's built-in tools, which you don't have to spend time making/tweaking in python.