Forum Discussion
Calculate profit and loss and average cost for my portfolio in stock market
Hi nalnofal
if I did not get you wrong, you could use the SUMPRODUCT function for this:
=(SUMPRODUCT(Table5545[BUY QTY];Table5545[Cost])+SUMPRODUCT(Table5545[SELL QTY];Table5545[Cost]))/SUM(Table5545[[BUY QTY]:[SELL QTY]])
It multiplies all buy-quantities with the cost, then adds the sell-quantities multiplied with the cost and in the end it divides that number by the total quantities buy/sell.
Hi Martin_Weiss
very interesting from you to use sumproduct formula for my buy/sell , its work fine in my first Example but unfortunately not work with other trading company that i have .
the problem happen when i make more trading Buy/sell for same company in shares market for multiple action
for example i put tow more company in my excel sheet : (CompanyB and CompanyC) with more data i fill, so that you can look into it to understanding all trading i make and how my bank calculations loss/profit and how they calculate my shares average cost/total Shares cost
i reshare my Test Example