Sure!

I’ll go over basic arithmetic, data manipulation, conditional logic, and text
formatting formulas in spreadsheets, providing detailed explanations and examples.
Though I can't directly create diagrams here, I’ll explain in a way that you can easily
follow along in your spreadsheet application like Excel or Google Sheets.

---

1. Basic Arithmetic Formulas

Basic arithmetic formulas perform simple mathematical calculations.

a) Addition

Formula: =A1 + B1

Example: If cell A1 contains 10 and cell B1 contains 15, =A1 + B1 will return 25.

b) Subtraction

Formula: =A1 - B1

Example: If cell A1 contains 20 and B1 contains 8, =A1 - B1 will return 12.

c) Multiplication

Formula: =A1 * B1

Example: If A1 contains 5 and B1 contains 4, =A1 * B1 will return 20.

d) Division

Formula: =A1 / B1

Example: If A1 contains 20 and B1 contains 5, =A1 / B1 will return 4.

e) Sum Function

Formula: =SUM(A1:A5)

Example: If A1 to A5 contains the values 1, 2, 3, 4, 5, =SUM(A1:A5) will return 15

(the sum of all values).

---
2. Data Manipulation Formulas

These formulas help to work with and manipulate data in different ways.

a) Concatenation (Joining Text)

Formula: =CONCAT(A1, " ", B1)

Example: If A1 contains John and B1 contains Doe, =CONCAT(A1, " ", B1) will
return John Doe. Here, " " adds a space between the two strings.

b) VLOOKUP

Formula: =VLOOKUP(lookup_value, table_range, column_number, [exact_match])

Example: Suppose you have a table of products and their prices:

A B
Product Price
Apple 2
Banana 1
Orange 3

=VLOOKUP("Banana", A1:B3, 2, FALSE) will return 1, the price of the banana.

VLOOKUP looks for "Banana" in the first column and returns the value from the
second column.

c) INDEX and MATCH

Formula: =INDEX(range, MATCH(lookup_value, lookup_range, [match_type]))

Example: Same product table as above, but we use INDEX and MATCH together to
achieve the same result as VLOOKUP. =INDEX(B1:B3, MATCH("Banana", A1:A3,
0)) will return 1 (Banana’s price).

---

3. Conditional Logic

Conditional logic evaluates a condition and returns different results depending on

whether the condition is true or false.

a) IF Function

Formula: =IF(condition, value_if_true, value_if_false)

Example: =IF(A1 > 10, "High", "Low") If A1 is greater than 10, it will return High.
Otherwise, it will return Low.

b) Nested IF

Formula: =IF(A1 > 90, "A", IF(A1 > 80, "B", "C"))

Example: If A1 is 95, the formula will return A. If it’s 85, it will return B, and
anything else will return C.

c) AND/OR Functions

Formula (AND): =IF(AND(A1 > 10, B1 > 10), "Both are High", "One or Both are
Low")

Example: If A1 and B1 are both greater than 10, it will return Both are High,
otherwise One or Both are Low.

Formula (OR): =IF(OR(A1 > 10, B1 > 10), "At least one is High", "Both are Low")

Example: If either A1 or B1 is greater than 10, it will return At least one is High.

---

4. Text Formatting Formulas

Text formatting formulas allow you to manipulate and adjust text.

a) UPPER / LOWER / PROPER

Formula: =UPPER(A1)

Example: If A1 contains john, =UPPER(A1) will return JOHN.

Formula: =LOWER(A1)

Example: If A1 contains JOHN, =LOWER(A1) will return john.

Formula: =PROPER(A1)

Example: If A1 contains john doe, =PROPER(A1) will return John Doe.

b) LEFT / RIGHT / MID

Formula (LEFT): =LEFT(A1, 4)

Example: If A1 contains HelloWorld, =LEFT(A1, 4) will return Hell (the first 4

characters).

Formula (RIGHT): =RIGHT(A1, 5)

Example: If A1 contains HelloWorld, =RIGHT(A1, 5) will return World.

Formula (MID): =MID(A1, start_position, num_characters)

Example: If A1 contains HelloWorld, =MID(A1, 3, 4) will return lloW (starting from

the 3rd character, 4 characters long).

c) LEN

Formula: =LEN(A1)

Example: If A1 contains Hello, =LEN(A1) will return 5 (the number of characters in

the cell).

d) TRIM

Formula: =TRIM(A1)

Example: If A1 contains Hello (with spaces), =TRIM(A1) will return Hello (removes
extra spaces).

---

Summary Table:

---

You can practice each of these formulas in your spreadsheet to see them in action!
Would you like more details on any specific area or examples with actual data?

Great! Let’s dive deeper into specific examples with actual data. I’ll use examples to
illustrate the formulas, so you can directly copy them into your spreadsheet and see
how they work.

---

1. Basic Arithmetic Examples with Actual Data

Let’s assume we have the following data in a spreadsheet:

a) Addition: Total Quantity and Price

Formula: =A2 + A3 + A4 + A5

Example: To calculate the total quantity of all items, input =A2 + A3 + A4 + A5. This
will return 53.

b) Multiplication: Total Revenue

Formula: =A2 * B2

Example: For each item, you can calculate the revenue (Quantity × Price) by using
=A2 * B2. The result will be 50 for the first row (10 × 5).

c) Using SUM to Add All Quantities

Formula: =SUM(A2:A5)

Example: To sum all quantities from A2 to A5, use =SUM(A2:A5) which will return
53.

---

2. Data Manipulation with Actual Data

Suppose we have the following data in a spreadsheet:

a) Concatenation: Combine First and Last Names

Formula: =CONCAT(A2, " ", B2)

Example: Use =CONCAT(A2, " ", B2) to combine the first and last names in row 2.
This will return John Smith.

b) VLOOKUP: Looking Up Prices

Let’s say you have this data in a table:

Formula: =VLOOKUP("Banana", A2:B4, 2, FALSE)

Example: This formula searches for Banana in the table and returns its corresponding
price from column B. The result will be 1.
c) INDEX and MATCH: Alternative to VLOOKUP

Let’s continue with the same product data.

Formula: =INDEX(B2:B4, MATCH("Orange", A2:A4, 0))

Example: This will return 3, the price of Orange. The MATCH function finds the
position of "Orange" in column A, and INDEX uses that position to get the
corresponding price from column B.

---

3. Conditional Logic with Actual Data

Let’s assume we have this data in a spreadsheet:

a) IF Function: Pass/Fail

Formula: =IF(A2 >= 80, "Pass", "Fail")

Example: This formula checks whether the score in A2 is greater than or equal to 80.
If true, it returns "Pass"; otherwise, "Fail". For a score of 92, the result will be Pass.

b) Nested IF: Letter Grades

Formula: =IF(A2 >= 90, "A", IF(A2 >= 80, "B", IF(A2 >= 70, "C", "D")))

Example: For a score of 92, it will return A. For a score of 85, it will return B, and so
on.

c) AND/OR Functions: Complex Conditions

Formula (AND): =IF(AND(A2 >= 80, B2 = "Pass"), "Good", "Needs Improvement")

Example: This formula checks if the score is 80 or higher and the result is "Pass". If
both are true, it returns "Good"; otherwise, "Needs Improvement".

---

4. Text Formatting with Actual Data

Let’s assume we have the following text data:

a) UPPER/LOWER/PROPER Functions

UPPER: =UPPER(A2) converts the text in A2 to uppercase.

Example: If A2 contains john, the formula will return JOHN.

LOWER: =LOWER(A2) converts text to lowercase.

Example: If A2 contains JANE, the formula will return jane.

PROPER: =PROPER(A2) converts text to proper case (first letter capitalized).

Example: If A2 contains MaRY sMITh, it will return Mary Smith.

b) LEFT/RIGHT/MID Functions

Suppose we have the following data:

LEFT: =LEFT(A2, 5) returns the first 5 characters of the text in A2.

Example: If A2 contains HelloWorld, the result will be Hello.

RIGHT: =RIGHT(A2, 5) returns the last 5 characters of the text in A2.

Example: If A2 contains HelloWorld, the result will be World.

MID: =MID(A2, 3, 4) returns 4 characters starting from the 3rd character of the text
in A2.

Example: If A2 contains HelloWorld, the result will be lloW.

---

5. Diagrams and Flow

You can visualize the flow of formulas with the following diagram examples in your
head or on paper:

1. VLOOKUP and INDEX/MATCH:

Imagine a table of data where VLOOKUP goes vertically down the first column to
find a match, and then pulls information from another column.

INDEX/MATCH works in two steps: first, MATCH finds the position of the item,
and then INDEX retrieves the corresponding value.

2. IF and Nested IF:

Think of a branching flowchart:

If Condition A is met, go one way, otherwise, go another.

If you use Nested IFs, you continue adding branches for each possible outcome (e.g.,
grades A, B, C).

3. AND/OR Logic:

Picture a gate that only opens if both (AND) conditions are met, or if one or more
(OR) conditions are met.

---

Practical Usage

To practice:

1. Set up a sheet with the example data.

2. Apply formulas step-by-step to see the results.

3. Experiment with changing the data to see how the formulas adapt.

For instance, try changing the prices or quantities in the multiplication formulas, or
adjust the scores for the IF functions to test different outcomes.

---
Let me know if you would like more examples or further clarification on any of these
formulas!