CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
DarkLite1
DarkLite1

Reputation: 14745

Calculate the average value of a coin in possession

The following Google sheet contains transactions in crypto currencies. One line can represent a "buy" or a "sell" of a crypto coin: image of transactions

What I'm trying to achieve is to calculate the average price paid for a specific coin that is still in the wallet.

In the example above that would be:

Simply calculating the average will result in 116.93 EUR which is incorrect.

What is the correct formula to achieve the desired result of 133.15 EUR?

Upvotes: 0

Views: 224

Answers (1)

Neven Subotic
Neven Subotic

Reputation: 1429

Check out averageIfs which allows you to stipulate conditions for which records to include. This way you can only include records where Type=Buy and Ticker=AXS. This example would look like this =AVERAGEIFS(C:C, J:J, "Buy", H:H, "AXS").

Yet to know which coin remains in your wallet may require you do create a column which tracks this. Currently I am unable to tell which coin remains in your wallet, I can only see what has been sold and what has been bought, and these two things are not the same (having, trading).

Upvotes: 0

Related Questions