Hello,
I have a transactions table as attached that shows the unit price of a fund at date of purchase. I'm trying to create a pivottable/chart that will show how the average unit price changes over time.
i.e (fund 2)
Month 1 price = 1170 (av=1170 (1170/1))
Month 2 price = 1181 (av=1175.5 (1170+1181/2))
Month 3 price = 1070 (av=1140.3 (1170+1181+1070/3))
Month 4 price = 1099 (av=1130 (1170+1181+1070+1099/4))
...and so on.
As shown, some months could have multiple purchases, but generally only one purchase per month per fund. Data will be added to the table on monthly basis.
Looking to create a chart and perhaps spark lines on a per-fund basis. Using Excel for Mac 16.58.
Thank you
Hi and welcome to our forum.
You can add a column to your table to calculate the running average per fund using the AVERAGEIF function. Note: your data has multiple purchases in a single month (even on the same day), but your example doesn't allow for this. To handle this I've averaged the average running total in the PivotTable in the file attached.
Hope that points you in the right direction.
Mynda
Thanks for taking the time to respond. This pointed me in the direction I needed to go - AVERAGEIF was the key.
