Forum Discussion
average of column b of all values in col d found in col a
I have 3 columns( in reality these columns have up to 1200 entries)
Col1 (named range DBNUM Col2 (named range EQUITY) Col15(named range DBNUMRN)
1 22 3
2 45 4
3 12
4 9
5 7
I am trying to find the average of the values in col2 that correspond the values in the 3r column that are found in the 1st col
29 Replies
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?
Assuming your DBNUM field is a strictly increasing set of values, LOOKUP may be used to return values from EQUITY column.
= AVERAGE( LOOKUP( DBNUMRN, DBNUM, EQUITY ) )
PeterBartholomew1Thanks Peter yours is the only formula of about 6 I have tried that has retuned the correct value. The one issue I am running into is that for some unknown reason the total sheet has become volatile,(it may be due to the sheer number of calcs and dynamic arrays it is building) As a result at times the array DBNUMRN (and some others) will at times have a blank row in them. If I click on a few cells in the sheet and get the blank to go away your formula works fine. In this same volatile behavior the results in DBNUMRN calc have some results show up in non consecutive order. Somewhere something on the sheet is causing the volatility and leading to the calculation getting into an iterations loop. I went and set max iterations to 20 just to calm this effect but that's treating the symptom not the problem. I have been very careful to have no circular references in the sheet so I don't know why it is doing this
- You might have used the volatile OFFSET in naming your ranges. Use INDEX instead. Avoid array formulas and replace them with non-array ones. Also, try replacing the volatile INDIRECT with CHOOSE.
