Hi all,
I have data that I need to do calculations on. I need a cumulative total (fine), but for certain lines in my data I need to use that cumulative total, do a calculation, then include the result of that calculation in that cumulative total so that the next row that needs to do that calculation it is starting with the right value.
The calculation is a gain/loss on a pool of assets.
Here's an example:
| A | B | C | D | E | F | G | H | I | J |
| Row # | Date | USD | Cumulative USD | FX rate | CAD (C*D) | ACB impact (if USD>$0, -F, otherwise, a calc is required) | Cumulative ACB after transaction (sum of G) | ACB rate (H/D) | Gain/loss |
1 | Jan 1 | $100 | $100 | 1.3 | $130 | $130 | $130 | 1.3 | N/A |
| 2 | Jan 2 | $100 | $200 | 1 | $100 | $100 | $230 | 1.15 | N/A |
| 3 | Jan 3 | -$50 | $150 | 1.2 | -$60 | -$57.5 ($60 USD * previous ACB rate) | 172.5 | 1.15 | $2.5 (60-57.5) |
| 4 | Jan 4 | $100 | $250 | 1.5 | $150 | $150 | $322.5 | 1.29 | N/A |
| 5 | Jan 5 | -$50 | $200 | 1 | -$50 | -$64.5($50 * 1.29) | $258 | 1.29 | -$14.5 (50-64.5) |
My issue is that to calculate column G, I need the previous total of H. I can get this for the first gain/loss calc (row 3) using a SUM and EARLIER. But then for row 5, I need to G to include an earlier result of column G - maybe it needs to iterate through, or perhaps have something in a virtual table?
Sorry about the formatting.
At a bit of a loss here, any thoughts would be appreciated
Thanks,
Ian Farrell
