A | B | C | |
1 | Time since start | Time difference | Result |
2 | 0 | ||
3 | 0.01 | 0.01 | 10 |
4 | 0.02 | 0.01 | 10 |
5 | 0.03 | 0.01 | #N/A |
6 | 0.04 | 0.01 | #N/A |
<tbody>
</tbody>
OK, this one has me stumped.
Column A is just values.
Formula in B3 is A3-A2, copied down.
Formula in C3 is VLOOKUP(B3,LookupTable,35,FALSE), copied down. So C4 is VLOOKUP(B4,OutputGrid,35,FALSE), etc.
So how to explain the errors? It works fine for the first 2 iterations, then fails for... no reason? I tried using "Evaluate Formula" function, but in all cases everything looks identical, except in C5 (and beyond) the last step gives a #N/A instead of 10.
Some notes:
- LookupTable is on another sheet. It's pretty large
- LookupTable is a named range, but the failure persists even if I use regular range references.
- If I use VLOOKUP(B3,LookupTable,35,TRUE), this seems to work for some reason. Well, it should work, but FALSE should work as well, and it does for the first 2 instances.
- The value 0.01 definitely exists in LookupTable.
- The (leftmost) values in LookupTable are in ascending order.
- In this file, I previously had an error. Something along the lines of "/xl/calcChain.xml" was removed or something like that. I thought it was repaired, but later I noticed that if I added or removed a column from the sheet that LookupTable was on, Excel would be stuck forever in a calcuation loop. I fixed this, by copying all formulas on the sheet to another sheet. Maybe the issue is deeper than I suspected?
Ideas?