google sheets
I'm desperately looking for a way to write a function like this:
=SUM(A1:CELL(B1))/COUNT(A1:CELL(B1))
where CELL(B1) contains a text, for example "A37".
In this way, I can do the average only in the cell that I want consider for now.
I now seems a strange request, because I could just change directly the formula, but in my case I have to use this value "A37" in a lot of formulas, and so I'm looking for a way to use parametric cell.
The following little script will calculate the median at the same time.
function myMedian(instances, values) {
var ins = [], half;
for(var i = 0, iLen = instances.length; i < iLen; i++) {
for(var k = 0, kLen = instances[i]; k < kLen; k++) {
ins.push(Number(values[i]));
}
}
// reference: https://gist.github.com/caseyjustus/1166258
half = Math.floor(ins.sort( function(a,b) {return a - b;} ).length / 2);
return ins.length % 2 ? ins[half] : (ins[half - 1] + ins[half]) / 2;
}
See example file I prepared: Median calculation
This is another solution.
=INT(E4/COUNTBLANK(B2:B))
The COUNTBLANK formula will count the blank cells, independent of the range and E4 will be divided by it. The INT formula will simply round the value to the nearest integer, since it concerns days.
I've created an example file for you: Count Blank Cells
Best Answer
As I understand your requirement:
is possible, but does not seem advisable.