# mmult function

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 )

# 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