10.9. Using common functions (SUM, AVERAGE, etc.): Conditional formatting using functions

10.9 Using Common Functions and Conditional Formatting in Google Sheets

Electronic spreadsheets are powerful tools for data organization, financial analysis, project management, among other applications. Google Sheets, an integral part of Google Workspace, offers a range of features that make these tasks easier, including common functions such as SUM, AVERAGE, among others, and advanced features such as conditional formatting. Let's explore how these tools can be used to improve the analysis and presentation of your data.

Common Functions in Google Sheets

Functions are predefined formulas that perform calculations using specific values, called arguments, in a particular order. Some of the most common functions you'll find in Google Sheets include:

• SUM: The SUM function adds all the numbers in a range of cells. For example, =SUM(A1:A10) will sum all numbers in cell A1 through A10.
• AVERAGE: The AVERAGE function calculates the arithmetic mean of the numbers in a range. For example, =AVERAGE(B1:B10) will calculate the average of the numbers B1 to B10.
• NUMCOUNT: This function counts how many cells in a range contain numbers. For example, =NUMCOUNT(C1:C10) will return the number of cells that contain numbers between C1 and C10.
• MAX: MAX returns the largest number in a set of values. For example, =MAX(D1:D10) will give the largest number in the range D1 to D10.
• MIN: MIN returns the smallest number in a set of values. For example, =MIN(E1:E10) will give the smallest number in the range E1 to E10.

These functions can be combined and used within other functions to perform more complex calculations.

Conditional Formatting Using Functions

Conditional formatting is a feature that allows you to change the appearance of a cell based on specific conditions. In Google Sheets, you can use conditional formatting with functions to highlight important information, identify trends, or simply make your data more readable.

For example, you may want to highlight all cells that have a value above the average. Suppose you have a list of monthly sales in a range of A1:A12 and you want to highlight the months in which sales were higher than the annual average. You can do this in the following way:

1. Select the range A1:A12.
2. Go to Format > Conditional Formatting.
3. In the dialog box that appears on the right, under "Format cells if", choose "Custom formula is".
4. Enter the formula =A1>AVERAGE($A$1:$A$12). This will check whether the value of each cell in the range is greater than the average of the entire range.
5. Choose the desired formatting, such as a fill color or text style, and click "Done."

With this, all cells with values ​​above the average will be automatically highlighted. Note that we use absolute references (with the dollar sign) to ensure that the average is calculated over the entire range, regardless of the cell being formatted.

Another practical example of conditional formatting is highlighting cells that contain errors. If you are using a function that could result in an error (such as division by zero), you can use the ISERRORS function to identify these cells. For example:

1. Suppose you have formulas in column B that occasionally result in errors.
2. Select column B.
3. Go to Format > Conditional Formatting.
4. In "Format cells if", choose "Custom formula is" and enter =ISERRORS(B1).
5. Choose a formatting, such as a red background color, and click "Done."

Cells containing errors will be highlighted, allowing you to quickly identify and correct problems.

Conditional formatting can be applied using a variety of functions and criteria, making it an extremely versatile tool for data visualization. You can highlight past dates, duplicate values, or even create data bars that visually represent the magnitude of values ​​in a range.

In summary, the combination of common functions and conditional formatting in Google Sheets can transform a simple collection of data into an informative and visually appealing dashboard. By mastering these tools, you will be able to analyze and present your data more effectively, facilitating informed decision-making.