Series overlap

When analyzing sales, we will often come across data series that would be convenient to display using a Stacked Column or Stacked Bar chart, but due to their nature this is not a good solution. For example, if we have a production cost and a sales price, we need to calculate the price difference in order to show the production price and the price difference as two complementary series on the chart; their sum is, it is easy to see, the sales price. Can we create such chart without additional calculation?

Filtering by date

The FILTER function can also contain multiple criteria, and if we want to consider them all at the same time, they must be in parentheses, separated by an asterisk. This is a convenient way to filter sales results by date, and set the criterion by changing the year and month values ​​from the drop-down list we have created by using Data Validation option.

Multiple aggregation criteria

A very rugged title, isn’t it? When you use multi-conditional data aggeregation functions like SUMIFS, AVERAGEIFS, COUNTIFS, etc. there may be multiple values ​​from one column that you want to consider. This is accomplished by listing these values ​​in curly brackets and then summing all the results. Here’s a quick guide to how to use multiple values ​​for a one criterion.

Dynamic categories

One of the earlier “recipes” mentioned categories and subcategories. If you remember, we wanted to make it possible to automatically create a list with a selection of subcategories by selecting a category from the list generated with Data Validation. We could have done this by applying a few tricks, but the list, in addition to the necessary values, contained “empty slots”. This can be easily avoided today with the help of dynamic arrays.

XMATCH function

The XMATCH function is a more modern version of the MATCH function, and is used to search arrays, by row or column, to find the position of a given value. It supports accurate and approximate comparisons, and wildcard characters can be used as a substitute for one (?) or more characters (*) during search. This is another of the new dynamic array features that is available exclusively to Office 365 subscribers as of February this year.