New answers tagged formulas
1
I'm not sure if you necessarily require a Google Apps Script solution; the tags you have used suggest that a spreadsheet formula might be adequate. If this is the case, then it is definitely possible to sum only rows that are not hidden with SUBTOTAL.
For example:
=SUBTOTAL(109;A:A)
will sum all cells in column A that are not hidden.
1
Turns out its not possible (for now).
Feature request: https://code.google.com/p/google-apps-script-issues/issues/detail?id=195
0
My approach would be to "normalise" the data first:
=ArrayFormula(IF({1,0};IFERROR(REGEXEXTRACT(A2:A;"^([a-zA-Z]+)"));B2:B))
and then you can actually wrap this directly in a QUERY function to produce the desired output:
=ArrayFormula(QUERY(IF({1,0};IFERROR(REGEXEXTRACT(A2:A;"^([a-zA-Z]+)"));B2:B);"select Col1, sum(Col2) where Col1 != '' group by Col1 ...
0
I believe you are looking for a nested if statement. Put this formula in B1 and then drag it down as far as needed...
=IF(AND(A1>1,A1<10), A1*.75,(IF(AND(A1>10,A1<20), A1*.60, (IF(AND(A1>20,A1<30), A1*.50, (IF(test, then_true, otherwise_value))))))
I just did the first 3 iterations. You can continue from there and set the values as needed. Make ...
1
If you add the following formula in C7 with sum written in B7, then the total will be calculated the way you want:
=SUM(ARRAYFORMULA(IF(A2:A6=1;C2:C6-B2:B6,0)))
0
I would add a fourth column for the calculated line item result (using an if statement) and then do a simple =sum() at the end.
Make a new column to the right of C. Add this if statement:
=if(A1=0, C1, C1-B1)
Then sum col C.
1
You can still use nested IFs:
=ARRAYFORMULA(if(row(A:A)=1,"Readmission Days",
if(G:G="","",if(AL:AL="","",(G:G)-(AL:AL)))))
There's no need to make a script to do that :)
1
Tried to find all kinds of solutions with formulas, but wasn't successful. Therefore I wrote this very small script to do the work for you:
function myCalc(range1,range2) {
if(range1.length != range2.length) {
throw "Both ranges need to be the same !!";
return;
}
var l = range1.length;
var output = [];
for(i=0; i<l; i++) {
...
1
If you don't want to precalculate the subtotal of off days, you can use an array formula:
=arrayformula(SUM((A:A="Jess")*(B:C="OFF")*1))
A:A="Jess" returns a bunch of TRUE and FALSE, in your sample, it would be {TRUE, FALSE, FALSE, TRUE, FALSE}
B:C="OFF" returns another bunch of TRUE and FALSE, in your sample, it would be {FALSE, TRUE; FALSE, FALSE; ...
0
On the left of the table create a column for each employee. Set value to =IF(A{row}="{employee}",1,0), then you can just take the array product of this column and the subtotal column.
Top 50 recent answers are included
