Google Sheets – How to Perform Iterative Sums

google sheets

Continuation of my previous question.

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.

Best Answer

This is quite a natural problem, but I couldn't think of an easy solution without creating some extra columns. I suggest a custom function such as partialTotal below. Its parameters are:

  1. The range of data to process
  2. The number of items you want to add
  3. The number of the column containing counts (relative to the range)
  4. The number of the column containing quantity to be totaled (relative to the range)
  5. Whether to take the latest data (bottom rows): true or false.

Example: suppose you have the following in the columns B:E, and you want to total the sales for the last 8 items of type "want". 

+-------+-------+-------+------+
| Item  | Price | Count | Sale |
+-------+-------+-------+------+
| stuff |     9 |     8 |   11 |
| want  |     8 |     7 |   22 |
| want  |     9 |     6 |   33 |
| stuff |     8 |     5 |   44 |
| want  |     9 |     4 |   55 |
| stuff |     8 |     3 |   66 |
+-------+-------+-------+------+

The command would be 

=partialTotal(filter(B2:E, B2:B="want"), 8, 3, 4, true)

where filtering happens first, and then totaling: take 8 items, counts are in the 3rd column (within the range), quantities to total are in the 4th column, and latest are wanted. The output is 77, which is 55 + 33*(4/6).

Here is the code of the custom function, to be placed in Tools > Script Editor.

function partialTotal(data,count,countColumn,quantityColumn,latest) {
  if (latest) {
    data.reverse();
  }
  var total = 0;
  var i = 0; 
  while (count>0 && i<data.length) {
    if (data[i][countColumn-1] <= count) {
      total = total + data[i][quantityColumn-1];
      count = count - data[i][countColumn-1];
    }
    else {
      total = total + data[i][quantityColumn-1]*count/data[i][countColumn-1];
      count = 0;    
    }
    i++;
  }
  return total;   
}
Related Topic