@XOR LX:
Thank you very much, it is working beautifully!
You're welcome, though it's a shame that you can't employ Jonmo's helper-column set-up, as it is more efficient and also easier to understand.
Would you mind explaining how it works please?
The part:
INDEX(NovDataTable[Inc Closed Date - Inc Date],1)
simply returns, in this case, the range reference for the first cell in the
Inc Closed Date - Inc Date column. Let's assume that that is cell A2 for the sake of argument.
In order to use this SUBTOTAL set-up, we must pass an array of range references to that function, which we achieve via the part:
OFFSET(INDEX(NovDataTable[Inc Closed Date - Inc Date],1),ROW(NovDataTable[Inc Closed Date - Inc Date])-MIN(ROW(NovDataTable[Inc Closed Date - Inc Date])),)
which works since the part:
ROW(NovDataTable[Inc Closed Date - Inc Date])-MIN(ROW(NovDataTable[Inc Closed Date - Inc Date]))
will return an array of integers from 0 up to one less the number of rows within that column. For example, if the number of rows within
Inc Closed Date - Inc Date were 9, the above would return:
{0;1;2;3;4;5;6;7;8}
Hence, in that case (and recall that we are assuming that the reference of the first cell in that column is A2) the above construction would be equivalent to:
OFFSET(A2,{0;1;2;3;4;5;6;7;8},)
which would return, in this case, an array of cell references, each equivalent to offsetting cell A2 by 0, 1, 2, etc. rows downwards, i.e.:
A2;A3;A4;A5;A6;A7;A8;A9;A10
For example, assuming some random values within those cells, we might have:
{#VALUE!;75;32;#VALUE!;#VALUE!;43;#VALUE!;#VALUE!;#VALUE!}
We then pass this array to SUBTOTAL with an initial parameter of 102, which will return a 1 if that cell is both visible and contains a numeric, and 0 otherwise. Note that #VALUE! is not a numeric.
For example, let's assume that rows 5, 6 and 7 are hidden, then:
SUBTOTAL(102,{#VALUE!;75;32;#VALUE!;#VALUE!;43;#VALUE!;#VALUE!;#VALUE!})
would return:
{0;1;1;0;0;0;0;0;0}
Finally:
=AVERAGE(IF({0;1;1;0;0;0;0;0;0},NovDataTable[Inc Closed Date - Inc Date]))
is the easy part, taking the average of values only where the above array contains a 1.
I guess it won't be possible to do it with sumproduct then?
Not without convolution, no. SUMPRODUCT sums, in general, rather than taking averages.
Regards
