Max Drawdown by ID and Transaction

[Autonomous]

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>
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ID
[/TD]
[TD]Transaction
[/TD]
[TD]Balance by ID
[/TD]
[TD]Date
[/TD]
[TD]Drawdown
[/TD]
[TD]Max Drawdown
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1001
[/TD]
[TD="align: right"]75.00
[/TD]
[TD="align: right"]75.00
[/TD]
[TD="align: right"]1/1/2019
[/TD]
[TD="align: right"]0
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1001
[/TD]
[TD="align: right"]-27.00
[/TD]
[TD="align: right"]48.00
[/TD]
[TD="align: right"]1/2/2019
[/TD]
[TD="align: right"]-0.36
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1002
[/TD]
[TD="align: right"]35.00
[/TD]
[TD="align: right"]35.00
[/TD]
[TD="align: right"]1/1/2019
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1003
[/TD]
[TD="align: right"]18.00
[/TD]
[TD="align: right"]18.00
[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1002
[/TD]
[TD="align: right"]12.10
[/TD]
[TD="align: right"]47.10
[/TD]
[TD="align: right"]1/2/2019
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]1001
[/TD]
[TD="align: right"]-3.00
[/TD]
[TD="align: right"]45.00
[/TD]
[TD="align: right"]1/2/2019
[/TD]
[TD="align: right"]-0.4
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]1004
[/TD]
[TD="align: right"]15.00
[/TD]
[TD="align: right"]15.00
[/TD]
[TD="align: right"]1/3/2019
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]1003
[/TD]
[TD="align: right"]-20.00
[/TD]
[TD="align: right"]-2.00
[/TD]
[TD="align: right"]1/1/2019
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]1003
[/TD]
[TD="align: right"]-6.00
[/TD]
[TD="align: right"]-8.00
[/TD]
[TD="align: right"]1/2/2019
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]1004
[/TD]
[TD="align: right"]16.00
[/TD]
[TD="align: right"]31.00
[/TD]
[TD="align: right"]1/4/2019
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]1002
[/TD]
[TD="align: right"]-5.00
[/TD]
[TD="align: right"]42.10
[/TD]
[TD="align: right"]1/3/2019
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Autonomous]

Wow not a single reply?