Excel Array Formulas

I just found a feature of Excel that I’ve never heard of before: Array Formulas!

Essentially, you can use ranges in criteria of functions such as COUNTIF. Full details available: http://www.cpearson.com/excel/ArrayFormulas.aspx

I used it to find the number of distinct values in a column (http://www.cpearson.com/excel/Duplicates.aspx).

All you need in the formula is:

{=SUM(1/COUNTIF(B2:B11,B2:B11))}

The curly braces around the formula make this an array formula.

I reckon I could do some pretty fancy processing using this in the future.. Not sure what though.

 

Advertisements
  1. Good find, I hate Excel and try and avoid it at all times, but I’ll keep this in mind. Cheers.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: