MMULT function is incredibly powerful and yet mysterious to most. Let’s explore this complex function (this post inspired by a formula challenge from contextures.com )
mmult function
Post Category → Efficiency
Sum Cells in Overlapping Ranges
This is a cruel Excel challenge (I’ve used it!). Why? Because there is a brilliantly simple solution to summing cells that overlap two ranges. Continue reading
Dynamic Ranges Using Index Function
Robert H. Gascon’s comment from my last post convinced me that I should be using the INDEX function for dynamic ranges not the volatile OFFSET function. Continue reading
False Friends in Excel
The term False Friends is used in various fields including linguistics and chess. In this post I’ll explain how it applies to Microsoft Excel! Continue reading
Concatenate Values to Create a Key
Excel users who don’t know this concept waste hours manually looking through their data. A filter helps but creating a valid lookup key is what Mr Carlson needs! Continue reading
Paste Formula Into Excel’s Name Box
Do you ever hold down the Ctrl key to select multiple non adjacent cells or ranges? I’ve found a way to select multiple non adjacent ranges (any sheet) by pasting a formula into Excel’s Name Box! Continue reading
The Einstellung Effect in Excel?
How did a Scientific American article help me create a much simpler solution in Excel? And what connection does it have to the Einstellung Effect? Read on and I’ll tell you 🙂 Continue reading
Chandoo’s Sequence Number Puzzle
I saw this sequencing challenge from Chandoo “Generate a new sequence number when value changes“. His formula solution is great but I kept thinking “What if we have 20000 rows?“ Continue reading
An off switch! What an excellent idea!
Some Excel models have so many complex formulas that they just can’t calculate any more. These models often need to be re-created. Some models can be saved by making formulas more efficient. One technique is to add an OFF SWITCH to heavy formulas. Continue reading
What is the easiest way to calculate grades in Excel?
Although the VLOOKUP versus INDEX/MATCH debate gets most of the attention there are MANY more topics to debate in Excel. When calculating student grades what method is best: NESTED IF or LOOKUP/BINNING TABLE? Continue reading