7.5 Geeks' Corner

TABLE OF CONTENTS

• 7.5.1 3D Formulas
• 7.5.2 Better understand array formulas
• 7.5.3 Transpose
• 7.5.4 The subtleties of IF
• 7.5.5 The subtleties of IF, BIS
• 7.5.6 Excel 2021 / Excel 365
• 7.5.7 Web data import
• 7.5.8 Filter
• 7.5.9 XLookup

---

7.5.1 3D Formulas

Sometimes you will use files whose structure is the same from sheet to sheet, and each of them corresponds to a month for example. If you need to perform operations on each of these sheets, for example by summing the different results for each month, you would enter the following formula:

Sheet1!A12 + Sheet2!A12 + etc…

The problem with this approach is that you will need to manually update your formula each time you add a sheet, which will prevent you from automating formulas. 3D formulas can help you get around this problem, for example, if you write the following formula:

=SUM(Sheet1:Sheet4!A12)

The formula will therefore be the sum of all the A12 cells of the various 1 to 4 sheets. But in a much more interesting and creative way, let’s imagine that you call two empty sheets, “start” and “end”. If you drag your sheets into the interval between the “Start” sheet and the “End” sheet, you can write the formula

=SUM(start:end!A12)

And all the A12 cells of the sheets that you drag in this interval will be summed, making it much easier to process repetitively structured data, knowing that you can refer to intervals and not just cells, by writing for example:

=AVERAGE(start:end!A12:A15)

7.5.2 Better understand array formulas

Keep in mind that given the logic of array formulas (so, which apply to an interval), you can use this to your advantage to find an interval of values, and not just a single value, as is the case with the “normal” use of Excel formulas.

For example, suppose you are looking for the mode of a statistical series (for analysis purposes, so you can see which values are the most common in the series, for instance). There may be several, especially if you handle series with a small number of integers. Let’s take the following example: you are looking to find what the most common grade is in student homework in an education program, in this file.

The data is entered in the following interval: B2:B13. If you enter the formula

=MODE.MULT(B2:B13) and type Enter, the following result will appear:

Which essentially amounts to entering the formula =MODE.SINGL and therefore finding only one result, whereas the correct answer is that there are 2 modes (6 and 8).

Similarly, if you select the cell, retype the formula and do Ctrl + Shift + Enter, you will find the exact previous result. On the other hand, if you make sure to select a range of cells and then enter the array formula, you will obtain all the results, i.e., 6 and 8 here as follows:

You can apply this logic to find the 5 largest values of a list, the most common occurrences etc., and thus extract intervals from which you can reference. Do not hesitate to double-check your formula in the solved example.

For example, extract the names of the 5 employees with the most days worked in the last x months, and use the extracted name range to find the number of holidays they have available (referring to another sheet for example) to better manage the rotation of your teams for instance.

7.5.3 Transpose

Sometimes you want to rearrange a table so that you can use the data presented horizontally, vertically in another document (and vice versa).

Rather than performing this operation manually, there is a formula that allows you to do this in Excel: TRANSPOSE. However, two things are important to note re: the use of this formula. First of all, you will need to use it as an array formula (reference section) for it to work.

For example, if we want to transpose the table A1:B5 horizontally from D1, we will write the following formula in D1:

=TRANSPOSE(A1:B5)

Then, simply entering your formula into its cell and typing Ctrl + Shift + Enter will not suffice, as this will give you the following result:

To ensure that all of your data is transposed, you must select a range that is at least as large (or even more if you refer to a table that you will update later) as the reference range. One sees this range has been selected, you can Ctrl + Shift +Enter, and you will obtain the transposition of your selected range, as follows:

This will allow you to link tables, so as to extract part of the data from one sheet into another to perform more in-depth calculations, for example, especially since the two matrices remain linked (if you change values in the initial range, the transposed destination range will change accordingly) with the following display result:

7.5.4 The subtleties of IF

When you write an Excel formula with multiple IFs, it is possible to use True, to limit the number of arguments you enter. To give a concrete example, imagine that in the processing of a KAP survey file (knowledge, attitude, practice) and only 2 types of infrastructures correspond to functional or semi-functional infrastructures, and that all the others correspond to non-functional infrastructures. In this case, instead of writing all the options, you can write:

=IFS(A2=”borehole”;”operational”;A2=”PMH”;”mid-operational”;TRUE;”non-operational”)

This gives you the benefit of not having to write down all the other options in the list (which can be quite numerous) and therefore as long as the condition is true, always proposing the same answer. You can also use this trick with Index/Match, or with any multi-condition formula.

Download this file if you want to check the formula.

7.5.5 The subtleties of IF, BIS

As shown in the following link it is sometimes more interesting to use Boolean logic (use a true/false, schematically) than IF, especially for questions of logic.

By way of example, compare:

=IF(A2=”red”;25;SI (A2=”white”;101,SI(A2=”blue”;74;0))

And

=(A2=”red”)*25 + (A2=”white”)*101 + (A2=”blue”)*74

In any case, keep in mind that Boolean logic is often very powerful in Excel, to start with because it allows operations to be performed faster than with formulas, but also because it allows to find bypass logic that is as elegant as it is interesting (c.f. the Index/Match example in array formulas for an example of applied Boolean logic.)

7.5.6 Excel 2021 / Excel 365

As indicated in the transpose section, many features have been added and thus change the way Excel is used.

In the foreground, it is the change in functions that required input via Ctrl + Shift +Enter such as TRANSPOSE, MODE.MULT etc.: you no longer need to select the destination area of your formula and entry with a matrix, Excel will do this for you automatically.

7.5.7 Web data import

If you frequently use data from a website, you can import the data directly into Excel.

To do this, as shown in the screenshot below, go to Data => New Query => From other sources => From the web.

The path may also be: Data => Get External Data => From other sources => From the web.

You will be able to select the data you want to download by indicating the web address you are interested in, https://en.wikipedia.org/wiki/World_population for example.

And you can then select the table that you want to download here by clicking on it, such as an estimate of the population distribution in 2020.

And your data will automatically be loaded as a table:

Warning: If the table is not necessarily usable from the start (if, for example, you have several subcategories in the table, the import will be corrupt), you will need to use the “Transform Data” button that will take you to Power Query.

For example, as you can see in the case of the Wikipedia page on world population, the table Past population will not really be usable adding text to the column on the population figure, so you will need to transform the data.

You can refer to the following resources for basic PowerQuery usage if you want to transform your data.

The following video provides an introduction:

Along with this one:

7.5.8 Filter

In the post 2019 and Office365 versions of Excel, you can now replace the use of VLOOKUP and HLOOKUP with XLOOKUP.

This formula, the syntax of which you can find on the official Microsoft website, allows you to filter a table using one or more criteria and to make an extract of this directly returned as a table.

This is particularly useful for obtaining data dynamically (in the same way but more complex than Transpose) since the data added to your initial table will also be filtered by the function as soon as they are summed.

7.5.9 XLookup

In the post 2019 and Office365 versions of Excel, you can now replace the use of VLOOKUP and HLOOKUP with XLOOKUP.

This formula, the syntax of which you can find on the official Microsoft website allows you to search for a match in an interval, and frees you from the limits of VLOOKUP and HLOOKUP (search to the right of the formula etc.)! It also has additional search parameters, and allows you to return not a value, but one or several references, allowing you to create more dynamic formulas.

If you would like to learn more about this formula, do not hesitate to watch this video:

And open this link.

It is particularly powerful and useful (since it allows you to respond to the uses of V/HLOOKUP and index/match. If you have it in your version of Excel, do not hesitate to use it!