Need help averaging repeating fields

[djeans]

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.

 

[comment]

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.

 

[djeans]

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