Column A can be the recurring cost. Column B can be the number of months in the recurrence. Column C can divide Column A by Column B to get the monthly cost. Then you can sum column C. For your last.fm example:
A2 = "9.00"
B2 = "3"
C2 = "=A2/B2"
The your total monthly cost would be the sum of Column C.
If you did not insist on array-based formula, then
sumifs(C:C, A:A, A2, B:B, "<="&B2)
entered in D2 and copied around the D column, would do the job.
I don't think that an arrayformula-based solution exists, because arrayformula runs through an array once, performing some element-wise operations. You want to compare each element with all others, which is one step further in complexity (a double for loop).
But you can use a custom function, something like =runningtotal(A2:A, B2:B, C2:C) which processes the columns and returns an array of running totals. The script implementing this function is below; add it via Tools > Script Editor. The logic is quite straightforward: as said above, it's a double for loop.
function runningTotal(names, dates, amounts) {
var sum, totals = [], n = names.length;
if (dates.length != n || amounts.length != n) {
return 'Error: need three columns of equal length';
}
for (var i = 0; i < n; i++) {
if (names[i][0]) {
sum = 0;
for (var j = 0; j < n; j++) {
if (names[j][0] == names[i][0] && dates[j][0] <= dates[i][0]) {
sum = sum + amounts[j][0];
}
}
}
else {
sum = '';
}
totals.push([sum]);
}
return totals;
}
Best Answer
Only Template Owners can update their templates. You could create a spreadsheet based in that template, then publish a new version of the template (be sure to give the proper attribution) or just create a copy of the modified spreadsheet for your own use.
Below there is a list of changes that you could consider to do in your own copy.
Regulars Hours (Column G)
Overtime Hours (Insert column to the right of Column G, new Column H). To insert a new column select the Column G, then right click over the selected area and select
Insert Column to the rightHourly Rate, Cell H26
Cell J25
Cell I27 (Total Amount Due)
Example