Highlighted
Contributor

# 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                              3

2                                       45                             4

3                                      12

4                                      9

5                                      7

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

29 Replies
Highlighted

# Re: average of column b of all values in col d found in col a

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

Highlighted

# Re: average of column b of all values in col d found in col a

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.

Highlighted

# Re: average of column b of all values in col d found in col a

@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

Highlighted

# Re: average of column b of all values in col d found in col a

To ultimately resolve the issue, you must attach a sample file. Note that you aren’t required to attach the actual file. So, no confidentiality issues would arise. I only require a sample (not the actual) file.
Highlighted

# Re: average of column b of all values in col d found in col a

@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)),"")

Highlighted

# Re: average of column b of all values in col d found in col a

The presence of IF sandwiched in your formula presupposes that is is an array formula. And there are plenty of them. It’s not surprising that aside from circular references, the array formulas also significantly cause slow calculation. As I declared earlier, that is an excessive use of array formulas. Avoid those array formulas by all means!
Highlighted

# Re: average of column b of all values in col d found in col a

@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

Highlighted

# Re: average of column b of all values in col d found in col a

In that case, I will never discover the cause of the circular reference without your sample file.
Highlighted

# Re: average of column b of all values in col d found in col a

The Countif was the problem I fixed it and no more Glitch

# Re: average of column b of all values in col d found in col a

Hopefully, you did. I wish you good luck with your work.