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:
- The range of data to process
- The number of items you want to add
- The number of the column containing counts (relative to the range)
- The number of the column containing quantity to be totaled (relative to the range)
- 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;
}
Best Answer
You can put a comment on any cell, and other people can reply your comment. After the discussion is over, you can resolve it so it get archived.
On the top right hand corner, there is a "Comments" button. You can use it to browse both active and resolved comments. You can also delete discussion threads you no longer want to keep.