I am having some trouble to create a formula that calculates two indexes of a matrix based on another matrix.
Let me explain a bit more in detail:
I have the following Tables:
TableA
------
Index | Value
0 | ...
1 | ...
2 | ...
TableB
------
Index | Value
0 | 10
1 | 20
2 | 30
3 | 40
4 | 50
5 | 60
Now based on the length of TableA I want to make a calculation. So for example if my index of TableA = 0 then I want to sum up the first two indexes of TableB:
Index(TableB, IndexTableA +1) + Index(TableB, IndexTableA)
So in the case where the index = 0 I will have the following calculation:
Index(TableB, 0 + 1) + Index(TableB, 0) = 20 + 30 = 50
Which is perfect. But now comes the tricky part. Everytime I want to select a pair for the calculation (e.q. [1,0], [3,2], [5,4]).
Now to calculate the second pair the above formula will not work so I created the following formula:
Index(TableB, IndexTableA +2) + Index(TableB, IndexTable + 1)
So if the index of Table A = 1 then the calculation is:
Index(TableB, 1+2) + Index(TableB, 1+1) = Index(TableB, 3) + Index(TableB, 2) = 40 + 30 = 70
This works perfectly. But now if the index = 3 The above formula does not work:
Index(TableB, 2+2) + Index(TableB, 2+1) = Index(TableB, 4) + Index(TableB, 3) = 50 + 40 = 90
But that is not the pair that I want. I want the pair to be Index5 and Index4 to be used in the formula.
Can anyone help me guide trough a solution that will work in all cases?