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

1. ## 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!  Register To Reply

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

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.  Register To Reply

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

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

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

Hi Glenn,

I define the number of data points down the column I by the time it took, in seconds, from frame point 1 (R6) to frame point 2 (R7) x 240 frames per second. In essence, it is the seconds per checkpoint x 240 frames. The number of frames details the number for cells I want to include, starting from each checkpoint (i.e. 1- 240 then 241-480 for example). Does this make sense? Thank you for your swift response!  Register To Reply

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

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.  Register To Reply

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

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.  Register To Reply

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

To be more specific, depending on the difference in time across two "Frame Points" (Which is just another way for me to say a Checkpoint), the time in seconds is calculated, then multiple by 240 to get the frame count (which is equal to the number of cells I want to go down the column "I"). I want to start going down at Cell I2 (based on the cell count calculated by the row O12:T12), adding the values in the specific number of cells calculated in this row. Then, I want to repeat this, starting from the cell directly after I stopped summing values from the previous "I2:Range". I calculate this amount using row O13:T13 (Referring to the difference from frame point 2 to 3, which is equal to Checkpoint 2). Because the length of each checkpoint is variable, and I can only multiply the time in seconds, I have this convoluted setup to extract the frame (cell) count, and I am attempting to apply it as a consecutive range to each portion of the "I" column, such that the summative values of each time range are calculated automatically, one after the other.  Register To Reply

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

Hi Glenn,

Sorry, I responded to this without seeing your latest message. I will try this and get right back to you. Thank you!  Register To Reply

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

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

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

Back in a few minutes with a better way.  Register To Reply

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

Hi Glenn,

Yes, I do see that. I am not sure why, perhaps it is because they are being rounded? Also, I tried to put in my data (50000 points) and calculate the values from S2 and T2 but I only see values of zero. Is this a formatting issue with he values being labelled as general vs number for example? Thank you  Register To Reply

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

Perhaps I can send you a few hundred/thousand data points to demonstrate what I am talking about. Unfortunately the size exceeds the limit for the forum post. If it would help, is there a way I might be able to send a larger file to you? Thank you  Register To Reply

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

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.  Register To Reply

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

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.  Register To Reply

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

Hi hringrv,

Thank you for your response. I will try this now and be back in a flash.  Register To Reply

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

Hi Glenn,

I will also try this and get back to you asap. Thank you!  Register To Reply

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

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.  Register To Reply

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

Try =ROUND(formula,0)

You're welcome.

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.  Register To Reply

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

Hi Glenn,

I will check out your suggestion and make sure to add reputation for you both as well as mark this as solved.
Following your comment, I have two more questions (my apologies). I believe this should be an easy Index function, but I am more familiar with offset. Might you have a suggestion for how I can simply formulate (Sum from cell I2 : I (value from T7) using Index? This function refers to the information in cell Q2. I would also like to do the same for cell Q1, but it would refer to the following (Sum from cell I2 : I (Value from S9).
Lastly, the equation you suggested, Glenn, for rounding down the seconds does not work for decimals above .5 (It rounds up). I tried employing the Rounddown function but it also seems to fail for some reason. May I ask for any suggestions?

The relevant cells in question have been highlighted in red. The yellow highlighted cells are irrelevant.

Thank you.  Register To Reply

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

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)  Register To Reply

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

My apologies Glenn,

As I mentioned, I find the functions of Index to be somewhat difficult. I attempted to map your function to that of I2 : (value from T7) but was unable to do so. Therefore, I thought I would ask.  Register To Reply

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

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.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 