In this exercise, we deal with the data of the rooms of a multi-storey building. We begin with an already formatted, organized table. Download
Note: in the downloadable file entire columns have been named, so in newer Excel versions (e.g. 365) when referring to a value in the current row, an "@" sign must be inserted (e.g. =@Area). The dynamic operation mode is only available in newer versions (365 / 2021).
Important introduced functions: SUMIFS() | COUNTIFS() | UNIQUE() | FILTER() | TRANSPOSE() | XLOOKUP().
Text manipulation
1.A. Sort the rooms by floor, and by door number!
The Floor and the Door numbers can be taken from the ID column (Type/Floor-Door), but in order to use them for sorting they have to be in separate columns.
- Insert three new columns: select e.g. columns A:C, then right-click and Insert.
- Enter the field names in the header line: Type, Floor, Door!
- The Type is the leftmost character:
=LEFT(@ID) - The Floor is described by two characters starting from the 3rd position:
=MID(@ID;3;2)+0 - The Door is described by the two rightmost characters:
=RIGHT(@ID;2)+0 - Select the cell containing the three formulas and double-click on the handle in the lower right corner of the selection in order to autofill the other lines.
- After that you can use e.g. the Sort command in the Data ribbon to sort the rows.
The results of the formulas are text strings, so if you want to treat them as numbers, you have to convert them (you may get an error message if the characters cannot be interpreted as numbers). An easy way to convert them is to add a zero — but it can be done using a function also.
- The VALUE() function converts a string that can be interpreted as a number into a number.
- Its opposite is the TEXT() function, which converts a numeric value to text, according to a specified format code.
- Therefore, the ID column can be reassembled by combining the previous three columns:
=@Type&"/"&TEXT(@Floor;"00")&"-"&@TEXT(@Door;"00").
Sums by categories
Aggregator functions usually have a version that takes into account only elements that meet certain conditions. Obviously, you often have to create the list of categories first.
2.A. Count the rooms on each Floor!
For the solution below, you first need to create a list of Floors — let's arrange it horizontally!
• Categories
- Enter 0 into a cell (AB3)!
- Enter 1 into the next (AC3)!
- Select the two cells and autofill the cells up to 7 by dragging the handle in the lower right corner!
The COUNTIFS() function returns the number of elements that meet all specified criteria.
The function has two types of parameters (in the case of N criteria, it will have 2N arguments):
– criteria_rangeN: the range whose elements the criterion applies to
– criteriaN: the condition that the elements of the previous range must meet.
- Inequalities must be entered between quotation marks (e.g. ">2", "<=1", "<>0").
- Textual criteria can also be entered in quotation marks and wildcard characters are allowed (? or *).
- Criteria can be placed in cells (no quotation marks needed).
The solution below assumes that the Floor column exists.
• Summation
- In the cell above floor 0 (AB1), enter the formula:
=COUNTIFS(Floor; AB3)
– AB3 is a relative reference to the first element of the floor list,
– the Floor reference must be absolute — but this is automatically true if you refer to a named range. - Copy the formula above the other numbers — e.g. drag the handle in the lower right corner of the cell to the right.
2.B. Calculate the sum of the area of each room category on each floor!
Due to the previous task, the list of levels is already available, so it is enough to create the list of the room categories.
• Categories
- Copy the Category column to a separate area (column AA).
- With all cells selected, select the Remove Duplicates command on Data ribbon!
- Check that My data has headers option is checked in the window that appears, then click OK.
- The unique list of room categories will be created.
Unfortunately, this list is not updated, so if new categories are added to the table, they will not automatically appear here.
The SUMIFS() function returns the sum of a selected property of the elements that meet all specified criteria.
The function has three types of parameters (in the case of N criteria, it will have 1+2N arguments):
– sum_range: the range of cells to sum
– criteria_rangeN: a range of the same size, whose elements are tested using the next criteria
– criteriaN: the criteria that desides which cells in the previous range will be added
• Summation
- In the upper left cell of the range of the areas (AB4), enter the formula:
=SUMIFS(Area; Floor; AB$3; Category; $AA4))
– AB$3 is a mixed reference for the first element of the list of floors,
– AB$3 is a mixed reference for the first element of the list of floors,
– $AA4 is a mixed reference for the first element of the list of categories,
– Area, Floor and Category references must be absolute — but this is automatically true if you refer to a named range. - Copy the formula into all cells (AB4:AI16).
2.C. Modify the previous solution to dynamically track the changes!
Dynamic functions only work in newer versions of the program, and since their results typically fill several cells, you have to make sure that there is enough empty space under the cells containing the formula, otherwise only the #SPILL! error message is displayed.
The UNIQUE() function returns a list of unique values in a range — e.g. all category codes.
The FILTER() function can be used to filter ranges based on a given criteria — leaving out the unnecessary elements.
The TRANSPOSE() function rotates the parameter range by 90°.
The SORT() function can be used to sort elements in alphabetical order.
• Defining a table
The unique list can be created more simply if only the range of possible values is given as a parameter (the reference to the entire column obviously includes the header and the empty rows as well) — it is therefore appropriate to define the data table as a table.
- Make sure the active cell is in the area of the table — the table will automatically extend to the first empty row or column.
- Define the table using the Table command in the Insert ribbon menu (Ctrl+R).
- In the Table Design ribbon menu, name the table (RoomList) and select its style!
The header cells of the table also act as references, therefore they cannot be empty or have the same content — in case of a gap or conflict, the program completes the header. For references within the table, it is sufficient to use the column label (=[@Area]*[@Height]), outside the table, the table name is also required (=SUM(RoomList[Area])).
• Categories
- Replace the list of categories with the following dynamic formula (AA4):
=SORT(UNIQUE(RoomList[Category]))
– the Category is a reference to the corresponding column of the RoomList table. - Define (Ctrl+F3) the list as _CatCodes (=$AA$4#)!
- Replace the list of floors with the following dynamic formula (AB3):
=TRANSPOSE(SORT(UNIQUE(RoomList[Floor])))
– the Floor is a reference to the corresponding column of the RoomList table. - Define (Ctrl+F3) the list as _Floors (=$AB$3#)!
• Summation
- Modify the formula in the upper left cell (AB4) by adding a "#" (which indicates that the formula applies to all cells of the dynamic content), so the formula automatically fills the range:
=SUMIFS(Area; Floor; AB$3#; Category; $AA4#), or with names:
=SUMIFS(Area; Floor; _Floors; Category; _CatCodes). - Define (Ctrl+F3) the list as _Areas (=$AB$4#).
Sum up the areas (=SUM(AB4#)) and compare it with the total area (=SUM(Area))!
Of course, you don't always need a category list — for example, if you're only interested in a single item.
Classification
3.A. Calculate the useful area!
All rooms must be classified as Useful or Other. This can also be decided using the IF() function, however, it is more practical with a table.
| CatCodes | Usefulness |
|---|---|
| Aux1 | Other |
| Aux2 | Other |
| Fund1 | Useful |
| Fund2 | Useful |
| Fund3 | Useful |
| Inst1 | Useful |
| Inst2 | Useful |
| Inst3 | Useful |
| Inst4 | Useful |
| Mech1 | Other |
| Other | Other |
| Spec | Useful |
| Training | Useful |
• Categories
Create a table, where the first column contains the list of CatCodes, the next shows their classification (Usefulness)!
The XLOOKUP() function has three obligatory parameters:
- lookup_value: the value to search for – now the Category of the current room
- lookup_array: the range to search – now the first column of the search table
- return_array: the range to return – now the second column of the search table
- [if_not_found]: return text when there is no valid match
- [match_mode]:
0 🡢 exact match (or #NA error message),
-1 🡢 exact match, or return the next smaller item,
1 🡢 exact match, or return the next larger item,
2 🡢 wildcard match using *, ?, ~ characters - [search_mode]:
1 🡢 perform a search starting at the first item (default),
-1 🡢 perform a reverse search starting at the last item,
2 🡢 perform a binary search — the lookup_array must be sorted in ascending order,
-2 🡢 perform a binary search — the lookup_array must be sorted in descending order.
• Classification
- On the right side of the Roomlist table create a new column named Useful?!
- Classify the rooms:
=XLOOKUP(@Category; CatCodes; Usefulness), or as a table:
=XLOOKUP([@Category]; CatCodes; Usefulness)!
• Summation
- Sum up the useful area based on the new Useful? column:
=SUMIFS(RoomList[Area]; RoomList[Useful?]; "Useful")!
Let's assume that on average of one rod per m² is necessary for the false ceiling. The rods are available in the following lengths: 10 cm, 25 cm, 50 cm, 1 m, 2 m. If the distance is less than 5 cm, the false ceiling is attached directly to the ceiling. Obviously, the shortest possible rods should be used.
3.B. Add up how many of each suspension rod is necessary!
Since each type of rod can be used in a size range, no exact match required. An important question is where the limit of the interval should belong — since a distance of 1 m obviously requires a 1 m rod, in this case the match_mode should be 1.
| Rod Length | Rod Type |
|---|---|
| 99,00 | Too_Big |
| 2,00 | Rod_200 |
| 1,00 | Rod_100 |
| 0,50 | Rod_050 |
| 0,25 | Rod_025 |
| 0,10 | Rod_010 |
| 0,05 | No_Rod_ |
• Categories
- Create or copy the lookup table in a separate area!
- Define the lookup_value column as R_length!
- Define the return_array column as R_type!
• Categorization
- On the right side of the Roomlist table create a new column named as Rod!
- Calculate the difference between Height and Height_Susp in each room:
=MAX(@Height-@Height_Susp;0)
– if a negative value would appear, the rod length would be zero. - The suspension lengths have to be classified according to the specified ranges. To do this, complete the previous formula using the calculated length as lookup_value:
=XLOOKUP(MAX(@Height-@Height_Susp;0);R_length;R_type;"?!";1).
• Summation
- On the right side of the lookup table create a new column which counts the number of pieces of each rod type (R_type):
=ROUNDUP(SUMIFS(Area; Rod; @R_type);0).
Two-dimensional lookup table
4.A. Create a search that summerizes the area belonging to each room category on a given floor!
The XLOOKUP function can also be used for two-dimensional searches because it can be nested within itself. This can be demonstrated with the help of the table in point 2.C.
- The header row is _Floors (=AB3#), the header column is _CatCodes (=AA4#), and the 2D data table is _Areas (=AB4#).
- You can get a horizontal array showing the area values in the row of the Aux1 value:
=XLOOKUP("Aux1"; CatCodes; _Areas) - You can get a vertical array showing the area values in the column of the 4th floor:
=XLOOKUP(4; _Floors; _Areas) - Or you can combine the two previous solutions, where
– the internal search selects a one-dimensional range (either a single row or a column) of the entire table,
– so the external search can select a single element from this range:
=XLOOKUP(4; _Floors; XLOOKUP("Aux1"; _CatCodes; _Areas)),or
=XLOOKUP("Aux1"; _CatCodes; XLOOKUP(4; _Floors; _Areas)).
It is more elegant to use references instead of specific values (4, Aux1).
Pivot Table
5.A. Create a Pivot Area showing the areas by floors and by categories! Display data as a percentage of the floor areas!
The report creates a two-dimensional table, so it can be summarized according to two independent aspects at the same time (the categories can be nested).
- Make sure that the active cell is in the RoomList table!
- In the Insert ribbon, select the Pivot Table button!
- The source of the Pivot Table should be Table/Range, and it should be on Existing worksheet (e.g. at AA30).
- The frame of the Pivot Table is created, which must be filled using the elements in the PivotTable Fields list:
– the Floor should be in the Columns section,
– the Category should be in the Rows section,
– the Area should be in the Values section,
which produces a very similar table to the one created in the previous task.
5.B. Present the data of the previous table in the percentage of the floor areas!
- Right-click to the Area row, and select the Value field settings option, – in the Show values as section select the % of Column Total option, – click on the Number Format button, and set the number format (e.g. 0.0%; -0.0%; -_%)!