# How do I calculate average cost for Share/Stock transaction

Status
Not open for further replies.

#### anilbaheti

##### New Member
Hi

I am attaching a template, wherein I can calculate average cost in first block, which in reality does not matches with actual average due to FIFO concept applied when selling a share/stock. I am unable to figure out how to get to the actual average cost.

Appreciate if someone can help on this template.

PS: I did go through below link, it does not matches with average and the template requires buy and sell to be two different block.

Regards
Anil

#### Attachments

• 12.1 KB Views: 6

#### Excel Wizard

##### Active Member
Please try at F2

=SUMIFS(D\$2:D2,B\$2:B2,">0")-SUM(TEXT(-SUMIF(B\$2:B2,"<0")-MMULT(--(ROW(B\$2:B2)>TRANSPOSE(ROW(B\$2:B2))),B\$2:B2*(B\$2:B2>0)),"0;\0")*(C\$2:C2-N(+C\$1:C1)))

Ctrl+Shift+Enter

#### Attachments

• 13.4 KB Views: 6

#### AlanSidman

##### Well-Known Member
Here is a template that I use for tracking stock transactions.

#### Attachments

• 13.5 KB Views: 9

#### anilbaheti

##### New Member
Please try at F2

=SUMIFS(D\$2:D2,B\$2:B2,">0")-SUM(TEXT(-SUMIF(B\$2:B2,"<0")-MMULT(--(ROW(B\$2:B2)>TRANSPOSE(ROW(B\$2:B2))),B\$2:B2*(B\$2:B2>0)),"0;\0")*(C\$2:C2-N(+C\$1:C1)))

Ctrl+Shift+Enter
Fantastic! This worked like a charm. Thanks a ton.

#### anilbaheti

##### New Member
Here is a template that I use for tracking stock transactions.
Thank you Alan for sharing. I will use the best practices.

#### bosco_yip

##### Excel Ninja
Another formula option, without array entry, just using normal entry

In F2, formula copied down :

=SUMIF(D\$2:D2,">0")-SUMPRODUCT((-SUMIF(B\$2:B2,"<0")>SUMIF(OFFSET(B\$1,,,ROW(B\$1:B1)),">0"))*(-SUMIF(B\$2:B2,"<0")-SUMIF(OFFSET(B\$1,,,ROW(B\$1:B1)),">0"))*(C\$2:C2-N(+C\$1:C1)))

#### Attachments

• 12.4 KB Views: 3

#### anilbaheti

##### New Member
Another formula option, without array entry, just using normal entry

In F2, formula copied down :

=SUMIF(D\$2:D2,">0")-SUMPRODUCT((-SUMIF(B\$2:B2,"<0")>SUMIF(OFFSET(B\$1,,,ROW(B\$1:B1)),">0"))*(-SUMIF(B\$2:B2,"<0")-SUMIF(OFFSET(B\$1,,,ROW(B\$1:B1)),">0"))*(C\$2:C2-N(+C\$1:C1)))

View attachment 79511
Thanks. this works well except when I add another symbol and its transaction. See below. I just copied same rows and changed Symbol to YYY and it shows twice the average.

#### Attachments

• 11.4 KB Views: 1

#### anilbaheti

##### New Member
Can someone help me with above query?

#### bosco_yip

##### Excel Ninja
Can someone help me with above query?
This is the forum rule, one question one post.

Your additional question in level #7, appear a new question.

So,

Please open a new thread for your new question with all attachment.

Thank you

Regards

Status
Not open for further replies.