Help U75 Excel gurus - need your help with a formula (daily percentage change)

[pinkychukkles]

Was about to sign up to the MrExcel.com forums but thought I'd try here first to avoid having to register there .

Usually I can figure out the solution from googling but this particular problem has proved a little more difficult.

In summary, I have a trading journal with a worksheet to input details of trades and a worksheet to display daily statistics. What I can't figure out is how to calculate the percentage gain or loss compared to the previous trading day (which isn't necessarily the day before).

I've figured out how to calculate total gain/loss from any particular day but I can't figure out how to look up the last running total figure from before the day in question.

Existing formula that isn't quite working:

=IF(OR(ISBLANK(W5),W6=0,COUNTBLANK(W6)),"",(SUMPRODUCT(--(INT('TradeInputSheet'!$B$3:$B$328)=W5),'TradeInputSheet'!$T$3:$T$328)/(SUMPRODUCT(--(INT('TradeInputSheet'!$B$3:$B$328)<W5),'TradeInputSheet'!$W$3:$W$328))))

Ignore the first part of the code - the part in question in from (SUMPRODUCT...

I'll get some screenshots up in a sec...
Thanks in advance for any pointers.




So, hopefully the images will help with explaining what I am attempting to achieve.
Take the date, e.g. 25-Aug-22, total up all the gains/losses for that date on the Trade Input Worksheet (Total A), then calculate that gain or loss compared to last total of the previous day (B).

 

[pinkychukkles]

Solved with help from the aforementioned MrExcel, posting here in case it helps someone else.

I needed to pull in an additional total from another worksheet and the below code worked:

=IF(OR(ISBLANK(W5),W6=0,COUNTBLANK(W6)),"",SUMIFS('TradeInputSheet'!$T$3:$T$328,'TradeInputSheet'!$B$3:$B$328,">="&W5,'TradeInputSheet'!$B$3:$B$328,"<"&W5+1)/(SUMIF('TradeInputSheet'!$B$3:$B$328,"<"&W5,'TradeInputSheet'!$T$3:$T$328)+SUMIF('Account Transactions'!$B$3:$B$39,"<="&W5,'Account Transactions'!$D$3:$D$39)))

Alternative simpler way, using different functions where W5 is the date and W6 is the amount of profit or loss for that day:

=IF(OR(ISBLANK(W5),W6=0,COUNTBLANK(W6)),"",W6/INDEX(TradeInputSheet!$W$3:$W$328,MATCH(MAXIFS(TradeInputSheet!$D$3:$D$328,TradeInputSheet!$D$3:$D$328,"<"&W5),TradeInputSheet!$D$3:$D$328,0)))