Forum Discussion
average of column b of all values in col d found in col a
By the way, you didn’t mention if you also have array formulas.
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
- 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.
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.
