Hot answers tagged google-spreadsheets
2
There is a way to do it, but it's a bit cumbersome. I've prepared the folowing data in the DATA sheet:
In another sheet (SORT) you can apply the SORT function:
=SORT(DATA!A3:J40,DATA!A1,DATA!A2,DATA!B1,DATA!B2,DATA!C1,DATA!C2,DATA!D1,DATA!D2,DATA!E1,DATA!E2,DATA!F1,DATA!F2,DATA!G1,DATA!G2,DATA!H1,DATA!H2,DATA!I1,DATA!I2,DATA!J1,DATA!J2)
This will yield ...
1
Use the following notation:
=AVERAGE(A$1:A5)
Copying this formula, will yield:
=AVERAGE(A$1:A7)
Absolute references explained:
$A$1; Both the column and row reference are fixed.
$A1; Only the column reference is fixed.
A$1; Only the row reference is fixed.
Reference: Absolute reference
1
Never mind, I found the solution.
It was because my data was text format (despite changing the formatting many times in the format options). To counteract this I had to make another table which used =VALUE(cell) on each piece of data. This gave me the numerical value allowing me to make graphs successfully.
1
Still don't have a solution for how to directly get values of unchecked checkboxes, but I was at least able to write a function that compares the correct answer to the submission.
This splits the correct answer into an array then compares each item to the submission:
function checkAnswer(answer, key) {
var kArray = key.split(', ');
var nArray = [];
for ...
1
You cannot, like in other spreadsheet programs like f.e. Excel, define explicitly on what list of columns to sort on in what order.
However, you can achieve the same result manually. If you wanted f.e. to primarily sort by column A and secondarily by column B, you can just first sort everything by column B and afterwards by column A.
This works because, ...
1
I created a little script that converts the string literal "_now" to current datetime -
function onEdit(e) {
if (e.range.getValue() == "_now") {
e.range.setValue(new Date());
}
}
I find it pretty handy to type _now and have it convert to current datetime value.
See https://developers.google.com/apps-script/quickstart/macros and ...
Only top voted, non community-wiki answers of a minimum length are eligible
