Forum Discussion
average of column b of all values in col d found in col a
SO the glitch I am having seems to be because EXCEL is saying I have circular references. I was careful not to have any, and the circular reference in error checking is greyed out. It keeps saying circular reference and I go to the cell it listed in the bottom left corner... refill the cell and then it sends me to another one somewhere else which doesn't exist. After 15 or so completely random cells always within a column of similar cells I gave up After looking online the only real explanation I found that made any sense was it is due to too many array formulas ,,,, BONUS points to @Twifoo
Not sure that is actually the issue as it isn't resolved yet other than to allow iterations in calculate
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.
