Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: average of column b of all values in col d found in col a

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-01-2019 07:55 AM

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

Labels:

29 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-01-2019 11:40 AM

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 ) )

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 08:25 AM

@Peter BartholomewThanks 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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 09:15 AM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 09:29 AM

@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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 09:34 AM

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.

By the way, you didn’t mention if you also have array formulas.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 09:47 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 09:55 AM

As an FYI the CountA for the sheet is currently at 13807 and only about 800 of those do not contain formulas

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 10:38 AM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 11:27 AM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 11:33 AM - edited 05-02-2019 11:39 AM

@Peter BartholomewI 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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 11:48 AM

As with everything else in life, array formulas are efficient if used sparingly. When used excessively, as described here, array formulas become inefficient.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 12:27 PM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 12:27 PM

@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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 12:33 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 12:58 PM

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).

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 01:56 PM - edited 05-02-2019 01:58 PM

SO the glitch I am having seems to be because EXCEL is saying I have circular references. I was careful not to have any, and the circular reference in error checking is greyed out. It keeps saying circular reference and I go to the cell it listed in the bottom left corner... refill the cell and then it sends me to another one somewhere else which doesn't exist. After 15 or so completely random cells always within a column of similar cells I gave up After looking online the only real explanation I found that made any sense was it is due to too many array formulas ,,,, BONUS points to @Twifoo

Not sure that is actually the issue as it isn't resolved yet other than to allow iterations in calculate

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 02:31 PM

I am with @Twifoo as far as the goal of removing

=INDIRECT(ADDRESS(ROW()+10,20)

is concerned. It is a volatile function do not see what it achieves over and above a relative reference to the addressed cell. Since the column picked up using INDEX value depends on the content of the cell, it is pretty arbitrary and circular references are likely to be a problem to track down.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 04:26 PM

=IFERROR(INDEX($BC$10:$BL$10,,(INDIRECT(ADDRESS(ROW(),20)))),(IFERROR(INDEX($BC$10:$BL$10,,(INDIRECT(ADDRESS((ROW()-(COUNT($T$2:$T$11)),21))…………..

So in the results column it travels down col 20 fine and yes I could use relative reference in that snippet. Now when I get to the next iferror I am a nonspecific number down the results column.. hence the (-COUNT...) because when I shift over to col 21 I need the result at the top. But I have no way of knowing when this event would trigger,

The Indirect allows me to start this second iferror looking at the top of its column

Does that make sense?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 06:50 PM

Your use of multiple IFERROR functions is inefficient. It would be better if reduced to a minimum, perhaps only one.

Such inefficiency is further worsened by the multiple INDIRECT functions within them. INDIRECT can be completely avoided in your case. You only have to establish a definitive logic that determines which column to CHOOSE; otherwise, you need to refer to only one column. Succinctly, a change in your data layout is inevitable.

Such inefficiency is further worsened by the multiple INDIRECT functions within them. INDIRECT can be completely avoided in your case. You only have to establish a definitive logic that determines which column to CHOOSE; otherwise, you need to refer to only one column. Succinctly, a change in your data layout is inevitable.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-02-2019 08:07 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-03-2019 01:44 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-03-2019 08:31 AM - edited 05-03-2019 08:38 AM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-03-2019 08:41 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-03-2019 08:48 AM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-03-2019 09:01 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-03-2019 09:21 AM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-03-2019 09:28 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-03-2019 09:30 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-03-2019 09:41 AM

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

by Dichotomy66 on May 01, 2019

Microsoft Store

Education

Developer