Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
    
Very nice explanation! –  chuff Jan 15 '13 at 19:06
    
Thanks, @allquixotic! This appears to exactly what I needed! The interior INDIRECT (i.e. your INDIRECT("A5")) did not appear to be necessary, though. Now, I have: J3 = COUNTA(A2:A10000) and J4 = SUM(IF(FREQUENCY(MATCH(A2:INDIRECT("A" & (J3+1)),A2:INDIRECT("A" & (J3+1)),0), ... and it's all working beautifully. –  John Straffin Jan 15 '13 at 19:16

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&""))

share|improve this answer
1  
Honestly, the others answered my question, but you correctly divined and solved the actual problem. It's likely yours is the solution that I'll use, but since others coming here will likely be due to the question I asked, I'm going to leave one of the others marked as the "answer". –  John Straffin Jan 16 '13 at 21:38
    
No problem John, happy to help..... –  barry houdini Jan 16 '13 at 23:24

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!

share|improve this answer
1  
+1 Peter - I agree. INDIRECT has many uses but I'd prefer INDEX here - using INDIRECT also means you are "hardcoding" the column refs with "A" - unless you particularly need to do that because you always want to refer to a specific column then it's probably unwise - add another column at A and the INDIRECT function is now referencing the "wrong" column....... –  barry houdini Jan 15 '13 at 20:36
    
I'm sorry, but I don't see how INDEX can help me in this situation. Can you explain? I plugged your formula above into my spreadsheet, where J3 is indeed the count (generated by =COUNTA(A2:A10000)) and column A is the data being counted, but the formula generated an answer of 0. What exactly is being SUMmed? By and large, this is not numerical data, but a table of inter-related items and counts of those items. The sheet is providing statistical analysis. –  John Straffin Jan 15 '13 at 23:05
    
John, I used the SUM formula as an generic example to demonstrate how to use INDEX instead of INDIRECT. I extended the answer to give you the formula you're looking for. –  Peter Albert Jan 16 '13 at 7:03
    
Ah ... sorry for being dense. Thanks! –  John Straffin Jan 16 '13 at 19:47
    
Verified that this one works, too, and much more cleanly. Someday, I may even understand exactly what it's doing! Thanks very much! –  John Straffin Jan 16 '13 at 21:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.