05-01-2019 07:55 AM
05-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
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
05-01-2019 11:40 AM
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 ) )
05-02-2019 08:25 AM
@Peter BartholomewThanks 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
05-02-2019 09:15 AM
05-02-2019 09:29 AM
@TwifooI used INDEX for all my named ranges. No OFFSET in the entire sheet, I do have over 18 Dynamic named ranges though. I did use INDIRECT for a small number of formulas 18 to be exact, The volatility started showing up before that. However likely I will go back and try to find a way using index instead, Not sure I will be successful as using INDIRECT(ADDRESS... for this application may be too difficult
05-02-2019 09:34 AM
05-02-2019 09:47 AM
The entire sheet is all array formulas
=IFERROR(INDEX($BM$10:$BO$10,,(INDIRECT(ADDRESS(ROW()+10,20)))) is the basic building block formula
Column 20 is a helper column built with a ton of criteria that returns the column in BM10:BO10 I want the data from. When the iferror occurs it goes to the same formula but looking at col 21.. etc etc until it has looked thru 4 columns
05-02-2019 09:55 AM
As an FYI the CountA for the sheet is currently at 13807 and only about 800 of those do not contain formulas
05-02-2019 10:38 AM
05-02-2019 11:27 AM
Interesting difference in approach. I work very hard to ensure every possible calculation is an array formula in order to simplify the logic of the model. I have come across factors of 2 lost in speed for a financial model (more for IFS are replaced since they are very fast) but nothing to put me off array formulas as a basic building tool. I have even gained a factor of 3 in speed on one client workbook by omitting intermediate step-by-step calculations.
There used to be concerns about array formulas mysteriously stopping calculating part way through without reporting an error but I think that is a thing of the past. For the future, modern Dynamic Arrays have the potential to change everything.
05-02-2019 11:33 AM - edited 05-02-2019 11:39 AM
@Peter BartholomewI agree and as my sheet is constantly referring to arrays in one table to an array in another table any changes would be minor
I think my real problem is with the dynamic named ranges as it is when I click on a blank cell in the same column but above the named ranges start cell that the sheet does a minor recalc that glitches though ti does still do the calcs. I am in the process of switching the data fields to structured tables and using those for references rather than the named ranges. This should improve accuracy and speed as the tables are not at the top of the worksheet
Though if you could take a peek at the indirect formula mentioned above I would appreciate it
05-02-2019 11:48 AM
05-02-2019 12:27 PM
You may not have noticed but in the tiny implementation based upon your problem definition, I converted both ranges to tables. I then used defined names to refer to ranges within the table. This makes the named ranges dynamic.
05-02-2019 12:27 PM
@TwifooA I am reworking some things calculation speed doesn't seem to be an issue as it does this in about 1sec. The weird glitch when you click on an empty cell that is in a dynamic named ranges column but above its start point that is having the data reshuffle in areas across the sheet is a pain. No data results are being dropped, but occasionally because it will insert a blank this causes a problem until I click on another cell and the blank goes away.
No sense in sending it to you to look at the indirects until after I have modified the sheet formulas to use structured table references instead of dynamic named ranges. This does seem to be working better on a bunch of levels While I am at it I will look for dtat columns where I used array formulas and didn't need to.
05-02-2019 12:33 PM
05-02-2019 12:58 PM
We may well finish up at a similar place. It is just that I start with the assumption of array calculation and only decompose to single cell calculation when I have reason to do so. That might be a use case that required incremental updates and only a small proportion of the calculation changes. The other reason to use relative referencing is that I simply run out of array dimensions.
I think my use of arrays will increase with the modern Dynamic Arrays. I really like the fact that the formula is held in a single cell and the spill region is determined without further user input (either manually determined fill ranges or the cumbersome CSE).
05-02-2019 01:56 PM - edited 05-02-2019 01:58 PM
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
05-02-2019 02:31 PM
I am with @Twifoo as far as the goal of removing
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.
05-02-2019 04:26 PM
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?
05-02-2019 06:50 PM
05-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
05-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.
05-03-2019 08:31 AM - edited 05-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
05-03-2019 08:41 AM
05-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)),"")
05-03-2019 09:01 AM
05-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
05-03-2019 09:28 AM