Hi
I have a data set which is filtered. I have supplied the original data set, the cells in formula format, and the subsequent filtered data.
I calculate a formula in column F from data in each row. I then calculate a running balance in column G, which simply adds the formula in column F to the prior balance in the cell above.
E.g. Balance in G3 = G2 + F3
The problem is when I filter the data so that I need the running balance of the cell directly above.
e.g. G4 = G2 + F4
I have tried using subtotal but it does not seem to like this variation. My dataset is thousand of lines long so it is causing me a problem as it's too time consuming to go through each row in the filtered set.
Does anyone have any ideas?
<!--StartFragment--> <colgroup><col width="65" span="7" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>
FORMULAS:
<!--StartFragment--> <colgroup><col width="130" span="7" style="width:130pt"> </colgroup><tbody>
<!--EndFragment--></tbody>
<!--StartFragment--> <colgroup><col width="65" span="7" style="width:65pt"> </colgroup><tbody>
</tbody>FILTERED:
<!--StartFragment--> <colgroup><col width="65" span="7" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>
I have a data set which is filtered. I have supplied the original data set, the cells in formula format, and the subsequent filtered data.
I calculate a formula in column F from data in each row. I then calculate a running balance in column G, which simply adds the formula in column F to the prior balance in the cell above.
E.g. Balance in G3 = G2 + F3
The problem is when I filter the data so that I need the running balance of the cell directly above.
e.g. G4 = G2 + F4
I have tried using subtotal but it does not seem to like this variation. My dataset is thousand of lines long so it is causing me a problem as it's too time consuming to go through each row in the filtered set.
Does anyone have any ideas?
| A | B | C | D | E | F | G |
| 1 | Date | Probability | Return | Equity | Profit | Balance |
| 2 | 01-Jan | -4.28 | -4.90 | 5.00 | -24.50 | -24.50 |
| 3 | 02-Jan | 1.52 | -6.60 | -33.00 | -57.50 | |
| 4 | 03-Jan | -7.02 | -0.05 | -0.25 | -57.75 | |
| 5 | 04-Jan | -0.48 | -0.05 | -0.25 | -58.00 | |
| 6 | 05-Jan | -1.19 | -0.05 | -0.25 | -58.25 | |
| 7 | 06-Jan | -1.39 | -0.05 | -0.25 | -58.50 | |
| 8 | 07-Jan | -2.96 | -0.05 | 10.00 | -0.25 | -58.75 |
| 9 | 08-Jan | -0.42 | -0.05 | -0.25 | -59.00 | |
| 10 | 09-Jan | -1.09 | 8.55 | 42.75 | -16.25 | |
| 11 | 10-Jan | -1.74 | 7.60 | 38.00 | 21.75 | |
| 12 | 11-Jan | -3.50 | -4.40 | -22.00 | -0.25 | |
| 13 | 12-Jan | -1.98 | -0.05 | -0.25 | -0.50 | |
| 14 | 13-Jan | -1.99 | -0.05 | -0.25 | -0.75 | |
| 15 | 14-Jan | -1.32 | -0.05 | -0.25 | -1.00 | |
| 16 | 15-Jan | 0.30 | -0.05 | -0.25 | -1.25 | |
| 17 | 16-Jan | -1.67 | -0.05 | -0.25 | -1.50 | |
| 18 | 17-Jan | -1.39 | -0.05 | -0.25 | -1.75 | |
| 19 | 18-Jan | -5.26 | -0.05 | -0.25 | -2.00 | |
| 20 | 19-Jan | -0.51 | -0.05 | -0.25 | -2.25 |
<!--StartFragment--> <colgroup><col width="65" span="7" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>
FORMULAS:
| A | B | C | D | E | F | G |
| 1 | Date | Probability | Return | Equity | Profit | Balance |
| 2 | 42370 | -4.27 | -4.9 | 5 | =D3*E3 | =F3 |
| 3 | 42371 | 1.51 | -6.6 | =D4*$E$3 | =G3+F4 | |
| 4 | 42372 | -7.01 | -0.05 | =D5*$E$3 | =G4+F5 | |
| 5 | 42373 | -0.48 | -0.05 | =D6*$E$3 | =G5+F6 | |
| 6 | 42374 | -1.19 | -0.05 | =D7*$E$3 | =G6+F7 | |
| 7 | 42375 | -1.38 | -0.05 | =D8*$E$3 | =G7+F8 | |
| 8 | 42376 | -2.95 | -0.05 | 10 | =D9*$E$3 | =G8+F9 |
| 9 | 42377 | -0.42 | -0.05 | =D10*$E$3 | =G9+F10 | |
| 10 | 42378 | -1.08 | 8.55 | =D11*$E$3 | =G10+F11 |
<!--StartFragment--> <colgroup><col width="130" span="7" style="width:130pt"> </colgroup><tbody>
<!--EndFragment--></tbody>
<!--StartFragment--> <colgroup><col width="65" span="7" style="width:65pt"> </colgroup><tbody>
</tbody>
| A | B | C | D | E | F | G |
| 1 | Date | Probability | Return | Equity | Profit | Balance |
| 2 | 01-Jan | -4.28 | -4.90 | 5.00 | -24.50 | -24.50 |
| 4 | 03-Jan | -7.02 | -0.05 | -0.25 | -25.00 | |
| 8 | 07-Jan | -2.96 | -0.05 | 10.00 | -0.25 | -25.25 |
| 12 | 11-Jan | -3.50 | -4.40 | -22.00 | -27.25 | |
| 19 | 18-Jan | -5.26 | -0.05 | -0.25 | -27.50 |
<!--StartFragment--> <colgroup><col width="65" span="7" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>
