Tag Archives: Formula Abbreviation

Shortest Formula Challenge #1: Results and Discussion 2

Shortest Formula Challenges • Tags: , , , , ,

Last week I set readers the challenge which can be found here.

Two correct answers received (three if you count Snakehips‘ improvement) from GreasySpot and Bill Szysz, of which Bill’s was the shorter of the two (74 characters compared to 249, excluding the equals sign).

Snakehips then came along and improved this to a mere 70 characters simply by making the reference to the required range relative. (I can imagine Bill is now kicking himself for using absolute references in a shortest-formula challenge!)

Anyway, between the two of them they managed to come up with what is indeed (at least, that I know of) the shortest possible solution to this problem, and that solution is:

More…

Advanced Formula Challenge #7: Results and Discussion 1

Advanced Formula Challenges • Tags: , , , , , , , , , , , , , , , ,

Last week I set readers the challenge which can be found here.

This is a trickier problem than it at first appears, and indeed there are several pitfalls which prevent us from using more “standard” techniques to arrive at a solution.

Perhaps the two main (hidden) obstacles, which were not immediately obvious from the examples I gave, are, firstly, the fact that we are prevented from using a construction involving a SEARCH-approach (e.g. by locating occurrences of each substring of the four types *????*, †????*, *????† and †????†, as John Jairo V attempted), since this of course presumes that there is only one occurrence of each of those substring types within our string, a presumption which cannot be made.

More…

IFERROR techniques for excluding certain values from results 6

Alternative Constructions, Esoteric, Excluding Values • Tags: , , , , ,

We are often in a position where we wish to exclude certain values from an array of results before passing that array to another function.

For example, a common, practical situation is that of finding the minimum value from a range whilst excluding zeroes. This can be done in several ways, for example using an array formula:

=MIN(IF(A1:A10<>0,A1:A10))

or, if we have Excel 2010 or later, using AGGREGATE:

=AGGREGATE(15,6,A1:A10/(A1:A10<>0),1)

More…