Tag Archives: Vector

Redimensioning 24

Redimensioning • Tags: , , , , , , , , , , , , , , , ,

Readers who have read some of my earlier posts will be familiar with the concept of “redimensioning” an array.

This is an extremely useful and important technique, which, in its basic form, allows us to take a two-dimensional array and convert it into one of just a single dimension, whilst of course retaining the elements within that array.

Such an approach is necessary if we wish to further manipulate the entries of some two-dimensional array. For example, we might be in a position in which, for whatever reason, we need to pass each of the entries in a two-dimensional array to an array of one or more parameters for further processing. However, since the evaluation of the resulting multi-dimensional “matrix” is not within Excel’s capabilities, we are obliged to first transform the original array to one of a single dimension.

More…

Advanced Formula Challenge #11: Results and Discussion 11

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

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

Good results for this one: six answers received, six correct answers received – from Oscar, Daniel, diondan1, Bill, Ikkeman and Calvin. Plus one (unverifiable, though no doubt correct!) Google Sheets solution from Isai, as usual. 🙂

So congratulations to all of the above!

The majority of those solutions adopted a strategy of comparing the characters from two sets of arrays derived using MID over an array of start_num parameters, though a couple of solvers (Bill and Calvin) decided to first derive the ASCII codes for these characters and instead use these as the basis for the comparison.

More…

Unique, Ordered List of Most Frequent Numbers in a Two-Dimensional Range 9

Alternative Constructions, Extracting Data, Manipulating Arrays • Tags: , , , , , , , , , , , , , , , , , , , ,

I recently received a request from James, who was interested in a formula-based solution to the following problem: given a two-dimensional range containing a mixture of numbers and empty cells (which I am defining as being either “genuinely” empty or as containing the null string “” as a result of formulas in those cells), generate a unique list of those numbers in order of their frequency within that range, with the most frequent first. What’s more, if two or more numbers occur the same number of times within that range, then they should be listed in order of their size from smallest to largest.

For example, for the dataset in A1:F6 below, we would return the list as given beginning in I1.

Unique, Ordered List of Most Frequent Numbers in a Two-Dimensional Range

More…

Non-Array TRANSPOSE 4

Alternative Constructions, Manipulating Arrays • Tags: , , , , , , , , , , ,

We sometimes look for non-array (i.e. non-CSE) versions of constructions which would normally require array-entry. Our reasons for doing so may be varied:

1) We may feel that it improves spreadsheet performance (sometimes true, sometimes not)

2) We perhaps have a dislike for having to use the required keystroke combination necessary for committing array formulas

3) We may simply be interested from a theoretical point of view

More…

List of unique entries from column of space-separated strings 6

Alternative Constructions, Extracting Data, Integer Generation, Manipulating Arrays • Tags: , , , , , , , , , , , , , , , , , , , , ,

Given the list below in A1:A10, we may wish to create a list of unique, single words from that list, as per column B here.

Unique From Space Separated

We can do this with the following set-up: More…

COUNTIFS: Multiple “OR” criteria for one or two criteria_Ranges 111

COUNTIFS, Manipulating Arrays, SUMIFS • Tags: , , , , , , , , , , , , ,

In this post I would like to clear up what appears to me to be a rather widespread misunderstanding of how COUNTIFS/SUMIFS operate, in particular when we pass arrays consisting of more than one element as the Criteria to one or even two of the Criteria_Ranges.

This latter technique is used when the criteria in question are to be considered as “OR” criteria, which is not to be confused with cases where we wish the criteria passed to be calculated rather as “AND” criteria.

For example, given the following data:

More…

Advanced Formula Challenge #2: Results and Discussion 3

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

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

Three solutions were offered, two of which from the same person, and both of which were correct! So many congratulations to Bill on successfully solving what was quite a complex challenge!

Indeed, as Ben Schwartz pointed out, this challenge appears to have been set previously on the internet, and seems to have been only partially solved on those occasions. In any case, thanks also to Ben for his suggestion, which he confesses was cobbled together from those previous solutions he found, and which worked in all but a few exceptional cases.

More…