Forum Discussion
average of column b of all values in col d found in col a
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
As an FYI the CountA for the sheet is currently at 13807 and only about 800 of those do not contain formulas
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).
- Yes I did notice that. At the time I was trying to avoid tables but alas another theory shot to hell lol. The larger problem I had with your solution is I was not just trying to sort large to small but thru comparison of what turned about to be 18 different criteria to establish the ordering,,, particularly for the middle values in the results column. I will assuredly revisit this though once I have reset my other 7 dynamic tables to use structured table references rather than dynamic named ranges
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.
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.
- As with everything else in life, array formulas are efficient if used sparingly. When used excessively, as described here, array formulas become inefficient.
PeterBartholomew1I 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
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.
- Please attach your sample file. I believe we can avoid the volatile INDIRECT. You also admitted the sheet is all array formulas, which another way of saying “the sheet calculates very slowly”. We will try to avoid array formulas, too.
