Happy New Year to everyone.
I’ve been busy working on a 3D version of my frame analysis program (which will be published in the not too distant future), but as things have been a bit quiet here recently, here are some things you can do with Excel’s shortest functions (N() and T()), inspired by a post at Bacon Bits.
Mike Alexander’s post (linked above) shows how to add comments to the end of a function by surrounding the text with the N() function, which will evaluate to zero, but I was more intrigued by one of the comments, pointing out that these functions can be used within array formulas to evaluate an embedded array. See the link for more details, but playing with this I found an interesting difference in behaviour compared with using — (double minus), which is often recommended for this purpose.
If we have a list of numbers, starting in cell A1, then the formula:
=COUNTA((OFFSET(A1,{1,5,6},0)))
will return 1, but:
=COUNTA(N(OFFSET(A1,{1,5,6},0)))
will return 3, because the N() function evaluates the Offset function, before the Counta function. In this case:
=COUNTA(–(OFFSET(A1,{1,5,6},0)))
will give the same result, but:
=SUM(–(OFFSET(A1,{1,5,6},0)))
returns #VALUE!, whereas:
=SUM(N(OFFSET(A1,{1,5,6},0)))
returns 15, which is the correct result.
Newton Excel Bach, not (just) an Excel Blog 
I’ve played around with OFFSET() and N() before but something I hadn’t spotted until now and which I think is very interesting is that the unary plus operator has the same effect here as N().
=SUM(+OFFSET($A$1,{1,5,6},0))
gives the same result (the sum of A2,A6,A7) as
=SUM(N(OFFSET(A1,{1,5,6},0)))
I’d previously thought that unary + never did anything.
LikeLiked by 1 person
Colin ….thats Brilliant ….I was the one who posted about N() and T() on Mikes Blog.
And I never new + would replicate what N() does to the array from OFFSET
Wonder if there is something similar for T()
LikeLike
Hi Sam,
It seems that it only replicates N() under very specific circumstances.
Also, it works for me in XL 2010 and XL 2003, but in XL 2007 it returns #N/A.
To be honest, my line of thinking at the moment is that it’s a bug.
LikeLike
I find one way to get a handle on understanding this kind of thing is to use the VBE watch window to evaluate the formula. Entering:
[transpose(offset(A1,{1;5;6},0))]
shows that OFFSET is actually returning an array of range references (the transpose function ensures the formula is array-evaluated.) Note that no code needs to be running for this.
To return a valid array of results it seems any function taking OFFSET as an argument must explicitly make provision for range references and not coerce these to arrays (in XLL terms arguments are registered as type R/U not P/Q and allows for types xlTypeRef or xlTypeSRef.)
There appear to be relatively few built-in functions that fit the above criteria. Other than N and T, which both return the upper left cell value when given a range reference as an argument, COUNTIF, SUMIF, SUBTOTAL, RANK, AREAS and Database Functions also allow arrays of references in some arguments – i don’t know if there are any others? Similar behavior occurs with INDIRECT although the VBE evaluate function won’t work the same in this case.
LikeLike
I’ve just discovered that you can use OFFSET itself to de-reference OFFSET. Weird, but true. Even better, it happily returns both text AND numbers – not just one or the other like N and T do.
So if you’ve got {1;”a”;”b”;2;3;”c”;”d”} in A1 to A7, then =OFFSET(OFFSET(A1,{1;3;4;6},),,) will return {“a”;2;3;”d”}
LikeLike
Wrapping in another offset function seems to just return the same thing for me. You could also try one of these for returning a subset of values from a range:
=LOOKUP({1,5,6},ROW(A1:A7),A1:A7)or=HLOOKUP(A1,A1:A7,{1,5,6}).They may be a little less efficient than OFFSET but allow for either text or numbers and are not volatile (although the second won’t allow blanks or errors in the first element.)
As you say, you need to make sure to dereference OFFSET when arguments contain arrays. Otherwise you can get quite serious errors, for example
=MEDIAN(LOOKUP({1},1,OFFSET(A1,ROW(),)))and=IRR(TRANSPOSE(OFFSET(A1,ROW(),)))both bring up memory exception dialogs in Excel 2010. In older versions the application can even crash if the reference is on another sheet.LikeLike
lhm…that seems to be the case for some other people that have tested this too. I’ve come to the conclusion that OFFSET(OFFSET is very very buggy. For instance, if you already have another OFFSET formula in the workbook, one or both may not return the correct result. And adding in some extra rows into the workbook sometimes fixes the issue, and sometimes not.
In short, steer clear of it.
By way of a demonstration, here’s one weird thing that’s been happening just now for me:
I put this in A1:F1 and named it d
1 2 3 A B C
In A2 I array entered =OFFSET(d,,{0,1,2,3,4,5},,)
When I evaluate this with F9, I get {1,2,3,”A”,”B”,”C”}, which is what I expect.
Then in A3 I array enter =OFFSET(OFFSET(d,,{0,1,2,3,4,5},,),,)
When I evaluate this with F9, I get {1,2,3,”A”,”B”,”C”}, which is what I expect.
THen in A4 I normally enter the same formula (i.e. I don’t array enter it, but just push Enter).
When I evaluate this with F9, I get {1,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}
When I go back and evaluate A2 or A3 I get what I did before, i.e. {1,2,3,”A”,”B”,”C”}
Now when I go and evaluate A4 again, I get {1,2,3,”A”,”B”,”C”}
This is weird. And this isn’t the only weird behavior I’ve been getting when OFFSET(OFFSET is in the sheet somewhere. Sometimes the NON OFFSET(OFFSET version of OFFSET return the wrong thing.
LikeLike
A Google search also shows that one other such function that does the same is CELL(“Contents”,…). But at http://www.excelforum.com/excel-formulas-and-functions/432774-how-to-reverse-a-range-array.html Harlan Grove says this isn’t reliable:
OFFSET only returns Range references. OFFSET called with array 1st, 2nd or 3rd arguments returns something what seems to be an array of range references. Excel can’t deal with such beasts when used as arithmetic operands or arguments to most functions. Fortunately, N() is one of the exceptions, and it effectively converts arrays of range references to arrays of numbers (note: it converts entries that aren’t numeric into
numeric zero). The T() function does the same for strings. CELL(“Contents”,.) isn’t reliable..
Here’s some more info on OFFSET’s weird behaviour:
http://www.office-archive.com/2-excel/b0fb46724e4ae2e2.htm
http://groups.google.com/groups?oi=djq&selm=an_562009917
http://dailydoseofexcel.com/archives/2005/05/11/arrays-with-offset/
LikeLike
Thanks Jeff, all good stuff.
I’ll try and find time to digest it all properly later, but one little nugget I did find that I didn’t know before (or perhaps had totally forgotten about) is that =CELL(“info”) without a cell argument returns information about the last cell that changed, so for instance, =CELL(“address”) will show the address of the last cell that changed.
LikeLike
Pingback: Just discovered – Data Explorer | Newton Excel Bach, not (just) an Excel Blog
It’s worth mentioning that there is a related INDEX() construction which appears to have surfaced on Chinese forums (http://www.exceltip.net/thread-28453-1-1.html):
=SUM(INDEX(A1:A7,N(IF(1,{2,6,7}))))Combining the above IF() construction with both Colin’s +OFFSET() suggestion and with Jeff’s reference to CELL() allows one to return arrays of mixed type from an array of cells. eg:
=MIN(CELL("contents",IF(1,+OFFSET(A1,{1,5,6},0))))Note this allows for negative return values when blanks or text are present unlike the equivalent N() setup. For further investigations on unary plus see: http://stackoverflow.com/q/31090509/1252820
LikeLike
Thanks for the update (and the reminder).
I’m going to look at making better use of the Watch Window, which I have to confess I use hardly at all at the moment.
Also when I get round to the long overdue update of my blogroll it will certainly include some of those linked in the discussions above.
LikeLike
I wrote a post some time back on this: http://dailydoseofexcel.com/archives/2014/11/20/an-index-of-insights-from-excelxor/
LikeLike
Jeff – i did see that post but was trying to locate the original source. XOR LX replies that the INDEX formulation had been posted earlier by a Chinese contributor and from the above link this was already in use at least three years prior to that.
However the
CELL("contents",IF(1,+...))construction i believe to be new and may solve a longstanding problem in array dereferencing which otherwise would need to be split into either T() or N() components. I posted here as the ideas behind it derived from comments given here.LikeLike
Lori – Do you have any comment on the suggestion that Cell(“contents”) is not reliable (originally from Harlan Grove)?
LikeLike
i think Harlan’s referring to the fact that a formula like:
CELL("contents",OFFSET(A1,{1,5,6},))is ok to array-enter over multiple cells but doesn’t allow for multiple return values inside array formulas like SUM(). Similar issues also happen with INDEX, as Jeff says in the link, as well as with a few other functions like VLOOKUP.
It seems many people, myself included, long thought there was no way to resolve these issues. But the magic IF(1,.) trick, that came to light only quite recently, appears to provide a way to fix these type of problems. In the formula:
CELL("contents",IF(1,+OFFSET(A1,{1,5,6},)))the + sign appears to be required to force evaluation of the OFFSET argument as an array instead of a reference:
CELL("contents",{A2,A6,A7})and the IF() part is needed for array evaluation of the CELL function:
{CELL("contents",A2),CELL("contents",A6),CELL("contents",A7)}as follows from checking the output of the watch window using:
[INDEX(<formula>)].In addition to CELL() a similar trick also works for ROW() and COLUMN() functions.
LikeLike
Thanks Lori, that makes sense (although I really need to spend some time on it).
Did you see the latest post at ExcelXOr?:
http://excelxor.com/2015/07/03/redimensioning/
LikeLike
I hadn’t noticed that reference – thanks. It was in fact a discussion with the author that lead to this find. Like you i’d be inclined to use a function for such array manipulations – numpy has some useful routines from what i read.
For applications of the above technique i was more thinking in terms of working with data from multiple sheets.
LikeLiked by 1 person
Indeed, it would appear that 3D-referencing tasks may be where this technique proves most useful, since in those cases we are obliged to employ OFFSET. Otherwise, we should be able to apply the INDEX technique with N(IF(1 to obtain our array of mixed datatypes.
However, it does not appear that the CELL function is required: the unary plus operator acting on OFFSET seems to suffice.
So, with SheetList a row-vector, the CSE-entered:
+OFFSET(INDIRECT(“‘”&SheetList&”‘!A1”),{1;5;6},)
will return a 3-by-3 array of mixed datatypes from the referenced sheets.
Regards
LikeLiked by 2 people
Xor lx, that’s an elegant method and may work inside basic functions (sum, count, min etc) but as per Colin’s comment it looks like one needs something more in general (eg try with any of sumproduct, vlookup, mmult, trend).
In the formula the + sign coerces the result to an array but still contains the range references. To see this you can create a dummy function and insert the formula as an argument eg =check(formula) then step through with the locals window. It follows that the functions for which the + method works without extra coercion generally seem to be those that can natively take ranges as arguments (xloper type R/U). For example these functions allow reduction of full column references such as A:A or multiple area ranges like (A1,A3).
LikeLike
Oops! Sorry, admin. Just “liked” my own post!
LikeLike
Just discovered this thread.
I have been an advocate of the sumproduct() function as a data selector and have used the form
=sumproduct(A1:A4,–(B1:B4=1))
to compute the sum of the A column array that has a 1 in the corresponding B column
The above has made me do some interesting side tests
These all look the same but only some work as needed
=sumproduct(A1:A4,–(B1:B4=1)) works
=sumproduct(A1:A4,+(B1:B4=1)) fails
=sumproduct(A1:A4,0+(B1:B4=1)) works
=sumproduct(A1:A4,(B1:B4=1)+0) works
=sumproduct(A1:A4,N(B1:B4=1)) works
=sumproduct(A1:A4,(B1:B4=1)^1) works
=sumproduct(A1:A4*(B1:B4=1)) works
I fail to see why the + version fails?
It even fails if it is entered as an array function
Bob J.
LikeLike
Bob – see http://mcgimpsey.com/excel/formulae/doubleneg.html for an explanation of what the — does. All the other operations (other than the +) do the same thing, they perform an operation that requires the expression inside the brackets to be converted into an array of numbers, rather than a single Boolean value. The unary + doesn’t work because a unary plus is implied before all numerical expressions, so it doesn’t change anything and is ignored.
But have a look through the comments; Excel isn’t consistent. A unary + is sometimes evaluated, in some versions, but you can’t rely on it.
As for whether to use — or N(), either will work in SumProduct, but I think the N is more readable, and also it works in some situations where — doesn’t.
LikeLike
Hi Doug / Bob – Just to clarify, in the previous discussion i believe it is the handling of range references in array formulas that can be unreliable unless functions like N or T are used.
The + sign is just an identity operation that has no conversion effect on numeric expressions. It is there, according to various sources, for Lotus compatibility where one would type a formula starting with a plus instead of an equals eg
+A1or+"hello". That said, it turns out it does have have some legitimate uses in connection with converting variant arrays or ranges to arrays. For example for adding a column of hex numbers:=SUM(HEX2DEX(+A1:A2))Or sometimes to evaluate formulas correctly when array entering a formula over multiple cells, eg:
=ISTEXT(T(+INDEX({"a","b"},)))LikeLike
“It is there, according to various sources, for Lotus compatibility where one would type a formula starting with a plus instead of an equals eg +A1 or +”hello”.”
Some of us still do that 🙂
LikeLike
Seems like you have access to my computer? 🙂
I have just been puzzling with arrays in the T function. Some inconsistent results!
I found The T() function gave a tidy way to transfer a range to vba from excel.
The range might be a column headed “Data” and containing say 5 elements. Say A1 to A6 in all.
A cell placed adjacent to the vba calling button (say in B3) (so it can be found using the button location) contains the code =T(A1:A6)
So the calling block shows the title of the Data and the vba accesses the data range using the formula i.e. =range(mid(range(“B3”).formula,4,len(range(“B3”).formula)-4))
Of course you do it a little tidier using the burton properties etc and it works great – looks logical from excel and gets a ful range for vba
Unfortunately while the vba always works, the T function often shows up a blank result.
Just noticed this yesterday
Also on Friday at a course it was pointed out to me that Average(A1:A3*B1:B3) gives different results depending on where in memory it sits. It may be related
So
A B C D
1 2 2 2
2 3 6 6
3 4 12 12
#VALUE! #VALUE!
C & D columns all hold the same formulae
ie
=Asum($A$1:$A$3*$B$1*$B$3)
or
=sum(($A$1:$A$3)*($B$1*$B$3))
I can’t explain any of the results – the correct answer is 20.
why it fails in row 4 I do not know.
Ah! It seems to be selecting the row to use from the row of the function
If I replace sum by average I get the same result
How can the location of a direct addressing function fail at one place but not at another?
I know it is not supposed to work – should use sumproduct() but?
If you array enter it, it works fine
Bob J.
LikeLike