Quirky Functions

Equation plots in Excel

You’ve probably all seen the Batman plot that can be generated by entering a formula into google Search. Their instant graphing capability turns this complex formula into a scatter-plot of 4 series.   Copy this formula into Google Search  …

Execute SQL from Excel

What can you learn here? Library of ADODB connections Populate from databases Use SQL on closed workbooks     A library to populate Excel tables with SQL query results get it now Using SQL to populate Excel ta…

Respect a Filter with Sumproduct

Quite often Sumproduct  is used in cases where a Pivot table just doesn’t cut it. Here’s how to make Sumproduct respect filters in a data table.   Pick a column in your data table   Lets use the ID column, and make a name ran…

Search

Search()  and Find()  are pretty much the same. The only difference is that Find  worries about matching case (upper/lower) and search  doesn’t. That means that Brass is the same as brass as far as Search  is concerned, but find  thinks they are differ…

SumProduct

This is my favorite function – you can do so much with it. What I like about most though, it is that it seems to have been added to excel as a kind of afterthought – its behavior is weird compared to everything else. It acts as an array function wit…

SumProduct – Prioritizing lookups

As I’ve said before you can use Sumproduct()  for many things that it probably wasnt designed for. Various array functions could be used to do the same thing, but trying to do it all in sumproduct is more interesting Prioritizing Lookups.   Lets say…

Sumproduct Strangeness-2

This time we are going to use SumProduct  and  Search  together to check for near matches in data. Search() and Sumproduct()   Combining Search()  and Sumproduct()  will give us the option of matching partial strings. This opens up a whole new set of po…

Using match to categorize

A common Excel task is to categorize values into bands. You can use Frequency or some pivot table capabilities to report on that, but lets say that you want to add a categorization characteristic to individual rows but want to avoid complex IF’s and array…