Monthly Archives: December 2014

Advanced Formula Challenge #10: Prime Factorisation 23

Advanced Formula Challenges

The challenge this week is as follows: given a value in A2, where A2 is an integer and 2<=A2<=100, a formula to be entered into B2 such that, if the value in A2 is a prime number, return "Prime"; otherwise, return a string representing the prime factorisation of that number, using the full notation as given in the below examples (where a lower-case "x" is used to denote multiplication).

Prime Factorisation

For those not sure of any results, I have given a full list in the attached here.

Solution next week. Best of luck!

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…