Excel Functions and Formulas

Functions: These are pre-built operations that perform specific calculations. Examples include SUM, AVERAGE, and ROUND.
References: These refer to cells or ranges of cells within the spreadsheet. They help you use existing data in your formulas. For example, A1:A10 refers to the cells from A1 to A10.
Constants: These are fixed values in a formula. They can be numbers (like 10) or text (eg. "Hello").
Operators: These are symbols used to perform calculations or compare values. Examples include + (addition), - (subtraction), * (multiplication), / (division), = (equals), and > (greater than).

SUM(A1:A10) is a common example. This formula uses the SUM function to add up all the values in the range of cells from A1 to A10.

SUM: Adds up a range of numbers.
COUNT: Counts the number of cells in a range that contain numbers.
COUNTA: Counts the number of cells in a range that contain any data, including text or numbers.
SUMIF: Adds up values in a range that meet a certain criterion.
AVERAGE: Calculates the average value of a range of numbers.
ROUND: Rounds a number to a specific number of decimal places.
PRODUCT: Multiplies a range of numbers together.
ROMAN: Converts a number to its Roman numeral equivalent.

Purpose: SUMIF lets you add up values in a list that meet a specific condition.
Syntax:
- =SUMIF(range, criteria, [sum_range])
- range: The cells containing the values you want to check for the condition.
- criteria: The condition you want to use to filter the data.
- [sum_range] (optional): The cells you want to add up if the condition is met.
- Example: =SUMIF(A1:A20, 10) will add up all values in the range A1:A20 that are equal to 10.

This example demonstrates how to use SUMIF to sum the values in alternate rows of a spreadsheet.
Goal: You want to add up the values in column "Amount" for rows where the value in column "Condition" is 1. This is equivalent to summing the value in every other row.
Formulas:
- =SUMIF(E15:E21,1,D15:D21): This formula adds the amounts in column D, but only for rows where the corresponding value in column E is 1.
- =SUMIF(E15:E21,0,D15:D21): This formula adds the amounts in column D, but only for rows where the corresponding value in column E is 0.

Purpose: SUMIFS lets you add up values based on multiple conditions.
Example 1: To find the total quantity of "HP Laser Jet" printers in the "Police" department, you can use the following formula:
- =SUMIFS(C2:C14, A2:A14, "HP Laser Jet", B2:B14, "Police")
Explanation:
- C2:C14: This range represents the quantities of each printer.
- A2:A14: This range represents the printer models.
- B2:B14: This range represents the departments.
- "HP Laser Jet": This is the first condition (for printer model).
- "Police": This is the second condition (for department)

Purpose: This function rounds a number to a specified number of decimal places.
Syntax: =ROUND(number, num_digits)
Example: =ROUND(12.416667, 2) (This would round to 12.42)
Other Rounding Functions:
- ROUNDDOWN: Rounds a number down to the specified number of decimal places.
- ROUNDUP: Rounds a number up to the specified number of decimal places.

Example: This text shows how rounding can be used in a budget. The table shows the different tax rates and their impact on the amount of money raised. The text refers to using the "Rate no round" column for calculation purposes and "Rate with round" for formatting.
Key Point: This example highlights the importance of rounding for clarity and presentation, especially when dealing with large numbers.

Purpose: These functions convert numbers between Roman numerals and Arabic numbers.
Syntax:
- ROMAN(number): Converts a number to its Roman numeral equivalent.
- ARABIC(text): Converts Roman numerals to Arabic numbers.
Example:
- =ROMAN(65): Returns LXV (the Roman numeral for 65).
- =ARABIC(LXV): Returns 65.

Purpose: These formulas allow you to evaluate conditions and perform different actions based on those conditions. They are used for decision-making within your spreadsheet.
IF: This function checks a condition and returns one value if TRUE and another value if FALSE.
AND: This function evaluates multiple conditions and returns TRUE if all conditions are TRUE.
OR: This function evaluates multiple conditions and returns TRUE if at least one condition is TRUE.
NOT: This function reverses the result of a logical expression. If the original expression was TRUE, it returns FALSE; if the expression was FALSE, it returns TRUE.
CHOOSE: This function returns a value from a list based on a specified index number.
IFERROR: This function returns a specified value if a formula results in an error.
ISTEXT: This function checks to see if a cell contains text and returns TRUE if it does.

Purpose: The IF function lets you make decisions within your spreadsheet based on conditions.
Syntax: =IF(logical_test, value_if_true, value_if_false)
- logical_test: The condition you want to test.
- value_if_true: The value to return if the condition is TRUE.
- value_if_false: The value to return if the condition is FALSE.
Example: =IF(25 > 10, "Greater", "Less"): This formula would return "Greater" because the condition 25 > 10 is TRUE.