Autonomous
New Member
- Joined
- Apr 30, 2019
- Messages
- 5
I have a worksheet with IDs in column A, Transactions in Column B, Balance by ID in Column C and Date in Column D. I need to calculate Drawdown in Column E per ID for each transaction in order. Your help would be greatly appreciated!
The problem is the formula below only works for one ID at a time. I need a formula that will group transactions by each ID and display the Drawdown calculation in column E.
<code>Below is formula and example of Drawdown output in Column E but I need output for each ID and associated transaction to display in column E so the following formula needs to be altered. There are literally hundreds of IDs so it is not feasible to nest IF statements per ID.
=IF($A$2:A2=A2,C2/MAX(IF($A$2:A2=A2,$C$2:C2))-1,"")
</code>
<tbody>
</tbody>
The problem is the formula below only works for one ID at a time. I need a formula that will group transactions by each ID and display the Drawdown calculation in column E.
<code>Below is formula and example of Drawdown output in Column E but I need output for each ID and associated transaction to display in column E so the following formula needs to be altered. There are literally hundreds of IDs so it is not feasible to nest IF statements per ID.
=IF($A$2:A2=A2,C2/MAX(IF($A$2:A2=A2,$C$2:C2))-1,"")
</code>
| A | B | C | D | E | F | |
| 1 | ID | Transaction | Balance by ID | Date | Drawdown | Max Drawdown |
| 2 | 1001 | 75.00 | 75.00 | 1/1/2019 | 0 | |
| 3 | 1001 | -27.00 | 48.00 | 1/2/2019 | -0.36 | |
| 4 | 1002 | 35.00 | 35.00 | 1/1/2019 | ||
| 5 | 1003 | 18.00 | 18.00 | 1/1/2019 | ||
| 6 | 1002 | 12.10 | 47.10 | 1/2/2019 | ||
| 7 | 1001 | -3.00 | 45.00 | 1/2/2019 | -0.4 | |
| 8 | 1004 | 15.00 | 15.00 | 1/3/2019 | ||
| 9 | 1003 | -20.00 | -2.00 | 1/1/2019 | ||
| 10 | 1003 | -6.00 | -8.00 | 1/2/2019 | ||
| 11 | 1004 | 16.00 | 31.00 | 1/4/2019 | ||
| 12 | 1002 | -5.00 | 42.10 | 1/3/2019 |
<tbody>
</tbody>
