Support Board
Date/Time: Sat, 23 Nov 2024 22:32:08 +0000
[Programming Help] - Receiving "#REF!" Error in spreadsheet
View Count: 192
| [2024-07-13 00:13:49] |
| User827002 - Posts: 36 |
|
I'm struggling to understand why I'm receiving this "#REF!" error. The formula in AT is =if(sum(AS3:INDIRECT(CONCATENATE("AS",(JJ3+3))))>0,1+AT4,0) . The JJ column currently shows 166, so AT should find the sum of AS3:AS169. For whatever reason I seem to get this error about half of the time, but I get it less when JJ is a smaller number. For example if I change the "JJ3+3" part of the formula to "JJ3-100" I don't receive the error but then AT is not showing me the correct number. I'm hoping you can help me troubleshoot this.
|
| [2024-07-14 14:38:28] |
| User43 - Posts: 101 |
|
did you check your AS column, if any cell in the range has a #REF error, the sum function will inherit it. That would also fit with your observation that the error disappears when you reduce the AS3:ASxx range. |
| [2024-07-14 19:25:51] |
| User827002 - Posts: 36 |
|
No, there isn't a #REF error anywhere in the AS range
|
| [2024-07-17 21:16:10] |
| User43 - Posts: 101 |
|
Then it looks like the statement created using the indirect function leads to a REF error. Personally I don't like to use the indirect function. Couldn't you achieve the same by using a offset? if your JJ3 field for some reason puts out some kind of error, or no number at all, you would end up with a REF error. Maybe consider putting a fail save into the indirect call by checking before using it if JJ3 is a number. Date Time Of Last Edit: 2024-07-17 21:41:24
|
To post a message in this thread, you need to log in with your Sierra Chart account: