6.3 A few useful tips
TABLE OF CONTENTS
- 6.3.1 The =ABS function
- 6.3.2 The =IFERROR function
- 6.3.3 Formatting merged cells
- 6.3.4 The Today formula
- 6.3.5. The “Classic” time formulas
This category includes small tricks that do not necessarily deserve to be highlighted, but which are very useful (for example: ABS and IFERROR formulas).
This function returns the absolute value of a value. Simply insert in your cell the equal sign followed by the name of the function and the number on which it will apply. This number can be a cell that contains it or the number itself. Examples include the following:
|=ABS(9)||Absolute value of 9||9|
|=ABS(-9)||Absolute value of -9||9|
|=ABS(A9)||Absolute value of -5 (value of cell A9)||5|
It is used to check if there is an error in the first argument of the formula. The function returns the result of the formula if there is no error, or the IFERROR value if a formula generates an error.
The syntax of the IFERROR is:
IFERROR(value;value if error)
Where value and value if error are the values entered by hand or included in the cells referred to.
You probably know that using merged cells is generally not recommended when building an Excel file. Why? because this usually induces referencing errors, duplication problems etc., etc…
But if you want to preserve the aesthetic appearance of merged cells without the disadvantage of merged cells, there is a relatively simple trick. In the following example, we want to imitate the merging of the 3 cells A1 – B1 and C1. To do this, we select the three cells and click on the small arrow at the bottom right of the Alignment box, in the Home section.
And in the window that appears, you will then be able to select in the horizontal alignment of the text, the option “Centre Across Selection”.
You will then get cells of merged appearance, but whose content is entirely limited to cell A1 (so beware of possible subsequent formatting conflicts).
One of the first things to know about temporality in Excel is that you can subtract dates (as long as they are well formatted), and thus get an interval (that will by default be returned to you in days, which you can then convert to months/weeks if necessary.) This is especially useful when you need to know how much time is left to complete an activity in a tracking file for example.
And you can make this approach more complex by using other formulas, such as the TODAY formula. As its name suggests, it returns today’s date in the indicated cell. This is not, in itself (apart from cosmetic applications), of great interest, but combined with other functions, or for calculations, it can be particularly useful.
For example, imagine that you are steering a project and that you would like to have an average indication of the money that will need to be spent per month (and thus adjust your consumption if you have, for instance, fixed expenses that can be modulated).
You can use this attachment as an example.
You can set an end date in a reference cell (in our example, cell B1) and, by combining this reference with the amount remaining on the project and the Today formula, you can calculate a monthly (or even daily if applicable) average, as follows:
30 is the number of days per month, F11 is the total remaining budget and B1 is the end date. This way you get the remaining time on the monthly scale (which you can change to a weekly scale if need for end-of-project details for example) as shown in this file, once you have set unit costs and the number of units.
N.B.: if you need to quickly enter today’s date, you can use the CONTROL + shortcut.
From a date, you can also extract the month of that date, the year, and the week, with the formulas =MONTH(), =YEAR() and =WEEKNUM(). At first glance this may seem unnecessary, but it is important - for instance to add a filter on an expense tracking.
Let us suppose that you have an expense tracking file with specific dates for each input/output. You want to add a PivotTable (see section) to compile data and analyse your expenses.
If you do not add a Month column (and Year in the same way), it will not be possible to view the expenses incurred over a period of time, whether it is a month, several months etc. For this, you must add a column with the MONTH function for example, and then you can add a filter in your PivotTable to create your filter, or add a time dimension to a PivotChart, as follows:
To then obtain this graph:
If necessary, do not hesitate to look over the attached file as an example.
N.B.: You can also use NETWORKDAYS, or COUPDAYS if you need to build an HR or accounting file (and, even if it’s a bit outside the scope of this toolbox, you have a significant number of financial tools in Excel, which you can easily find in the Formulas tab , under the Financial section).