When making business reports it is often necessary to calculate the sum of the amount as a product of quantity and price which are stored in the columns of the table. For this purpose, we can manually create formulas, and we can also use SUMPRODUCT function, which has a goal to summarize products within the range for given arguments. Let’s say something more about this, not so known, function …
Aggregation of data (summarization, counting, averaging) can be done with the help of functions that have one or more of the conditions under which calculation is performed. These are very useful functions because they allow complex analyzes and an efficient way to make summary reports. In the following text we will describe these functions through several examples …
Data aggregation represents a mathematical operation (summarization, counting, averaging) that we perform over a set of data. Conditional aggregation, as its name implies, is performing data aggregation over a set of data that meets certain condition which is contained within a given data range. In this “recipe” we’ll cover functions that use single condition for performing aggregation.
There are several functions in Excel that literally serve to “find the cell in time and space”. They can be used to identify the row and column in which the cell is located, count the rows or columns, determine the type of value that the cell contains … By themselves, these functions are seldom used. In combination with other functions, in conditional formatting, and especially with data validation, they can be irreplaceable.
Errors are happening and this is inevitability! When we know in advance that there is a high likelihood that they can happen, we can use error detection functions. They can discover whether an error has occurred, to return its code, and to show an alternative value instead of a mistake. This can be, for example, a notification about a predefined text about an error or an empty cell, if we want to ignore the error …