Formulas and Functions in Spreadsheets

Formulas And Functions

Arithmetic Operations

• + for addition
• - for subtraction
• * for multiplication
• / for division
• % for modulus (remainder)

Basic Functions

• SUM(range) - calculates the sum of a range of cells
• AVERAGE(range) - calculates the average of a range of cells
• COUNT(range) - counts the number of cells in a range that contain numbers
• MAX(range) - returns the maximum value in a range of cells
• MIN(range) - returns the minimum value in a range of cells

Text Functions

• LEN(text) - returns the length of a text string
• LOWER(text) - converts a text string to lowercase
• UPPER(text) - converts a text string to uppercase
• PROPER(text) - converts a text string to proper case (first letter capitalized)

Logical Functions

• IF(logical_test, [value_if_true], [value_if_false]) - tests a condition and returns one value if true and another value if false
• AND(logical1, [logical2], ...) - returns true if all conditions are true
• OR(logical1, [logical2], ...) - returns true if any condition is true

Charts And Graphs

Types of Charts

• Column Chart: used to compare categorical data across different groups
• Bar Chart: used to compare categorical data across different groups (horizontal)
• Line Chart: used to show trends over time or other continuous data
• Pie Chart: used to show how different categories contribute to a whole
• Scatter Chart: used to show the relationship between two continuous variables

Creating a Chart

• Select the data range
• Go to the "Insert" tab
• Click on the chart type (e.g. Column, Line, etc.)
• Customize the chart as needed (e.g. title, axis labels, legend)

Chart Elements

• Title: the title of the chart
• Axis Labels: the labels on the x and y axes
• Legend: the key that explains the different series in the chart
• Data Series: the individual data points or groups in the chart

Formulas and Functions

Arithmetic Operations

• + is used for addition
• - is used for subtraction
• * is used for multiplication
• / is used for division
• % is used for modulus (remainder)

Basic Functions

• SUM(range) calculates the sum of a range of cells
• AVERAGE(range) calculates the average of a range of cells
• COUNT(range) counts the number of cells in a range that contain numbers
• MAX(range) returns the maximum value in a range of cells
• MIN(range) returns the minimum value in a range of cells

Text Functions

• LEN(text) returns the length of a text string
• LOWER(text) converts a text string to lowercase
• UPPER(text) converts a text string to uppercase
• PROPER(text) converts a text string to proper case (first letter capitalized)

Logical Functions

• IF(logical_test, [value_if_true], [value_if_false]) tests a condition and returns one value if true and another value if false
• AND(logical1, [logical2],...) returns true if all conditions are true
• OR(logical1, [logical2],...) returns true if any condition is true

Charts and Graphs

Types of Charts

• Column Chart is used to compare categorical data across different groups
• Bar Chart is used to compare categorical data across different groups (horizontal)
• Line Chart is used to show trends over time or other continuous data
• Pie Chart is used to show how different categories contribute to a whole
• Scatter Chart is used to show the relationship between two continuous variables

Creating a Chart

• Select the data range
• Go to the "Insert" tab
• Click on the chart type (e.g. Column, Line, etc.)
• Customize the chart as needed (e.g. title, axis labels, legend)

Chart Elements

• Title is the title of the chart
• Axis Labels are the labels on the x and y axes
• Legend is the key that explains the different series in the chart
• Data Series are the individual data points or groups in the chart