Forum Discussion

Dichotomy66's avatar
Dichotomy66
Brass Contributor
May 01, 2019

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          ...
excel
Formulas and Functions
Dichotomy66's avatar
Dichotomy66
Brass Contributor
May 02, 2019

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

Twifoo's avatar
Twifoo
Silver Contributor
May 02, 2019
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.
  • Dichotomy66's avatar
    Dichotomy66
    Brass Contributor
    May 02, 2019

    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

    • Twifoo's avatar
      Twifoo
      Silver Contributor
      May 02, 2019
      Please specify your INDIRECT formula. Perhaps, I can suggest an alternative that avoids the volatile INDIRECT.
      By the way, you didn’t mention if you also have array formulas.
      • Dichotomy66's avatar
        Dichotomy66
        Brass Contributor
        May 02, 2019

        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

Resources