UpToTheGills
New Member
- Joined
- Aug 26, 2022
- Messages
- 38
- Office Version
- 2019
- Platform
- MacOS
Hi, firstly, if nothing else, thanks for taking the time to read my question even if my explanation is poor and/or you are unable to help.
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:
Ignore the first part of the code - the part in question in from (SUMPRODUCT...
Screenshots below - 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).
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:
Code:
=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...
Screenshots below - 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).