Google Sheet: Reset Average for Financial Portfolio

ZackChua95

New Member
Joined
May 21, 2022
Messages
1
Office Version
  1. 365
Platform
  1. MacOS
Hi all!

I've recently found an issue with my formula when I am filling up the shares I re-bought.

In 2021, I've bought bought 6 TSLA shares and sold all. However, the portfolio side would still reflect the average cost of all my shares without any reset.

Because it did not reset, in 2022 I bought more TSLA shares, it will add in the average along with my past shares that are all being sold. Is there a formula to reset the average cost to 0 if I had no more shares?

Thank you for reading through and assisting me on this in advance.

Cheers,
Zack

*PS, do excuse my poor English
*Ignore the realised profit part, I didn't manage to find a code for it
 

Attachments

  • Formula 1.png
    Formula 1.png
    250.6 KB · Views: 8
  • Formula 2.png
    Formula 2.png
    147.3 KB · Views: 8
  • Formula 3.png
    Formula 3.png
    189 KB · Views: 7
  • Formula 4.png
    Formula 4.png
    161 KB · Views: 7
  • Formula 5.png
    Formula 5.png
    160.3 KB · Views: 8
  • Formula 6.png
    Formula 6.png
    162.7 KB · Views: 8

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,223,350
Messages
6,171,584
Members
452,411
Latest member
colpie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top