Quirky Functions

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  ...

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...

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 n...

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...

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...

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. ...

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 p...

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...