I'm trying to make a trading log for Eve Online and I need to keep track of average sale prices for arbitrary items. I do a sum of FILTER results to find the total cost of all of a particular item and then divide by the count to find the average. The problem is, I want to keep a more accurate rolling average of my current stock only, i.e. I want to only use the latest N entries from my FILTER results, where N is number bought minus number sold. Conversely, when calculating profit it would be more accurate to only count entries which have already been sold, i.e. the earliest ones.
Is there a way to do this? The only way I could think of would be using a for loop to count backwards from the end of the list, but I don't know how to do that in a spreadsheet. These are the formula I'm currently using:
Item =UNIQUE(B3:B) Bought =SUM(FILTER( D:D , B:B = Q3)) Sold =SUM(FILTER( L:L , J:J = Q3)) In Stock =R3-S3 Avg. Buy Value =ArrayFormula(SUM(IFERROR(VALUE(REGEXREPLACE(FILTER( E:E , B:B = Q3)," ISK","")))))/R3 Avg. Sell Value =ArrayFormula(SUM(IFERROR(VALUE(REGEXREPLACE(FILTER( M:M , J:J = Q3)," ISK","")))))/S3 Avg. Profit =V3+U3 Total Profit =W3*S3 Avg ROI =-W3/U3