# Need help averaging repeating fields

This topic is 958 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

I'm trying to create a formula to average some fields, but am currently stumped.

I have a FMP database that I created to use as a journal for my stock trading.

I have a field set to calculate the average price of the stock based on the initial price, plus any additional stocks that I purchased after the initial purchase. The average price of the position should "average down" as I scale in more shares at a lower price, or average up if I buy higher priced shares.

The fields related to this formula would be openingPosition (how many shares purchased),

openingPrice (cost of initial shares purchased),

scaleInPosition (number of additional shares purchased after the initial purchase - a repeating field with 10 repetitions),

scaleInPrice (price of the new shares that were scaled in  - also a repeating field with 10 reps)

averagePrice (the calculation field to return the average price of the position after scaling in)

The closest I got to this working was:

(openingPrice + Sum ( scaleInPrice )) / (1 + Count ( scaleinPrice ))

but that only works if everything bough is 1 (1 initial share, 1 share scaled in each time, etc)

If I scale in 10 shares, it does not work.

any help or suggestions would be greatly appreciated.

Thanks.

##### Share on other sites

This would be much simpler if you had a related table of transactions, where each purchase would be an individual record with fields for price and amount purchased. Then you could use a summary field to produce the average of price weighted by amount.

With your current structure, you will need to multiply each individual price by the corresponding amount, sum the results and divide by the total amount.

##### Share on other sites

Thanks for the reply. That gives me a good idea where to start. I'll try the related table approach.

##### Share on other sites

This topic is 958 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account