Excel has a large number of conversion functions. This text will show the ones used to convert lowercase letters into uppercase, uppercase to lowercase, and capitalize the initial letters of each word in the given cell. We will also mention two features that are used to clean the text, either by removing excessive spaces or unprintable characters that often exist in old applications and end up in the table when retreiving data.
When someone tells you, “I’m left without text” this usually means a state of shock and confusion. Ignoring text related functions will not leave you in shock, but you will certainly be deprived of some useful tools. These functions are numerous, and in the “recipe” that follows I’ll describe these that allow merging of two or more texts into one, converting text to number or number in text, determining the length of text …
In practice, it often happens that we have tables that are in a mutual relationship. Similar to the database, the value of a table column column may be a key by which we can access the columns of another table. In this text, we will discuss the functions used to search through data ranges. If you master them, it’s quite certain that this will quickly become one of your favorite Excel tools.
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.
Testing of the conditions is done so that, depending on its fulfillment, we will get different results. These can be some concrete values, but most often, depending on the fulfillment of conditions, we devise different formulas for calculating. It is possible to create multiple conditions (multi-branching) or as a condition to specify an index that determines which expression will be calculated. The following text will detail the logical functions for testing the conditions.