Central formulas
- Jul. 18, 2019
 | Example files with this article: |
Introduction
In Excel we have formulas in a great variety, I don't need to tell that to you. But did you already see formulas like the ones I show in the attached file ?
The formulas =result and =Total_year are used like that in the file. Are these functions that are known in Excel and you guys have missed a couple of new functions ? Not at all, I added them myself with the defined names (go to Formulas > Name Manager, or Ctrl + F3):
given that the cursor is located in cell C5, January, then the result is equal to =C3 - C4. It is important that we use relative cells: actually the formula is saying: take the cell in the same column, 2 rows up, and subtract the cell in the same column, 1 row up.
If you were to insert a row inbetween rows 3 and 4, then you will break the result formula... So be careful ! However, I see also great advantages of this approach, if use wisely. Like this you can put a long a difficult formule only once in the defined names, and when done you can suffice in a lot of cells with just the name of the formula. Very useful !
The same approach holds for the formula for the yearly total (=Totaal_jaar): add up the values that you can find in the 12 columns to the left of the active cell, in the same row. Again, if you were to insert quarter 1/2/3/4, that's possible but you have to change the relative cell references in the formula to incorporate the correct 12 monthly cells. Just for the recorde, you can name the formules just the way you want, it does not necessarily need to be equal to the row or column that you want to compute.
Finally, can you explain why, in the formula for the yearly total, we see that column XEU is used ? Indeed, the cursor is in column C and if we need to go 12 columns to the left, Excel will continue from the far right-hand side at the very last column ! Did you know that ? ;-)
