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:
will return 1, but:
will return 3, because the N() function evaluates the Offset function, before the Counta function. In this case:
will give the same result, but:
returns #VALUE!, whereas:
returns 15, which is the correct result.