Areas()

Check out the following: http://www.xlfdic.com/ ... you can download a file which will show you examples of how each function is used.

Areas is more commonly associated with VBA if you use code that generates a range consisting itself of non-contiguous ranges (say a SpecialCells routine against a parent range) ... you can iterate through the areas of resulting range. The areas function itself will simply tell you how many areas are included in a given range... so if you have a named range called rangex consisting of A1:A2, A5:B5, C1:D10 -- AREAS(rangex) would return 3.

I can't think myself off hand as to when you would want/need to use it as a native function.... I'm sure someone else can though.

I have found a use for AREAS....

Whilst doing some investigation on INDEX I came across a potential albeit tenuous use for AREAS....

Say you had the following:

A1:A3: 1 to 3
B1:B3: A to C

D1:D3: 4 to 6
E1:E3: D to F

G1:G3: 7 to 9
H1:H3: G to I

Now let's assume you have a named range:

Name: test
RefersTo: =Sheet1!$A$1:$B$3,Sheet1!$D$1:$E$3,Sheet1!$G$1:$H$3

If you were updating your named range continuously and you had an INDEX to return the value from say the 2nd row & 2nd column of the last area in the named range you could use:

=INDEX(test,2,2,AREAS(test))

This way you'd never have to update the INDEX - it would adjust automatically.

I did say it was tenuous...

(And yes I know there are other better ways to achieve the same result but it was meant to try and show there is a possible use for the AREAS function)