Microsoft Excel CSE Functions

Posted by batman on Sep 16th, 2010
2010
Sep 16

Have you ever felt that, while the "SUMIF" and "PRODUCTIF" functions in Excel are great, there's just not enough of them? Wouldn't a "MINIF", or "MAXIF" or even an "AVERAGEIF" have been really nice as well? Or even the ability to have a custom "MYCUSTOMIF"?

Then welcome to CSE functions. CSE is an anagram for Control Shift Enter, and is named such because, unlike just typing in your formula and pressing Enter, you need to type it in, and press Control Shift Enter – or else it just won't work. And you'll need to make a note of this, because you probably won't remember this in a few years time.

OK, so what are CSE functions? By definition CSE functions are functions that operate on arrays (they are also known as array formulae). An array can be one-dimensional (single row or column), or two-dimensional (multiple rows or columns). An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and calculate a column or row of subtotals. You can also place a formula in a single cell and calculate a single amount. An array formula that resides in multiple cells is called (logically enough) a multi-cell formula, and an array formula that resides in a single cell is called a single-cell formula.

CSE functions can be identified by being wrapped with curly braces i.e. "{=MIN(IF($C$2:$C$20=A5,$I$2:$I$20))}", wheras a regular function would be "=SUM(A2:A20)".

I won't attempt to provide a tutorial on CSE's – no point in re-inventing the wheel. There are several really good resources already out there. You can try any of these to get a kick-start:-

 

Powered by Zoundry Raven

Technorati :
Del.icio.us :
Zooomr :
Flickr :

Share and Enjoy:
  • Facebook
  • Twitter
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • LinkedIn
  • MySpace
  • laaik.it
  • Google Buzz
  • Technorati
  • Slashdot
  • Reddit