I was trying to answer this StackOverflow question and stumbled on a way to consistently crash Excel. Here’s the formula that did it
If I remove the TRANSPOSE function, no problemo. But as soon as I type it in, boom. The idea is to perform a two stage lookup in a single array formula. I have this range
| Product | Code |
| S1 | 19875 |
| S2 | 19834 |
| S1 | 13575 |
| S1 | 35675 |
| S2 | 47875 |
And I have this range
| Code | Indent |
| 47875 | 20 |
| 35675 | 25 |
| 19875 | 40 |
| 19834 | 15 |
| 13575 | 22 |
I want to sum all of the Indents for each Product. I started by getting an array of the Codes
($A$2:$A$6=A2)*($B$2:$B$6) which resolves to {19875;0;13575;35675;0}
Next, I put those Codes into a MATCH formula
MATCH(($A$2:$A$6=A2)*($B$2:$B$6),Sheet2!$A$2:$A$6,FALSE) which resolves to {3;#N/A;5;2;#N/A}
I filtered out the NAs
becomes
I offset from Sheet2!B1
When I SUM that up, I get 40. I thought maybe if I TRANSPOSEd the array, I could trick it into summing properly, but instead I get “Microsoft Excel has stopped responding”. I never did find a satisfactory way to get the job done with one formula. I used a helper column and it was easy, just not satisfying.
If you’d like to crash your Excel 2003 (didn’t try other versions), download the file and array enter the formula in cell F1.
You can download recursivelookup.zip
Interesting. In 2007 (fully patched, as far as I know), I get #N/A. In 2010 (test version I had downloaded), I get 87. Neither crashes.
I tried in Excel 2007 and it results in #N/A error, but doesn’t crash.
All seems to work fine till we apply the SUM(…) function: for some reason it is just adding a single element of the {40;”Indent”;22;25;”Indent”} array. I say a single element because I have tried extending the SUM(…) function to the rows below and the result is, surprisingly, {40;0;22;25;0}). So it’s working, but not in a matricial way… Why is this?
Incidentally, I also tried changing the IF(ISNA(MATCH(…))) for an IFERROR(MATCH(…)); this works fine, but then resulst in the same error when you get to apply the OFFSET(..).
That’s pretty interesting. I also couldn’t crash Excel 2007 with the same formula, but I did get the same odd behavior as Gor. Evaluating the formula piecemeal with the F9 key in the formula bar works just fine. But letting the whole thing calculate (as an array formula) returns #N/A. Specifically, if I highlight everything inside the ‘SUM’ and then hit F9, it gives back ‘{40,”Indent”,22,25,”Indent”}’, and then ‘SUM’ works on that. If I highlight the entire formula after the ‘=’, I get #N/A.
I wonder if it could be ‘OFFSET’ that’s messing things up. Doesn’t ‘OFFSET’ return a range, in this case a multi-area one? Maybe breaking up the calc manually forces Excel to display the intermediate array result, but the actual internal representation of ‘TRANSPOSE(OFFSET(…))’ is something ‘SUM’ can’t operate on directly. It looks like a weird edge-case that just slipped through. Not an excuse for crashing, though…
I think this gets the job done in one fell swoop.
CSE:
or for those like to avoid CSE formulas, try:
or
I believe Laurent Longre pointed this out a long time ago and I have run into it on occasion both while working with formulas and with charts. If I recall correctly John Walkenbach used the function in one of his books on charting and gave me credit for it — but I could be mistaken. ;-)
OFFSET returns some undocumented structure that is not always usable. Wrap it in N() to get Excel to “clean up” the result.
The following array formula works in 2003 and 2010. 2007 was not tested.
=SUM(N(OFFSET(Sheet2!B1,IF(ISNA(MATCH(($A$2:$A$6=A2)*($B$2:$B$6),Sheet2!A2:A6,FALSE)),0,MATCH(($A$2:$A$6=A2)*($B$2:$B$6),Sheet2!A2:A6,FALSE)),0)))
and in 2010 the following array formula also works:
=SUM(N(OFFSET(Sheet2!B1,IFERROR(MATCH(($A$2:$A$6=A2)*($B$2:$B$6),Sheet2!A2:A6,FALSE),0),0)))
Of course, one could use MS Query to create a parameterized query joining the 2 tables and also get the desired result. {grin}
OFFSET(range,array,…) effectively returns an array of range references. OFFSET and INDIRECT can only return range references. =SUM(OFFSET(range,array,…)) formulas will return array results when array-entered into multiple cell ranges. Maybe not specified, but it has characteristics of ranges and arrays.
I think this makes the work,
=SUMPRODUCT(($A$2:$A$6=A2)*SUMIF(Sheet2!$A$2:$A$6,B2:$B$6,Sheet2!$B$2:$B$6))
Regards
Here is an alternate solution
On sheetC If A1 has S1 then in Cell B1 type
=SUM((IF((Product=A1),Code1)=TRANSPOSE(Code2))*TRANSPOSE(Amt))
Array entered
Where Code1 = Dynamic Range for Codes on SheetA starting from Row2
Where Code2 = Dynamic Range for Codes on SheetB starting from Row2
Likewise Product and Amt
@Elias,
That’s brilliant! I would never have thought of using SUMIF in array form. The nice thing about your formula is that it’ll work even if sheet2 has multiple instances of the same code. (…assuming that we’d want to sum the Indent field for each distinct code.)
You could use the below.
//Ola.S
Table 1
Col: A B C
Product Code Intendent
Col C:
=SUMIF($E$4:$E$8,B4,$F$4:$F$8)
Table 2
Col: E F
Code Intendent
Table 3
Pivottable (Col:A and C)
@Sam: You could drop the IF if you used this:
Aside: Is there ever a time when we really need IF? I used to think that the only time you really needed an IF statement is when the thing you were evaluating had errors. But have since realized that ISERROR(array)=false is an effective boolean way to remove errors. Anyone have any thoughts on times where you can’t help but use an IF statement?
@Jeff, Dont give up on IF yet…As I mentioned earlier (on the EH Blog and in EH Academy)
IF has a unique property of returning an array of Numbers or FALSE, something that cant be replicated via a boolean logic
Here is an example
Assume the following data where each CLIENT could be repeated serval times
CLIENT AMT
ABB 100
AREVA 200
SIEMENS 300
TYCO 400
ABB 500
GE 600
ABB 700
If you want to find out the AMT next to a specific instance of a client, so find the Amt next to the 2nd instance of ABB
=Index(Amt,Small(IF(Client=”ABB”,Amt),2)
Try this without IF and the solution becomes clumsy…
@sam,
The formula does not work. Why are you using INDEX? How about just using {=SMALL(IF(CLIENT=”ABB”,AMT),2)} ?
Opps typo
=Index(Amt,Small(IF(Client=”ABB”,Row(Amt)),2) – CSE
– Assuming data starts from Row1 or Row(Amt)-Row(Header) otherwise
@Reuvain – You need Index, The Small/If combo find the position of the specific instance of the the duplicate
The Index picks the amt
@SAM: Ahhhh….where it shines is that with a pure boolean approach you can’t find the kth smallest number in an array excluding zeros, but with the IF approach you can, because SMALL ignores anything that evaluates to FALSE. O
So say we want to return the 2nd largest number in the array {1,2,3,4,5} that exceeds 2. We can test which numbers are of interest with this:
…which returns ={FALSE,FALSE,TRUE,TRUE,TRUE}.
We can pinpoint the position in this matrix where this is true by doing this:
…which returns ={0,0,3,4,5}
But when we try to find the 2nd smallest number of interest, things get ugly.
This:
…returns 0.
Whereas if we use an IF statement, like this:
…which returns ={FALSE,FALSE,3,4,5}, then when we wrap a SMALL function around that, it ignores the FALSE items, and returns 4
And while you could always substitute those zeros out in the non IF approach like this:
…it’s damn messy.
So okay, I’ll use it for that. But nuttin else ;-)
One more way to get the 2nd instance of ABB without using IF.
=SMALL(Amt*(Client=”ABB”),2+COUNTIF(Client,”ABB”))
Ctrl+Shift+Enter
Regards
Sorry forgot to use the tag code.
Regards
@Sam: Another time when I guess you need an IF is when you WANT to return an error when a certain condition is met, such as this:
I can’t think how you’d easily do this without an IF.
So all right, all right…I’ll use it for that. But nuttin else, including your example above. Clumsy, this? Phoohey
;-)
Monty Python anyone? All right… all right… but apart from better sanitation and medicine and education and irrigation and public health and roads and a freshwater system and baths and public order… what have the Romans done for us?
@Elias: That’s genius. Thanks for sharing…I’m sure I can put it to good use.
@Elias: …although it does rely on the data being sorted ascending. But I still love your COUNTIF clause:
Actually, I take back my thought that IF is probably the only way to intentionally and conditionally introduce errors such as #N/A into a range.
Say we’ve got the following numbers in A1:A5, and we want to plot the ones over 30 on a graph, while not plotting the others via introducing an #N/A error.
10
20
30
40
50
We could use this:
…but we could also skip the IF and use this:
So to quote a misquote, “IF eunt domus” (more Monty Python)
@Reuvain. Thanks, for your comment. Sorry I didn’t reply earlier.
@Jeff. It’s always a pleasure to receive a positive comment, especially IF it comes from the Hero.
Regards
@Elias: Second the Genius for N+Countif(Client,”ABB”)
@Jeff : Data need not be sorted
@Jeff,
I like your method of using the MATCH function to introduce the errors.
In Excel 2010 there is a new function called AGGREGATE which is like SUBTOTAL on steroids. One of its parameters let you ignore errors when calculating “aggregates”. With this in mind you can put the conditional array in the denominator so that it will throw a #DIV/0 error if the condition is false. So in your example with the numeric array, instead of the messy version
you could instead use
which will return 4.
And in Sam’s example you could use
Scratch the last comment about Sam’s example. I keep forgetting that he’s doing 2nd “instance” instead of 2nd smallest.
This will work for Sam’s example
and as the AGGREGATE function natively takes arrays it does not need to be entered with Ctrl+Shift+Enter.
Evaluating formulas in the VBE watch window can help clarify things, for example:
[transpose(offset(a1,{0;1},0))]
Function results can generally be broken down into three types: single values (numbers, text, boolean, errors), arrays of values, or references (that may be converted to values or arrays). But what happens if a function returns the other possibility: an array of references?
I’m not totally clear on the details but this type of result needs to be dereferenced otherwise it can throw an error or even crash earlier versions. N, T and several other functions can resolve this data type in reference arguments, L.Longre has some examples on C.Pearson’s site using the legacy CALL function which still applies to xlm/xll.
re It’s always a pleasure to receive a positive comment, especially IF it comes from the Hero.
…I’m afraid I’m not he. Think you’re confusing me with Daniel Ferry. I am but a visitor to his blog and a student on his course (which is excellent, by the way)
@Sam: re @Jeff : Data need not be sorted. In your or my example, no. But in Elias’ example, yes.
@Jeff,
We can tweak Elias’s formula so that it need not be sorted as follows
@Reuvain: That’s really cool!
Also problems in Excel 2010, take these two for instance:
=SMALL(TRANSPOSE(OFFSET(A1,{0,2,4},0)),2)
=SMALL(LOOKUP({1,2,3},{1,2,3},INDIRECT({“A1″,”A3″,”A5”})),2)
In Excel 2003 these are equivalent to =SMALL((A1,A3,A5),2).
In Excel 2007 the second returns the same result but the first returns an error.
In Excel 2010 both bring up memory exception dialogs.
Change references to refer to another sheet, however, and both give errors in all versions and the first crashes 2003. You could use SMALL(N(… instead but it won’t exclude blanks or non-numeric data in the range.
Creating multiple area or 3D references in this way would be very handy for extending formula functionality. MS should really get around to fixing these issues properly as they cause application instability and loss of user confidence. Still i don’t see it happening any time soon given it’s been flaky so long.
Well the issue with Excel crashing is getting MUCH worse. Apparently many of my deployed Excel-based programs are becoming corrupted such that all you have to do is open them and save them. MS tried to blame my code, but I showed it happening with code/macros disabled. I’ve provide data from ProcMon and ProcDump and they are “studying” it.
Background: Workbooks originally developed in Excel 2003, now enhanced using Excel 2007. Remain in .XLS format because they’re used in 14 heterogeneous client enviroments. Running in mostly WinXP/XL12 (Excel 2007) targets that do include SP2. Some, but I cannot confirm all, have been opened, used and saved with Excel 2010.