UPPER F
The upper function converts a text string to all uppercase
Try It:
LOWER F
The lower function converts a text string to all lowercase
Try It:
PROPER F
The proper function converts a text string so the first letter in each word is capitalized
Try It:
CONCATENATE F
The concatenate function allows you to combine text that appears in different cells
John
Smith
Try It:
IF can put one things a cel .
T IF f has parts: 1) L Test,
2) V If T , 3) Value If F
IF can p 1 2 in a
Tina's Sales For Month
$6,500.00
Sales Needed to Receive Bonus
$6,000.00
Bonus Amount
$200.00
Return the text "No Bonus" if the
bonus was not met.
Tina's Bonus =
P 1 2 N in a c
Units Sold Hurdle
1000
Bonus IF TRUE
$375.00
Bonus IF FALSE
$45.00
John's Units Sold
995
Bonus Amount
P a fo or a number a cell
Your Sales
$5,000.00
Sales Needed for a Bonus
$4,000.00
Commission, based on actual
sales 5.00%
Your Bonus
P 1 2 F in a c
Sales1
$5.00
Sales2
$15.00
The SUM =
Select Function From Dropdown
=> SUM
When you will have two possible outcomes in a single
cell, use an IF/THEN format when thinking about the
scenario.
Your logical test must include three parts - two pieces
of information that you are comparing and a
comparison operator
Comparison Operators - you must include one of these:
>
<
=
>=
<=
<>
T 1
T
Student 1
87
Student 2
92
Student 3
74
Student 4
96
Student 5
82
Student 6
66
I
$1,100
Rent
350
Groceries
150
Car Payment
200
Electric Bill
50
Gas (auto)
100
Internet
40
Cell Phone
75
Dining Out
50
Miscellaneous
150
T Expense
D
B
A Exp
We can also complete this formula without directly knowing the balance
information by including those formulas in our IF function.
This makes for a simpler spreadsheet, but does make for a
more involved formula.
Here is how that could look:
I
$1,100
Rent
350
Groceries
150
Car Payment
200
Electric Bill
50
Gas (auto)
100
Internet
40
Cell Phone
75
Dining Out
50
Miscellaneous
150
B
Over budget by $65
A Exp
Scenario 1:
After reviewing the scores on Test 1, you would like
to recommend tutoring to students who earned less than a 75.
- Students needing tutoring should have the label
"Tutoring Recommended" added to column C.
- Students who have earned at least a 75 should have a blank
cell appear in column C.
Scenario 2:
While creating a personal budget to keep track of your
expenses, you have decided to use a cell to highlight whether you
are over or within your budget.
In B26, calculate the Total Expenses.
In B27, calculate the difference between the Income and Total Expenses
- This will be -$65
In B29, create a formual that will return the actual expense total
for balances that are at least zero.
For months where you have gone over budget, your spreadsheet will read
Over budget by & the amount you have gone over budget.
A correct formula will yield the result:
Over budget by $65
We can also complete this formula without directly knowing the balance
After reviewing the scores on Test 1, you would like
to recommend tutoring to students who earned less than a 75.
- Students needing tutoring should have the label
"Tutoring Recommended" added to column C.
- Students who have earned at least a 75 should have a blank
While creating a personal budget to keep track of your
expenses, you have decided to use a cell to highlight whether you
In B27, calculate the difference between the Income and Total Expenses
In B29, create a formual that will return the actual expense total
For months where you have gone over budget, your spreadsheet will read
Over budget by & the amount you have gone over budget.
Annual Interest Rate
6.50%
Monthly Contributions
Interest Payment per Year
12
Years to Contribute
Number of Years
30
Avg Annual Interest Rate
Loan Amount
150,000
Value of Investment
Monthly Loan Payment
How much was invested?
Annual Interest Rate
6.50%
Monthly Payments
Interest Payment per Year
12
Annual Interest Rate
Number of Years
30
Current Loan Value
Loan Amount
150,000
How many years to pay off?
Monthly Loan Payment
Annual Interest Rate
6.50%
Interest Payment per Year
12
Number of Years
30
Loan Amount
150,000
Monthly Loan Payment
PMT F Using Named Ranges
N Ranges - Create Selection
PMT F
FV F
NPER F n
(100.00)
$
20
5%
(100.00)
$
3%
1,000.00
$
FV Functio
NPER Functi
1
0
F
0.65
D
0.75
C
0.85
B
0.95
A
S
G
0.75
2
Product 1
20.00$
Product 2
25.00$
Product 3
15.00$
Product 4
15.00$
Product 5
16.00$
P
P
Product 2
3
P
P
D
Boom01
$15.00
Flying Range is 10
Boom02
$30.00
Flying Range is 20
Boom03
$40.00
Flying Range is 50
Boom04
$45.00
Flying Range is 60
Boom05
$65.00
Flying Range is 70
Boom06
$69.00
Flying Range is 80
Boom07
$100.00
Flying Range is 85
Boom08
$110.00
Flying Range is 110
Boom09
$165.00
Flying Range is 160
P
P
D
Boom07
4
D Late
0
30
60
% L Fee
1%
2%
3%
D Late
B
L Charge
E 1: Deliver v to cell. Find approximate value
column 2 of table.
E 2: Deliver to cell. Find exact value from
2 of loo table.
E 3: Deliver v to cell. Find value from column 2 & 3. U COLUMN
(tells you what column you are in).
E 4: Use HLOOKUP to a value to a form .
89
$500.00
14
$789.00
2
$255.00
0
$593.00
53
$1,040.00
75
$651.00
90
5%
