|
|
FUNCTIONS:
Learn useful Functions such as:
-
Average
(average of a range of
cells)
-
Max
(highest number in a
range cells)
-
Min
(lowest number in a
range of cells)
-
Sum
(sum or total of a range
of cells)
-
Count
(count cells containing
a number)
-
CountA
(count cells containing
text or a number)
-
CountIf
(count only those cells
meeting a single
criteria - a word, a
number, etc.)
-
SumIf
(sum or total only those
cells meeting a single
criteria - a word, a
number, etc.)
-
SumProduct (count or sum
cells meeting multiple
criteria - words,
numbers, etc.)
-
If (check
to see if cells meet a
certain criteria and
assign a result - we
also cover AND OR conditions)
-
HLookup
& VLookup (type
something in a cell and
have Excel look up data
in a table)
-
Pmt (calculate payment
and interest rates)
-
Concatenate & Trim
(combine data from
multiple fields into
one)
-
Date (calculate
days/months/years
between two different
dates)
-
Learn how to "nest" one function
within another
-
Use proper use of
Commas, Colons,
Parentheses, Exclamation
Points, Brackets, etc.
Example:
=SUM([jan_sales.xls!A1:D1],[feb_sales.xls!A1:D1])
|
GOAL SEEK, SOLVER, SCENARIOS,
"WHAT-IF" TABLES:
Goal Seek allows you to
calculate an unknown value in a
given formula, but is only useful
for problems that involve finding a
single variable. When the desired
result of a calculated cell is
known, but not the input value that
calculation needs to reach that
result, you can use Goal Seek.
Solver is a tool that helps
you find solutions involving
multiple variables.
Scenarios are part of a group
of commands that can be called
what-if analysis tools. A scenario
is a set of values that a user can
create and save and then substitute
at any time in the worksheet. The
user can then switch to any of these
new scenarios to view different
results in the worksheet. To compare
several scenarios, you can create a
report that summarizes them on the
same page.
What-If Tables allow you to
analyze data and produce a table to
show the results. When a single
variable like the unit price
changes, use a one-input What-If
Table. If the unit price AND number
of units sold changes, use the
two-input What-If Table.
MACROS:
Create Absolute and/or Relative
Macros to automate common,
repetitive tasks.
PASSWORD PROTECT ALL OR PART OF
WORKBOOK:
Password Protect an entire workbook
(file) or individual cells within a
worksheet so they cannot be opened
and/or edited.
(858) 578-9476
Info@PC-Classes.com
 |
