1

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
0

While filter is intuitive, query is a more powerful tool, and is better suited for your task.

Suppose A has the timestamp, B the type of product, and C the data you want to average. The command

=query(A:C, "select C where B = 'ProductName' order by A desc limit 10")

selects 10 most recent transactions for the given product name. If you want to average these, put the average around the command:

=average(query(A:C, "select C where B = 'ProductName' order by A desc limit 10"))

To take the 10 oldest instead of 10 newest, change desc (descending) to asc (ascending).

To use the product name contained in another cell (e.g., F1), insert it by concatenation:

=average(query(A:C, "select C where B = '"&F1&"' order by A desc limit 10"))
  • This is what I was having trouble with; I couldn't figure out how to put a variable in the query. =average(query(A:C, "select C where B = '"&F1&"' order by A desc limit 10")) – Macattack278 Sep 15 '15 at 6:09
  • The problem I'm having now is that the number of items isn't necessarily the limit value. If I want the first N items, I may have to use the first M entries because entries may consist of several of the same item. I figure I could use a while clause, using an iterative sum of the item count, but there doesn't appear to be a while clause in the query language docs. – Macattack278 Sep 15 '15 at 10:15
  • 1
    I think you'll need a helper table for that: something that aggregates the results of the first table, eliminating repetitions. Then you can query that table. – user79865 Sep 15 '15 at 11:00
  • 1
    It would help to see a sample of your data. But, as a separate question, because this one is about selecting "latest n" and it has been answered. – user79865 Sep 15 '15 at 11:56
  • 1
    New question – Macattack278 Sep 15 '15 at 12:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.