In column "D" I have Dates ( DD/MM/ YYYY ) all in chronological order. Oldest > Newest (top > down).
Column AO has "Cumulative Gain (%)".
0.27%
0.42%
0.60%
0.80%
...
...
Each Cumulative Gain corresponds to a date in Column "D".
I want to know - without using a pivot table - what the end of day cumulative gain was from the previous day (not simply sum total gain%)
You can see a spread-sheet example attached.
Last edited by domgilberto; 12-22-2014 at 08:31 AM.
Is this what you had in mind?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hey thanks for quick response.
Looking over your spread-sheet I have made an error in my explanation.
Because column AO is a cumulative gain, it is telling us what the gain is in chronological order. Therefore using your spread-sheet if you see row 11, the gain for 04/11/2014 was in-fact +0.60%
For 05/11/2014 it's = 1.29% (last row on 05/11/2014) - 0.60% (row 11 for 04/11/2014) = 0.69% for the 05/11/2014
Sorry I was way off in my explanation
I thought that that might have been the case. leave it with me. Another thing. Are you happy for the Table to be peppered with dates where nothing happened 9ie not present in your source data)? Would you like to have a list only of days where something happened?
exactly that - sounds great!
Also, MAX and MIN % days gains would be interesting to know. Wondering how you wuold write this formula
OK. Is this more like what you want??
I missed the MAX-Min bit. Leave that one with me for a moment or so.
OK. how's this?
ooooh nice! I see what you've done there. Cumulative daily gain! Nice work man! Really appreciate this.
Is it possible to see what the daily gain was (non-cumulative). So using your most recent spreadsheet uploaded, the days cumulative gain - the previous days cumulative gain.
An example of this referencing your spreadsheet would be:
> 07/11/2014 daily gain: +0.13%
> 12/11/2014 daily gain: +0.09%
> 14/11/2014 daily gain: +0.14% (days cumulative +1.65 - yesterdays cumulative +1.51)
Thats pretty much it
Looks good... Hopefully this will finish the job. If not, please shout...
Update: looking into this now and will report back shortly!
Last edited by domgilberto; 12-22-2014 at 12:48 PM.
??????????
Sorry didn't mean to say scrap this to what you have done lol! I meant scrap what I had written lol.
It's perfect. I am wondering if it is possible to modify your formula so that is does the SUMTOTAL of the days Gains ($) instead?
So:
=IF(A2="","",IFERROR(LOOKUP(2,1/('Track Record'!$D$8:$D$5000=A2),'Track Record'!$AQ$8:$AQ$5000),""))
Bit in BOLD I want a sum total of the corresponding day (as everything in AQ is $ profit and loss that correspond to date closes)?
Ummm. there isn't anything in AQ on the sheet that's on the Forum... Can you piut in some dummy data and your expected answer.
Sure - thanks so much for being patient, you'll find it attached
What answer are you expecting for, say, 7th November?
Forget it. i'm being a bit thick. Answer: yes you can. Here's the proof.
I have a quick question regarding your most recent spread-sheet:
Is it possible to see what the LOWEST or MIN balance was for the corresponding day too? So in Column AY (lets say) I'd want to see what the lowest the balance was across all entries on the 27/06/2013 and just populate that one balance?
Thanks :D
Like this, maybe???
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks