Alternative to using an array formula for {=LARGE(IF(... function

**Rabiah** · 05-29-2014, 09:35 AM

I'm (very) new to excel so bare with me.

I have an excel spreadsheet to log incoming samples from different companies (Sample ID, Company,..., Date received,...etc.).

A second sheet in the workbook has a list of every company that has ever sent in a sample (basically, a database with their contact details), in alphabetical order. I have a column in this sheet called 'Last Active' to tell me the date for which the last sample was sent in for each company.

My formula for this is:
{=LARGE(IF('Sheet 1'!C:C=Sheet 2!A2,'Sheet 1'!I:I),1)}

However, this array formula is calculating through around 3500 rows in Sheet 1, which I think is what is causing the huge delay when the sheet calculates. I've had to switch it to manual because it was freezing every time I changed anything in the workbook. Ideally, I'd like for it to be calculating automatically.

Is there a way to replace the array formula to something which isn't so intensive in terms of calculating? Perhaps breaking it down into several columns and hiding the unnecessary ones?

Any help would be appreciated!

**Jonmo1** · 05-29-2014, 09:41 AM

Welcome to the board.

Originally Posted by Rabiah

{=LARGE(IF('Sheet 1'!C:C=Sheet 2!A2,'Sheet 1'!I:I),1)}

However, this array formula is calculating through around 3500 rows in Sheet 1

Actually, that's not quite correct.
It's calculating through ALL rows in the sheet, 65536 (or 1048576 in XL2007+)

Restricting your formula to the actual used range will significantly improve the performance.
To accomodate adding data later, I generally recommend adding 10% to the expected number of rows.
If you have around 3500 rows, then 3500 * 10% = 350
So do 3850 (or just round up to 4000)

{=LARGE(IF('Sheet 1'!C$1:C$4000=Sheet 2!A2,'Sheet 1'!I$1:I$4000),1)}

**ChemistB** · 05-29-2014, 09:41 AM

You can speed it up a lot by reducing it to your 3500 rows instead of the entire column

{=LARGE(IF('Sheet 1'!C2:C3600=Sheet 2!A2,'Sheet 1'!I2:I3600),1)}

Does that speed things up?

**XOR LX** · 05-29-2014, 09:41 AM

Hi,

For a start, you should never use entire column references in array formulas - that formula will calculate over all one-million-plus rows, whether beyond your last non-empty cell or not.

Either change the references to a suitable upper limit, e.g. 4000 (you mentioned 3500) or, if you are using Excel 2010 or later, let us know (you don't give your version in your profile) and there's a non-array version which may be slightly more efficient.

(We can add an INDEX to that current formula to make it technically non-array (i.e. doesn't require CSE), though this doesn't improve things as much as some imagine.)

Regards

**Rabiah** · 05-29-2014, 09:50 AM

I'm using Excel 2011 for Mac.

Sorry, I didn't mention that I am constantly updating the first sheet as companies send in more samples which is why there is no limit set.

So it would be better to set an upper limit and just update this limit as and when required?

Many thanks!

**Jonmo1** · 05-29-2014, 09:52 AM

Look into using Dynamic Named Ranges

http://www.contextures.com/xlNames01.html

Or, make your formula extend a little beyond the actual data set, as suggested.

Then, when you ADD data, use INSERT instead of Paste.
The formula will automatically adjust

**Tony Valko** · 05-29-2014, 09:55 AM

Another option is to use dynamic ranges (ranges that automatically expand when new data is added).

What version of Excel are you using?

**Tony Valko** · 05-29-2014, 10:10 AM

I'm using Excel 2011 for Mac.

Assuming the ribbon is the same as a pc version...

Goto the Formulas tab>Define Name
Name: Company
Refers to:

='Sheet 1'!$C$2:INDEX('Sheet 1'!$C:$C,MATCH("zzzzz",'Sheet 1'!$C:$C))

Name: Values
Refers to:

='Sheet 1'!$I$2:INDEX('Sheet 1'!$I:$I,MATCH("zzzzz",'Sheet 1'!$C:$C))

Then, your array formula becomes:

=LARGE(IF(Company='Sheet 2'!A2,Values),1)

Or:

=MAX(IF(Company='Sheet 2'!A2,Values))

**XOR LX** · 05-29-2014, 10:21 AM

Originally Posted by Tony Valko

='Sheet 1'!$C$2:INDEX('Sheet 1'!$C:$C,MATCH("zzzzz",'Sheet 1'!$C:$C))

Five "z"s, eh? Interestingly random choice! (And slightly less tiresome and automatic than 255 anyway.)

Still, would love it if the OP had a Company listed: "zzzzzz Beds Inc"!!

Regards

**Jonmo1** · 05-29-2014, 10:35 AM

Originally Posted by XOR LX

Still, would love it if the OP had a Company listed: "zzzzzz Beds Inc"!!

Interesting thought,

Might be better to use the numbers in column I to find the last row..

='Sheet 1'!$C$2:INDEX('Sheet 1'!$C:$C,MATCH(9.99999999999999E+307,'Sheet 1'!$I:$I))
and
='Sheet 1'!$I$2:INDEX('Sheet 1'!$I:$I,MATCH(9.99999999999999E+307,'Sheet 1'!$I:$I))

**Tony Valko** · 05-29-2014, 10:42 AM

Originally Posted by XOR LX

Five "z"s, eh? Interestingly random choice! (And slightly less tiresome and automatic than 255 anyway.)

Still, would love it if the OP had a Company listed: "zzzzzz Beds Inc"!!

Regards

Pedantic makes my skin crawl!

Think outside the box. Free yourself from the "herd"!

If I have a list that contains either Y or N (for Yes and No) why on earth would I want to use a lookup value of:

REPT("z",255)

When something as simple as "z" will do?

Same principal applies to the last number in a list.

A bazillion 9's or 100?

**XOR LX** · 05-29-2014, 10:51 AM

Originally Posted by Tony Valko

Pedantic makes my skin crawl!

Think outside the box. Free yourself from the "herd"!

If I have a list that contains either Y or N (for Yes and No) why on earth would I want to use a lookup value of:

REPT("z",255)

When something as simple as "z" will do?

Same principal applies to the last number in a list.

A bazillion 9's or 100?

You misinterpreted my post - I was actually on your side!!

**Tony Valko** · 05-29-2014, 10:56 AM

Originally Posted by XOR LX

You misinterpreted my post - I was actually on your side!!

Yes, I know!

The rant was for the benefit of others that may have an open mind.

**Rabiah** · 05-29-2014, 10:57 AM

Thanks for the suggestions.

I tried the dynamic ranges but it still takes around 10 seconds to calculate. Could that be because I'm referencing entire columns for other formulas on the sheet?

E.g. This formula is to total the number of uninvoiced samples from each company:

=SUM(COUNTIFS('Sheet 1'!C:C,A8,'Sheet 1'!L:L,"N",'Sheet 1'!H:H,"*"&"T"&"*"))

Would it be better to name columns L and H too?

**XOR LX** · 05-29-2014, 11:06 AM

Originally Posted by Tony Valko

Yes, I know!

The rant was for the benefit of others that may have an open mind.

Actually, to be fair though, I think "pedantic" is a bit harsh.

I come from a mathematical upbringing, so I have some sympathy with the 'purist' school which is attracted (?) by the idea of "knowing" that the most rigorous value possible has been used (no matter what the data in question).

Of course, pragmatically, we often "know" that e.g. "zzz" or 10^10 is often more than adequate for these questions, and I often use the latter myself, but mainly since I find the use of "BigNum" to be simply aesthetically unappealing!

For that (rather trite) reason, I don't mind REPT("z",255) - it's quite short! - and at least it saves on having to "think" of an alternative - why five "z"s, and not four, etc.?

So I guess I'm not that fussed on this issue. If you want to know what really does annoy me, it's the use of "BigNum" in e.g. reciprocal LOOKUP constructions on an array of Booleans, where the only possible returns are either 1 or #DIV/0!.

Now at that my mathematical hairs really do prick up on end...

Regards

**Tony Valko** · 05-29-2014, 11:06 AM

You don't need the SUM function with that.

The COUNTIFS function is an efficient function in that it will only evaluate ranges up to the size of the used range.

For example, if you had data down to row 100, these formulas are equally efficient:

=COUNTIFS('Sheet 1'!C:C,A8,'Sheet 1'!L:L,"N",'Sheet 1'!H:H,"*T*")

=COUNTIFS('Sheet 1'!C1:C100,A8,'Sheet 1'!L1:L100,"N",'Sheet 1'!H1:H100,"*T*")

For lots of tips on efficiency:

http://www.decisionmodels.com/

**Jonmo1** · 05-29-2014, 11:07 AM

I think Countifs (all the xxxIFs) function only evaluates the actual Used Range.

But the 'Pedantic' side of me would say it's always better (at least it Never Hurts) to limit the ranges to the actual used area.

**Tony Valko** · 05-29-2014, 11:12 AM

Originally Posted by XOR LX

If you want to know what really does annoy me, it's the use of "BigNum" in e.g. reciprocal LOOKUP constructions on an array of Booleans, where the only possible returns are either 1 or #DIV/0!.

I am in total agreement! That's more or less what I was referring to as "pedantic".

You HAVE to use BigNum. You HAVE to!

LOL

**Jonmo1** · 05-29-2014, 11:14 AM

Originally Posted by XOR LX

So I guess I'm not that fussed on this issue. If you want to know what really does annoy me, it's the use of "BigNum" in e.g. reciprocal LOOKUP constructions on an array of Booleans, where the only possible returns are either 1 or #DIV/0!.

+1 on that.

**XOR LX** · 05-29-2014, 11:16 AM

Originally Posted by tony valko

i am in total agreement! That's more or less what i was referring to as "pedantic".

You have to use bignum. You have to!

Lol

lol!!

**Rabiah** · 05-29-2014, 11:18 AM

Thanks for the help guys. I have no idea what you're ranting about on the side but maybe I will one day ha!

**XOR LX** · 05-29-2014, 11:19 AM

Originally Posted by Rabiah

Thanks for the help guys. I have no idea what you're ranting about on the side but maybe I will one day ha!

Good point! And sorry for hijacking your thread with our esoteric rants!!

**Tony Valko** · 05-29-2014, 11:19 AM

I want to apologize to the OP for "hijacking" the thread and going off into the wild blue yonder!

**Jonmo1** · 05-29-2014, 11:23 AM

Glad we could help, and also sorry for the hijacking..

**kvsrinivasamurthy** · 05-29-2014, 11:46 AM

Try this array formula in place of your formula.Naming of ranges not necessary and when data is added formula takes care of it.

Please Login or Register  to view this content.

**XOR LX** · 05-29-2014, 12:00 PM

Originally Posted by kvsrinivasamurthy

Try this array formula in place of your formula.Naming of ranges not necessary and when data is added formula takes care of it.

Please Login or Register  to view this content.

It may not be "necessary", but given a choice between using a Named Range with INDEX and your suggestion, which is not only volatile (two "unnecessary" uses of INDIRECT), but also seems to have missed the whole point of this discussion (i.e. using whole column references with array formulas), I know which I'd take.

Regards

**Jacc** · 05-29-2014, 12:03 PM

Why not use the Excel Table feature instead of dynamic named ranges?

**Rabiah** · 05-29-2014, 05:31 PM

I don't think using Excel Table will improve the efficiency of the calculation. Ideally, I wanted a solution that didn't use an array formula at all but I can't seem to find a way around it!

**Jacc** · 05-30-2014, 05:52 AM

Maybe it won't. The reason I suggested it is because then you don't have to think about any dynamic named range formulas.

Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Re: Alternative to using an array formula for {=LARGE(IF(... function

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Non array alternative for percentile function

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions