Excel supports a special type of formulas that allows us to use arrays of variables as arguments rather than variables (references, ranges, names), where we can perform certain mathematical operations over all of them. Moreover, there is a situation where the arrays considerably shorten the way to the solution or are even necessary to get to it. In the following text, through a few examples, it will be shown how to use array formulas.
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.
Pivot charts have almost all properties as standard charts that are used to visualize reporting data. They are specific in that they are always in relation to the Pivot table on the basis of which they were created or by some other data source (Data Model). Changing data in the Pivot table, e.g. by adding a new report dimension, the Pivot chart is automatically updated. You can find out more about Pivot Charts in the text that follows …