Forum Discussion
average of column b of all values in col d found in col a
I am with Twifoo as far as the goal of removing
=INDIRECT(ADDRESS(ROW()+10,20)
is concerned. It is a volatile function do not see what it achieves over and above a relative reference to the addressed cell. Since the column picked up using INDEX value depends on the content of the cell, it is pretty arbitrary and circular references are likely to be a problem to track down.
=IFERROR(INDEX($BC$10:$BL$10,,(INDIRECT(ADDRESS(ROW(),20)))),(IFERROR(INDEX($BC$10:$BL$10,,(INDIRECT(ADDRESS((ROW()-(COUNT($T$2:$T$11)),21))…………..
So in the results column it travels down col 20 fine and yes I could use relative reference in that snippet. Now when I get to the next iferror I am a nonspecific number down the results column.. hence the (-COUNT...) because when I shift over to col 21 I need the result at the top. But I have no way of knowing when this event would trigger,
The Indirect allows me to start this second iferror looking at the top of its column
Does that make sense?
- Your use of multiple IFERROR functions is inefficient. It would be better if reduced to a minimum, perhaps only one.
Such inefficiency is further worsened by the multiple INDIRECT functions within them. INDIRECT can be completely avoided in your case. You only have to establish a definitive logic that determines which column to CHOOSE; otherwise, you need to refer to only one column. Succinctly, a change in your data layout is inevitable.Thanks I will definitely look into CHOOSE. No good way to put the data in one column and still not run into the issue of which row to select, The Formulas for creating each helper column are already very long. To conjoin them so all data would be in one column would make the formula a major novel and lead to excel to doing a ton of recalculations over and over
Putting the implementation issues aside for the moment, I suspect that the logic of the solution needs to be checked through at the 'array of arrays' level.
"Column 20 is a helper column built with a ton of criteria
that returns the column in BM10:BO10 I want the data from"
If I were with you, could you demonstrate to me that none of the criteria depend upon the content of columns BM:BO? The combination of circular references and volatile functions means that a cycle of complete recalculations will be triggered by virtually any interaction with the workbook.
