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:
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?