Forum Discussion
average of column b of all values in col d found in col a
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 ) )
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
- 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.
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
