What I want to do is this:
- Have one cell calculate the number of items present in a particular column, e.g. C1 = 129
- Have a second cell use that number as part of the definition, e.g. D1 = AVERAGE($A$1:$A$(C1)), meaning ($A$1:$A$129)
Why I want to do it is this:
I've got a formula, cribbed form another website, that counts the number of distinct items in a column. This formula will not tolerate blanks, nor (afaik) can it be made to do so. I use this formula in a weekly report, and the number of items in the column changes from week to week. Instead of manually changing the value in the multiple places it's used (as I do now), I'd love to simply drop in the new data, have the sheet count the number of items, and use that number in the cribbed formula.
Here's the cribbed formula in question:
=SUM(IF(FREQUENCY(MATCH(Data!A2:A100,Data!A2:A100,0),MATCH(Data!A2:A100,Data!A2:A100,0))>0,1)) Where the "A100" is the bottom extent of the data-filled column for this week. Next week it could be A200 or A50. However, for this week, it I were to change A100 to A101 (where no data exists), the formula will fail gloriously with a "Value Not Available Error".
Any way to do what I need?
3 Answers
Use the INDIRECT() worksheet function.
In a nutshell, the formula =INDIRECT("A1") returns whatever value is contained in the cell A1. So all you need to do is, instead of "A1", replace that static value with whatever calculation you are using to come up with the cell row/column. The result of the whole formula will be the value contained within the cell specified in the argument to the INDIRECT() worksheet function.
Here's a simplistic example
Value of cell A1 = 1
Value of cell A2 = 2
Value of cell A3 = 3
Value of cell A4 = blank/null
Value of cell A5 = =COUNT(A1:A3) (replace this with your cribbed formula)
Value of cell A6 = =AVERAGE($A$1:INDIRECT("$A$" & INDIRECT("A5")))
Go to Formulas -> Evaluate Formula and step through the evaluation if you want to see how it works. Basically, it concatenates the string "$A$" with the value "3". The "3" is obtained from the value of the cell A5, which is 3, because counting 3 cells returns 3 in this example. Then we call INDIRECT() a second time, to get the value of $A$3, but what is actually returned is a cell reference! If you don't believe me, evaluate the formula and watch what Excel turns the call INDIRECT("$A$3") into during evaluation. It's strange, but it works -- INDIRECT() behaves differently depending on whether it is being output into a range reference or into a value.
It is possible to amend your original formula to allow (but not count) blanks, i.e. this version
=SUM(IF(FREQUENCY(IF(Data!A2:A100<>"",MATCH(Data!A2:A100,Data!A2:A100,0)),ROW(Data!A2:A100)-ROW(Data!A2)),1))
confirmed with CTRL+SHIFT+ENTER
.....but you may want to try this one too - shorter and doesn't need "array entry"
=SUMPRODUCT((Data!A2:A100<>"")/COUNTIF(Data!A2:A100,Data!A2:A100&""))
Instead of INDIRECT you could also consider using INDEX. Assuming your count is in J3 and the data is in column A), this formula would sum all elements: =SUM(A2:INDEX(A2:A10000,J3))
For your specific problem, this should work:
=SUM(IF(FREQUENCY(MATCH(Data!A2:INDEX(A2:A10000,J3),Data!A2:INDEX(A2:A10000,J3),0),MATCH(Data!A2:INDEX(A2:A10000,J3),Data!A2:INDEX(A2:A10000,J3),0))>0,1))
The huge advantage of INDEX compared to INDIRECT is that it's non-volatile and generally faster upon calculation. The non-volatility means that Excel only needs to calculate the formula when any predecessor is changed. INDIRECThowever will be calculated upon any calculation/change done in the workbook - thus esp. in large model slowing down the model significantly!
