# Select the 'earliest n' or 'latest n' entries of a subset in google spreadsheets

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

=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
``````

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"))` Sep 15, 2015 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. Sep 15, 2015 at 10:15
• 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, 2015 at 11:00
• 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, 2015 at 11:56
• New question Sep 15, 2015 at 12:36