REF ERROR: Sum, Offset and Indirect functions to map sum alternating ranges of values

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.

I think you are making life very complicated for yourself. However, this does not work:
=SUM(OFFSET(INDIRECT("I"&U7),,,S8,1))

because U7 and S8 are not integers. You have just formatted them to look like integers.

Can you explain HOW you define the start and end points to help come up with something more sensible.

If you want to SUM from I2 to I-something, XXX datapoints down, how do you calculate the value of XXX?

Maybe

=ROUND(240*R12,0) in R7 copied down. R2 and S2 return zero for the SUM as the range of values doesn not go down as far as the starting point.

Basically, your formula was saying start at cell U29.4 and sum down for 234.76 rows (or whatever... these numbers were just made up by me). To avoid "odd" results, it needs to have integers as inputs.

I understand now, I think. Have you noticed that the values in P12 to P14 are "out" by one second?

Back in a few minutes with a better way.

In the file with the name beginning with Example, cell S2 shows a #REF! error. The reason is that its formula uses INDIRECT("I"&T8), but T8 may display as an integer, but it evaluates to 31977.2. T31977.2 isn't a valid cell address. You need to use either ROUNDDOWN(T8,0) or ROUNDUP(T8,0).

That said, you don't need to use INDIRECT. You could use =SUM(INDEX(I:I,T8):INDEX(I:I,T8+R9-2)) because INDEX expects its 2nd and subsequent arguments to be integers, it automatically discards the fractional part. Another benefit it that INDEX isn't volatile. Even if you want the perceived benefits of OFFSET, there's no reason whatsoever to use INDIRECT for this. Try

=SUM(OFFSET(I1,T8-1,,R9-1,1))

because OFFSET also expects 2nd and subsequent arguments to be integers and also discards fractional parts.

HRLN may have answered this. But this is what i had in mind.

See sheet.

R6, to give the formula a starting point
1

R7, copied down:
=1+ROUND((Q7-Q6)*86400*240,0)

to provide an end point.

S7, copied down:
=SUM(INDEX($I$2:$I$100000,R6,):INDEX($I$2:$I$100000,R7,))

The total at S8 is incomplete and s9 and s10 are blank as the dataset is not long enough. Try it out on a bigger dataset and let me know how it goes.

Hi Everyone,

Both solutions work perfectly. Thank you very much for your expertise. My last question, a very simple one, would be in reference to what you pointed out earlier Glenn. In cells N12:N14 the seconds function is rounded up. Given that I am adding the decimal independently, would you happen to know how I might round down the value instead, such that my additions are correct? Your earlier formula takes that into account Glenn, but I would like to retain my current structure, and am also curious in general. I am aware of the round down function, but am ignorant as to how I use it with =Seconds... Excuse me. Thank you both, once again.

Try =ROUND(formula,0)

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

can I suggest that you GET familiar with INDEX. OFFSET is useful, but it is volatile. It recalculates EVERY tiem ANYTHING changes ANYWHERE on your worksheet. In a big file, that can cause severe performance issues.

Might you have a suggestion for how I can simply formulate (Sum from cell I2 : I (value from T7) using Index?
Yes. Use the formula I suggested in my last post. That is EXACTLY what it does.

I tried employing the Rounddown function but it also seems to fail for some reason. May I ask for any suggestions
=INT(O13*86400)

INDEX is simple.

=INDEX(Range,Row Number,Column Number) If you have a range I2:I100 and a row number of 1, it will return the first value in that range (I2), with 2, it will return I3, and so on. If the range is all in one column, you don't need to add the the column number.