Hello,
I am having difficulty with a certain function. It is as follows: =SUM(OFFSET(INDIRECT("I"&U7),,,S8,1)). The idea is to sum all of the values in a range from the starting row (distinguished by cell U7) to the end row (distinguished by S8), across a single column (I). For some reason, this formula works perfectly when I apply it to about 30 cells or less, but gives me a reference error when I apply it to values greater than that. I have attached my worksheet, demonstrating this problem. Please see the following REF Errors in cells S2, T2 (They are mirrored in W2 and X2 but a solution to the former will apply just the same).
For clarity, I have a string which details the amount of cells to observe, based on the total time (cells 012:014). Essentially, I record data at 240 fps, and therefore just 3-4 minutes of data donates tens of thousands of data points, which is necessary for my work. In order to easily manipulate the range of values analyzed by the S2 and T2 cells, please change the time values; they are in a custom format of mm:ss.00. Sheet 1 is my formulas working with 2 data points per interval (Although it works at 2400 data points per interval as well for some reason - does this have to do with equal intervals or something?) and sheet 2 is a closer realistic amount of data per interval (Although, again, I'm normally in the tens/hundreds of thousands) demonstrating no differences outside of my formulas (Just the INTERVALS) and depicting a REF error in ST and T2.
Edit: The original file I posted lacked a working example of the formula in question. I have attached a new file (Example of formula working) demonstrating how the file can sometimes work, sometimes faulted. Please refer to this file. The ranges over which the formula should act are past the mark of the included data (excised due to submission limitations) but the theory remains the same. If you perform error tracing, Excel suggests my initial ranges in the formula are what is causing the error, independent of the data being retrieved (As evidenced by the value 0 in S2 vs #REF in T2).
Please, if anyone could offer their assistance in this manner it would be genuinely appreciated!
Thank you for your time.
Bookmarks