Caalculate Profiit and Loss column for multiple buy and sell entries of similar stock

[mohammed3786]

Hello 

Thankyou for help in advance. i am currently working on a work system for my trading and I'm stuck at this point. 
i have multiple buy entries of one stock ..comprising multiple quantities...and I'm calculating the profit/ loss by subtracting my entry point from the actual live market price.(*quantity)

But my issue is when Im selling a particular stock ...how do I get the profit/ loss (for that particular sell quantity) based of on multiple buy of that stock before. 

for example in my below attached excel on sheet - "TRADE JOURNAL "
the are multiple "buy" stocks initially and if you can see the in column Q i was able to calculate the current profit and loss by subtracting my entry price from the live current trading price * quantity ......but when I'm selling (SELL) In line 13,  I'm afraid this logic will not work. 
when I'm selling ...i only enter my exit price and exit quantity.....to calculate the P/L the buy price shud be the average of all the entry prices of that particular  stock before corresponding to the buy quantities...May be i wud need to segregate my P/L by realised and unrealized...can anybody please help me out in how to go about it. Feel free to edit the excel sheet if needed. 
My ultimate goal is to calculate the my current profit or loss (based on individual transactions) hope i make sense. 
Your assistance is greatly appreciated. 

[#2 · Accepted solution]

@mohammed3786 Perhaps the attached sheet helps. I added a column to calculate the averages and changed some formulae. The changes in the TRADE JOURNAL are in the columns/cells with the yellow headers/background.

[#3]

@Riny_van_Eekelen 

Hi Riny, thankyou for your reply and effort. i was studying the excel sheet and i understood what you have done, however i can you help me address the following query

in the excel attached i have tweaked a little to show the "SELL "action p/l as realised P/L in the S column..lets consider the three transactions on rows 8,10 n 13....the idea is the first two buy transactions of Steel authority of India gives me a loss of ((-3100-5262.5=-8362.5)....now when i SELL Steel authority of India in the 13th row ...this loss of (-832.5 in S13) is a part of the -8362.5..( this -832.5 is realised out of -8362.5)..i hope I'm making sense.

so now my overall position on steel authority of inida is -7530. with remaining 400 shares

is thr any way i can show this (-7530) maybe in a different column for every script where I stand ?
So for Steel authority of inida i stand at -7530 after three transactions ....similarly for every other stock..

my other query is ...is the any way I can show the open transactions in yellow and if I sell all the quantities of a particular stock i had...then the row wud automatically turn back to white?

yr input is highly appreciated. Thankyou very much Riny.

[#4]

@mohammed3786 Please see the attached workbook. Added yet another column using two SUMIF functions in one formula. Not sure though it this really does what you want/need.