Yesterday we started the 30XL30D challenge with the action-packed, fun-filled, EXACT function. It had several examples and ways to apply it in your own workbooks.
AREAS Function
Today we’ll investigate the AREAS function, and it’s a bit lighter in the usefulness department (to put it politely). It’s like the lazy brother-in-law, in a bad sitcom, who lies on your couch, and drinks your beer.
But, even a layabout can have a purpose. That lazy brother-in-law can serve as an example for your children, of how NOT to behave. As for the AREAS function, we can use it to see how the 3 different reference operators work, and how they affect the formula results.
So, let’s take a look at the AREAS information and examples, and if you have other examples, please share them in the comments. But don’t send me your brother-in-law!
Function 02: AREAS
The AREAS function returns the number of areas in a reference — an area is a range of contiguous cells or a single cell. The cells can be empty, or contain data – that has no effect on the count.
How Could You Use AREAS?
The AREAS function doesn’t have many real-world uses, but it’s an interesting example of how the reference operators work. You can use the AREAS function to do the following:
- Count the number of areas in a range
- Count the number of intersections for multiple ranges
- Calculate an area number for an INDEX function
AREAS Syntax
The AREAS function has the following syntax:
- AREAS(reference)
- reference can be a single cell or range, or can refer to multiple areas.
Reference Operators
When entering references, you can use any of the 3 reference operators:
: | colon | A1:B4 | Range | all cells between, and including, the two references |
, | comma | A1, B2 | Union | combine multiple references into one |
space | A1 B3 | Intersection | cells common to the references |
AREAS Traps
If you are using a comma in the AREAS function, to refer to multiple ranges or cells, add another set of parentheses.
=AREAS((F2,G2:H2))
Otherwise, the comma will be interpreted as a field separator, and you’ll get a “too many arguments” error.
Example 1: Count the Areas in a Range
You can use the AREAS function with a simple range reference, and the count will be 1.
=AREAS(G2:H2)
Example 2: Count the Areas in Multiple References
You can use the AREAS function with multiple references, to get a total count of areas. Because a comma is used as the union operator, you’ll need to add an extra set of parentheses in the formula.
=AREAS((F2,G2:H2))
The two ranges in the reference are adjacent, but are counted as separate areas, so the formula result is 2.
Example 3: Count the Areas in Overlapping References
Even if the references overlap, or one reference is completely within another, when using the comma as the union operator, each area will be counted separately.
=AREAS((F2,F2:H2))
The two references overlap, and F2 is completely within the F2:H2 range, but they are counted as separate areas, so the formula result is 2.
Example 4: Count the Areas in Intersecting References
When you use the space character to create an intersection from the references, the intersection areas will be counted.
=AREAS(TESTREF01 TESTREF02)
The named range TESTREF01 is coloured blue and TESTREF02 is coloured purple. These ranges intersect at three points, outlined with bold borders, so the formula result is 3.
Example 5: Calculate the Area Number for INDEX
The INDEX function, in Reference form, can use area number as its final argument.
INDEX(reference,row_num,column_num,area_num)
This example, based on an Excel newsgroup post by Leo Heuser, refers to a non-contiguous named range – TestBlock.
In the INDEX formula, TestBlock is the reference, and the AREAS function calculates the number of areas in the TestBlock range.
To get the value from TestBlock, row 5, column 1, last area, use this formula:
=INDEX(TestBlock,5,1,AREAS(TestBlock))
The last area is Day04, and the 5th value in Day04 is H05, which is the formula result.
Download the AREAS Function File
To see the formulas used in today’s demo, you can download the AREAS function sample workbook. The file is zipped, and is in Excel 2007 file format.
Try to use each function in your own workbooks. Then, for extra brain-sticking power, teach a friend or co-worker how to use each function. When you explain it to someone else, you’ll remember it better.
Watch the AREAS Video
To see a demonstration of the examples in the AREAS function sample workbook, you can watch this Excel video tutorial.
_____________
Hi Debra,
Never paid any attention to the Areas function in Excel, just in VBA.
I will now, it is a very informative writeup.
Better check the couch however, I think that brother-in-law may have gotten to the keyboard.
Shouldn’t…
“The named range TESTREF01 is coloured blue and TESTREF01 is coloured purple.
These ranges intersect at three points, outlined with bold borders, so the formula result is 4.”
Actually be…
The named range TESTREF01 is coloured blue and TESTREF02 *** is coloured purple.
These ranges intersect at three points, outlined with bold borders, so the formula result is 3 ***.
Thanks Jim, it’s fixed now — and I’ll have to keep a closer eye on that guy!
I don’t think I knew about this one. I knew of the Areas property of the Range object in VBA, and that’s very useful. I can’t think of a use for this one off the top of my head.
@Jon, thanks — glad I’m not the only one who can’t see a good use for this function.
Still need to change the second TESTREF01 to TESTREF02 (…is coloured purple.)
Thanks Ruby, it’s fixed now.
[…] AREAS to find the last area in a named […]
[…] AREAS […]
Hi Debra!
I’m busy updating and upgrading my Functions Lists for 2007 – 2010 and adding a help / examples button for at least the most common functions.
AREAS? Has anyone ever found a use for it? I think your guy on the couch drinking your beer is related to Chip Pearson’s drunken uncle DATEDIF although at least he’s useful (if a tad ynreliable).
Regards.
Hi Debra,
I have experience something with the INDEX function that baffles me, and I hope you can shed me some light. FYI I am using Excel 2007.
My question is, I have data in a table and row 1 is the header. I am using the first syntax i.e. index(array,row_num). When I use index(H2:H10,0), the formula will return the value in H2 but if I change my array to H3:H10 I will get #VALUE! which I suppose should be the correct answer.
Regards
@Jeffrey, try using 1 as the row number in the INDEX function, instead of zero. That should get rid of the error.
Hi Debra,
Thanks for your response. Actually the 0 was derived from a sumproduct formula for searching multiple criteria (now it came back to me that I could also use match function for searching with multiple criteria, thanks to this blog) and it will return 0 if the criteria was not met. I’m okay with the error because that should be the correct answer if row number is 0 but what I don’t understand and sort of confusing is why my index formula return a value if my array/range starts at row 2 just below the header row.
Index(H2:H10,0) would not return an error but Index(H3:H10,0) would, why the inconsistency?
Regards
Hi Jeffrey. This is due to the magical world of array formulas. This is tricky to explain, but I’ll give it a shot.
http://office.microsoft.com/en-nz/excel-help/index-HP005209138.aspx tells us “If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press CTRL+SHIFT+ENTER.”
What this DOESN’T tell you is that while such an INDEX function returns an array , for the non Array-Entered version, what actually gets displayed depends on where abouts in the spreadsheet you’ve entered this function.
1. If the formula happens to be entered in a row/column that is parallel to the range that INDEX points at, then the INDEX function will return an entire array, but will display only the value of the corresponding row/column in the indexed range.
2. Otherwise it returns a #VALUE error, which basically means “Hey, I’ve got a whole list of numbers/text, but I’ve only got one cell to display them in, and I don’t know which one in particular that you want me to display”.
Normally a function that tries to returns a whole list into just one cell shows a #VALUE error, unless it has some clue in regards to which particular value you want to display from such a list. But if you click on a cell that has this error, and push F9 (which tells Excel to ‘evaluate’ the list) then you’ll see that in fact it’s not really an error value at all, but instead is a whole bunch of numbers/text.
From your question, I believe that your formula just happens to be in row 2, and that you have NOT array-entered the function i.e. you entered the formula into the cell, and pushed ENTER (which is fine for your purposes).
As long as your formula is Index(H2:H10,0) then your formula will return a value…in particular it will return the value from H2. But as soon as you change that formula to Index(H3::H10,0), then you’ll get the #VALUE warning.
Here’s an example to help illustrate this.
Say cell A1 has the text “Header” in it, and you have the values 2;3;4;5 entered into cells A2;A3;A4;A5. If you type =INDEX($A$2:$A$5,0) into cell B1 and copy it down to B6, then in cells B1 and B6 you will see a #VALUE error, because rows 1 and 6 DON’T have corresponding rows in the range that you INDEX function points to. However, cells B2 through B5 it will return the corresponding value from cells A2 through A5.
What’s also interesting is that if you select cells B1 through B6 in turn, and highlight the entire formula in the formula bar and then press F9, you’ll see that they all return exactly the same thing:
={1;2;3;4;5}
…even though they all display different things.
John Walkenbach covers Array Formulas in his books very well, and you’ll find lots of resources on the web.
Hi Jeff,
Thanks very much for your explanation and the example, appreciate it. I didn’t know that the location where I enter the INDEX formula could also affect the output.
Regards
No sweat. Glad I could help. Sorry about all the italics in the above comment…I stuffed up the formatting.