May 01 2019 07:55 AM
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
May 02 2019 08:07 PM
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
May 03 2019 01:44 AM
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.
May 03 2019 08:31 AM - edited May 03 2019 08:38 AM
@Peter BartholomewThe content of BM10:BO10 is static, Just values no formula. The helper columns make no reference to any of the values in BM10:BO10. Other rows in these columns are not dependent on the value in BM10:BO10. The helpers are dependent on the values in row 11, 12, and 13 which are dependent on values in other places on the sheet
May 03 2019 08:41 AM
May 03 2019 08:48 AM
@Peter Bartholomew Not sure where you see a circular reference possible,,, The helper columns evaluate the data in the rows of column Bm:BO based upon criteria like > < =
The other areas in some formulas where a cell does refer to itself is when using ROW and also when using Countif ex. in cell BP25 =IFERROR(INDEX(MAIN[DBNUM],MATCH(0,IF(($BR$21>MAIN[EQUITY])*($BR$22>MAIN[EV]),COUNTIF($BP$24:$BP25,MAIN[DBNUM]),""),0)),"")
May 03 2019 09:01 AM
May 03 2019 09:21 AM
@TwifooIt doesn't calculate slow at all,,, calcs done in a mouse click it is the random circular refences that create an odd glitch that shuffles data at time is all
May 03 2019 09:28 AM
May 03 2019 09:30 AM
The Countif was the problem I fixed it and no more Glitch
May 03 2019 09:41 AM