I would like to calculate an average item cost for a trading log, using only the most recent number of items equal to the number in stock. Currently, I'm only averaging over all purchase history, which is inaccurate in the event of price fluctuation.
Here is an example of two sales. Assume I've bought 5 and sold 3, leaving me with a stock of 2.
Date and Time Item List price Count Sale Currency Client 2015.09.14 05:29 Fleeting Propulsion Inhibitor I 1,903,008.01 ISK 4 -7,612,032.04 ISK ISK xxxx 2015.09.14 07:27 Fleeting Propulsion Inhibitor I 1,903,008.01 ISK 1 -1,903,008.01 ISK ISK xxxx
I want to count the second (latest) entry, but only 1/4 of the first entry.
If each sale was for only a single item, I could simply read the latest number of entries equal to my stock. This was what I asked in my last question, and this was my solution (where Q1 is the item name and T1 is current stock:
=ARRAYFORMULA(SUM(VALUE(REGEXREPLACE(query(A:E, "select E where B = '"&Q1&"' order by A desc limit "&VALUE(T1)&"")," ISK",""))))
I figure an iterative sum of the 'count' column up to the stock number is the best way to do this, but I don't know if that's possible.