Excel Vjezbalje
Excel Vjezbalje
Excel Vjezbalje
Topics
1
3
Seattle, WA, E W T M Accounting Excel Seminar
4
5
Seattle, WA, E W T M Accounting Excel Seminar
6
9
Seattle, WA, E W T M Accounting Excel Seminar
Topics
Keyboards
Pivot Tables
Seattle, WA, E W T M Accounting Excel Seminar
Recorded Macros For Reports
Charts
Alt keyboards
Decimal
Date
Time
Percentage
Slaying Excel Dragon: 1) Pages 119 - 340 in book and 2) videos #11 - 36 on DVD and 3) Video #1 on Ctrl + Shift + Enter DVD.
Excel's Golden Rule: If a formula input can vary, put it in a cell and refer to it in the formula with a cell reference.
Tax (inefficient formula): illustrate formula input that can should NOT hard coded into formula.
Net Income formula: illustrate formula with built-in function within a larger formula.
COUNTIF & Label formula: illustrate counting with criteria and how the join symbol is used with criteria.
4 Lookup Formulas
LOOKUP DVD
Slaying Excel Dragon: 1) Pages 374 - 419 in book and 2) videos #39 - 43 on DVD.
Report Layout
Style Formatting
Number Formatting
Pivoting
Filtering a Row
Clear Filter
Grouping Integers
Grouping Decimals
Pivot Chart
Running Totals
% of Column Total
% of Row Total
Difference From
% Difference From
Creating Second PivotTable from Second Cache of data using Excel 2003 keyboard shortcut for 3-step Wizard.
How to trick the Macro Recorder into seeing a variable height report using Relative References.
Rearrange records from vertical orientation to proper table using Relative References.
7 Charts
Slaying Excel Dragon: 1) Pages 465 - 506 in book and 2) video #49 on DVD.
Chart Types
Column Chart
Chart keyboards
Keyboard: Ctrl + 1
Copy Charts
X Y Scatter
https://www.youtube.com/user/excelisfun
Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun: $20
ivotTable: Alt, N, V, T
ivotTable 2003: Alt, D, P
age Setup: Alt, P, S, P
Number Formatting is a Faade. What you see is not always what is in the cell.
Numbers Numbers
25 25
50 50
10 10
15 15
Total 99 100
Today's Date:
Today's Date: Applying General Number Formatting wipes away all N
General Number Formatting Keyboard: Ctrl + Shift + ~
Number Under
Date Number Formatting
12/31/1899 1
1/1/1900 2
9/4/2013 41521
Formula: End Date - Start Date Formula: End Date - Start Date +1
Time is a Serial Number between 0 and 1 that represents the proportion of one 24 hour day.
Where 8/24 = 1/3, 12/24 = 0.5
Number Under
Time Number
Time Formatting
8:00 AM 0.3333333333
9:30 AM 0.3958333333
12:00 PM 0.5
5:00 PM 0.7083333333
Time In 8:00 AM
Time Out 11:30 AM
Time Billed Wage 25
Gross 0
Formula: (End Time - Start Time)*24
t Date +1
2 Formula Elements.
1 Equal sign (starts all formulas).
2 Cell references (also defined names, sheet references, workbook references, table nome
3 Math operators.
4 Numbers (if the number will not change; for example, 12 months, 24 hours).
5 Functions (IF, MATCH, INDEX, COUNTIF, and so on).
6 Function argument elements (such as a 0 in the third argument of MATCH to tell functio
7 Comparative operators.
8 Join operator: Ampersand (join symbol: &).
9 Text within quotation marks (for example, "For the Month Ended").
10 Array constants (for example, {1,2,3}).
3 Math Operators.
+ Adding.
- Subtracting or Negation.
* Multiplying.
/ Dividing.
^ Raising to an exponent.
() Parentheses.
4 Comparative Operators.
= Equal: are two things equal?
<> Not: are two things not equal? Type less than symbol, then greater than symbol.
> Greater than: is the thing on the left greater than the thing on the right?
>= Greater than or equal to: is the thing on the left greater than or equal to the thing on th
< Less than: is the thing on the left less than the thing on the right?
<= Less than or equal to: is the thing on the left less than or equal to the thing on the right
5 Types of Formulas.
1 Calculating Formulas that calculate a number answer (like adding)
2 Logical Formulas that give you a logical value, either TRUE or FALSE (like formulas that s
3 Text Formulas that deliver a word to a cell or create labels for reports (like a label for a lo
4 Lookup Formulas that look up an item in a table (like looking up a price for a product).
5 Array Formulas are advanced formulas that act on arrays (ranges) instead of individual c
Array formulas can deliver results that are calculating, logical, text, or lookup.
Note:
* Because the number $10,500.00 can change, we put it in a cell and make formulas that r
* Because the number 12 will not change, we type it directly into the formula.
Ex 2 Type of Formula:
Calculating.
Formula Elements:
Equal sign, cell reference, math operator, number.
Amount $25,256.00
Tax (inefficient formula) Formula you do not want to c
Label AND put variable formula input into cell (Golden Rule)
Tax Rate 0.125
Tax (inefficient formula) Efficient formula: =D90*D94
Revenue $60,000
Administrative Expense $18,360
Operational Expenses $32,088
Other Expenses $312
Net Income
Ex 5 Type of Formula:
Logical.
Formula Elements:
Equal sign, cell reference, comparative operators (equal sign).
DR CR
322.00 322.00
172.01 172.01
144.10 144.10
638.11 638.11
Note:
* Formulas calculate upon the underlying numbers.
* Number Formatting (Decreasing Decimals) does not remove the "unseen" decimals.
* In this way, formulas can better analyze a spreadsheet than a person can be just "eyeing
Ex 6 Type of Formula:
Text.
Formula Elements:
Equal sign, cell reference, ampersand, text in quotes.
First Last First & Last
Bill Jelen
Joe Smith
Sioux Franks
Bob Chin
Luong Girvin
Ex 7 Type of Formula:
Calculating.
Formula Elements:
Equal sign, COUNTIF function, range of cells, cell reference.
Hurdle >350
# of Sales Greater Than 350
Sales
$312.00
$295.00
$294.99
$675.00
$452.00
$195.00
Ex 8 Type of Formula:
Calculating.
Formula Elements:
Equal sign, COUNTIF function, range of cells, comparative operator in quotes, ampersand,
Hurdle 475
Err:501
Sales
$312.00
$295.00
$294.99
$675.00
$452.00
$195.00
Ex 9 Type of Formula:
Calculating.
Formula Elements:
Equal sign, SUMIFS function, range of cells, cell reference.
SUMIFS to add with two criteria.
SalesRep Product Total Units
Chelia Product 1
Ex 10 Type of Formula:
Calculating.
Formula Elements:
Equal sign, SUMIFS function, range of cells, cell reference.
SUMIFS to add between 2 dates.
Lower Upper Total Units
>=9/22/2013 <=9/23/2013
9/22/2013 9/23/2013
1000 1500
Ex 11 Type of Formula:
Calculating.
Formula Elements:
Equal sign, NETWORKDAYS.INTL, Absolute & Relative cell references, function argument ele
F4 key toggles between the four types of basic cell references
Start Date for Project End Date Workdays
9/17/2013 10/12/2013
9/27/2013 10/1/2013
10/1/2013 10/12/2013
10/1/2013 10/28/2013
10/15/2013 10/22/2013
10/28/2013 11/15/2013
Ex 12 Type of Formula:
Calculating.
Formula Elements:
Equal sign, mixed cell references, math operator.
Jan Feb
Revenue $55,900 $62,500
COGS
Operating Expenses
Administrative Expenses
Tax Expense
Note:
* Understanding and using mixed cell references has two advantages: 1) Formula creation
2) Some advanced Excel techniques can only be done with mixed cell references (exam
Ex 13 Type of Formula:
Lookup Formula.
Formula Elements:
Equal sign, VLOOKUP function, cell reference, range of cells, function argument element.
Drop-down arrow: Data Ribbon, Data Validation, List
Product Price
Pro 1 $22.00
Pro 2 $25.00
Pro 3 $24.50
Pro 4 $30.00
Amount Commission
0 0
500 0.025
2000 0.04
10000 0.065
15000 0.09
ID First City
842-7728
ID First Last
842-7618 Sioux Smith
842-7728 Chin Pham
842-5556 Gigi Reynolds
842-8395 Phil Gramm
Ex 15 Type of Formula:
Lookup Formula or Logical Formula.
Formula Elements:
Equal sign, MATCH and/or ISNA and ISNUMBER functions, Absolute & Relative cell reference
MATCH to compare 2 lists.
List 1 List 2
Prospective
Company Customer List Customer List
WFMI QFC
PCC PCC
SWY FM
KG SFM
WM
FM List 2
QFC
PCC
FM
SFM
List 2
QFC
PCC
FM
SFM
Ex 16 Type of Formula:
Lookup Formula.
Formula Elements:
Equal sign, INDEX and MATCH, cell references, range of cells, function argument element.
INDEX and MATCH to lookup Left.
Product ID
Boom2
ID Product Price
SS-325 Boom1 $22.00
DTR-6456 Boom2 $23.00
HH-3874 Boom3 $19.00
han symbol.
at is in F12)
e underlying number in the cell.
eyes and what is actually stored in the cell as content.
Number Format that you see on the face of the spreadsheet.
r decimals, but that does not remove the decimals for calculating purposes.
nt formula: =D97*D98*(1-D99)
ent: =D97*0.05*(1-0.25)
More inefficient: =D97*0.0375
In Balance?
Logical aligned CENTER
nseen" decimals.
on can be just "eyeing it".
Text aligned LEFT
Units
1772
1270
1335
1897
1062
581
383
601
Units
1772
1270
1335
1897
1062
581
383
601
Holidays:
10/5/2013
11/1/2013
Sunday is of
Excel Cell References 15 Examples Formulas, Conditional Formatting & Data Validation
http://www.youtube.com/watch?v=_PBPU_5jTUA
argument element.
Total
$0.00
Product Price
Pro 3
, function argument element.
4)
5)
6)
7)
8)
9)
10)
11)
12)
13)
14)
15)
16)
17)
18)
What is a Macro?
Macro Means Code (Set of instructions) for common repetitive tasks.
Code in Excel is called VBA (Visual Basic Application)
You can write code by hand in Excel in the VBA editor (Alt + F11), but not in this class.
Luckily there is a way to have Excel write code for you: Macro Recorder. A Macro Recorder is like a tape recorder recording a
voice, except the Macro Recorder watches what you do in Excel and then writes code for you.
In Excel 2007, you must use the ".xlsm" extension in order to use Macros. You must also set the security level in the Code
Group in the Developer Ribbon to "Disable all Macros with notification"; then each time you open the workbook, click the
"Options" button that appears when you open the workbook, then click "Enable".
In Excel 2003, the file extension .xls works with VBA or without VBA.
Show Developer Ribbon Tab: right-click ribbon, Customize Ribbon, check box for Developer tab.
Relative Macros record Relative movement of the selected cell (OFFSET function..
Absolute Macros record Absolute movement of the selected cell (RANGE function).
The "Use Relative Reference" button in the Code Group in the Developer Ribbon. The "Use Relative Reference" button is a
toggle that toggles between Absolution and Relative References. When the button is yellow/orange in color, the Marco is
recording Relative Cell References in the Macro. When it is not yellow/orange, it is recording Absolute References.
To turn on the Macro Recorder use the "Record Macro button in the Code group in the Developer Ribbon (or the Record Macro
button on the left side of the Status Bar).
Be sure to Turn off the Macro Recorder when you are done
If you store Macros in "This workbook", they are only available in this workbook
If you store Macros in "Personal workbook", they are stored in the hidden workbook named "Personal" and the Macros will be
available in all workbooks on your computer. To edit a Macro in a Personal workbook, you must unhide the workbook using the
"Unhide" button in the Window Group on the View Ribbon.
The VBA editor (Microsoft Visual Basic editor) is where you write code (Alt + F11)
Use Alt + F8 to see list of Macros. You can select a Macro and then click the Edit button to see the code that the Macro recorde
recorded.
In the VBA editor:
Left side shows: 1) "Project Explorer" (Ctrl + R), which sows objects such as Workbook, Worksheets and Modules (Macros). 2)
Properties (F4 key)
If you copy code from internet, when you are in VBA editor, Alt I + P, name Macro, Paste Code on right side, go back to Excel,
Alt + F8 to open Macro List, Select Macro, click Options, assign keyboard shortcut
First Last Address City State Zip Phone E1
Steve
Chin
14 17th St.
Seattle
WA
98104
206-572-3409
Steve_Chin@notaol.com
Chin_14 17th St.@notaol.com
Troun
Tenn
6 19th St.
Seattle
WA
98106
206-667-3154
Troun_Tenn@notaol.com
Tenn_6 19th St.@notaol.com
Rhond
Tradfren
10 13th St.
Seattle
WA
98117
206-574-1265
Rhond_Tradfren@notaol.com
Tradfren_10 13th St.@notaol.com
Sheli
Das
2 15th St.
Seattle
WA
98111
206-637-4014
Sheli_Das@notaol.com
Das_2 15th St.@notaol.com
Jon
Teddy
3 17th St.
Seattle
WA
98114
206-515-5102
Jon_Teddy@notaol.com
Teddy_3 17th St.@notaol.com
Jeri
Yessler
12 16th St.
Seattle
WA
98114
206-583-2943
Jeri_Yessler@notaol.com
Yessler_12 16th St.@notaol.com
Luke
Suix
8 19th St.
Seattle
WA
98113
206-774-4449
Luke_Suix@notaol.com
Suix_8 19th St.@notaol.com
Luke
Red
15 16th St.
Seattle
WA
98108
206-685-2311
Luke_Red@notaol.com
Red_15 16th St.@notaol.com
Jeri
Smythe
14 19th St.
Seattle
WA
98112
206-574-6156
Jeri_Smythe@notaol.com
Smythe_14 19th St.@notaol.com
Luke
Rad
3 16th St.
Seattle
WA
98106
206-599-3338
Luke_Rad@notaol.com
Rad_3 16th St.@notaol.com
Jon
Dap
5 19th St.
Seattle
WA
98116
206-679-1485
Jon_Dap@notaol.com
Dap_5 19th St.@notaol.com
Steve
Gupis
6 17th St.
Seattle
WA
98111
206-609-1507
Steve_Gupis@notaol.com
Gupis_6 17th St.@notaol.com
Jon
Summi
4 14th St.
Seattle
WA
98104
206-733-4318
Jon_Summi@notaol.com
Summi_4 14th St.@notaol.com
Sheli
Fun
7 15th St.
Seattle
WA
98110
206-691-7629
Sheli_Fun@notaol.com
Fun_7 15th St.@notaol.com
E2
First Last Address City State Zip Phone E1
Steve
Chin
14 17th St.
Seattle
WA
98104
206-572-3409
Steve_Chin@notaol.com
Chin_14 17th St.@notaol.com
Troun
Tenn
6 19th St.
Seattle
WA
98106
206-667-3154
Troun_Tenn@notaol.com
Tenn_6 19th St.@notaol.com
Rhond
Tradfren
10 13th St.
Seattle
WA
98117
206-574-1265
Rhond_Tradfren@notaol.com
Tradfren_10 13th St.@notaol.com
Sheli
Das
2 15th St.
Seattle
WA
98111
206-637-4014
Sheli_Das@notaol.com
Das_2 15th St.@notaol.com
Jon
Teddy
3 17th St.
Seattle
WA
98114
206-515-5102
Jon_Teddy@notaol.com
Teddy_3 17th St.@notaol.com
Jeri
Yessler
12 16th St.
Seattle
WA
98114
206-583-2943
Jeri_Yessler@notaol.com
Yessler_12 16th St.@notaol.com
Luke
Suix
8 19th St.
Seattle
WA
98113
206-774-4449
Luke_Suix@notaol.com
Suix_8 19th St.@notaol.com
Luke
Red
15 16th St.
Seattle
WA
98108
206-685-2311
Luke_Red@notaol.com
Red_15 16th St.@notaol.com
Jeri
Smythe
14 19th St.
Seattle
WA
98112
206-574-6156
Jeri_Smythe@notaol.com
Smythe_14 19th St.@notaol.com
Luke
Rad
3 16th St.
Seattle
WA
98106
206-599-3338
Luke_Rad@notaol.com
Rad_3 16th St.@notaol.com
Jon
Dap
5 19th St.
Seattle
WA
98116
206-679-1485
Jon_Dap@notaol.com
Dap_5 19th St.@notaol.com
Steve
Gupis
6 17th St.
Seattle
WA
98111
206-609-1507
Steve_Gupis@notaol.com
Gupis_6 17th St.@notaol.com
Jon
Summi
4 14th St.
Seattle
WA
98104
206-733-4318
Jon_Summi@notaol.com
Summi_4 14th St.@notaol.com
Sheli
Fun
7 15th St.
Seattle
WA
98110
206-691-7629
Sheli_Fun@notaol.com
Fun_7 15th St.@notaol.com
E2
First Last Address City State Zip Phone E1
Steve
Chin
14 17th St.
Seattle
WA
98104
206-572-3409
Steve_Chin@notaol.com
Chin_14 17th St.@notaol.com
Troun
Tenn
6 19th St.
Seattle
WA
98106
206-667-3154
Troun_Tenn@notaol.com
Tenn_6 19th St.@notaol.com
Rhond
Tradfren
10 13th St.
Seattle
WA
98117
206-574-1265
Rhond_Tradfren@notaol.com
Tradfren_10 13th St.@notaol.com
Sheli
Das
2 15th St.
Seattle
WA
98111
206-637-4014
Sheli_Das@notaol.com
Das_2 15th St.@notaol.com
Jon
Teddy
3 17th St.
Seattle
WA
98114
206-515-5102
Jon_Teddy@notaol.com
Teddy_3 17th St.@notaol.com
Jeri
Yessler
12 16th St.
Seattle
WA
98114
206-583-2943
Jeri_Yessler@notaol.com
Yessler_12 16th St.@notaol.com
Luke
Suix
8 19th St.
Seattle
WA
98113
206-774-4449
Luke_Suix@notaol.com
Suix_8 19th St.@notaol.com
Luke
Red
15 16th St.
Seattle
WA
98108
206-685-2311
Luke_Red@notaol.com
Red_15 16th St.@notaol.com
Jeri
Smythe
14 19th St.
Seattle
WA
98112
206-574-6156
Jeri_Smythe@notaol.com
Smythe_14 19th St.@notaol.com
Luke
Rad
3 16th St.
Seattle
WA
98106
206-599-3338
Luke_Rad@notaol.com
Rad_3 16th St.@notaol.com
Jon
Dap
5 19th St.
Seattle
WA
98116
206-679-1485
Jon_Dap@notaol.com
Dap_5 19th St.@notaol.com
Steve
Gupis
6 17th St.
Seattle
WA
98111
206-609-1507
Steve_Gupis@notaol.com
Gupis_6 17th St.@notaol.com
Jon
Summi
4 14th St.
Seattle
WA
98104
206-733-4318
Jon_Summi@notaol.com
Summi_4 14th St.@notaol.com
Sheli
Fun
7 15th St.
Seattle
WA
98110
206-691-7629
Sheli_Fun@notaol.com
Fun_7 15th St.@notaol.com
E2
First Last Address City State Zip Phone E1
Steve
Chin
14 17th St.
Seattle
WA
98104
206-572-3409
Steve_Chin@notaol.com
Chin_14 17th St.@notaol.com
Troun
Tenn
6 19th St.
Seattle
WA
98106
206-667-3154
Troun_Tenn@notaol.com
Tenn_6 19th St.@notaol.com
Rhond
Tradfren
10 13th St.
Seattle
WA
98117
206-574-1265
Rhond_Tradfren@notaol.com
Tradfren_10 13th St.@notaol.com
Sheli
Das
2 15th St.
Seattle
WA
98111
206-637-4014
Sheli_Das@notaol.com
Das_2 15th St.@notaol.com
Jon
Teddy
3 17th St.
Seattle
WA
98114
206-515-5102
Jon_Teddy@notaol.com
Teddy_3 17th St.@notaol.com
Jeri
Yessler
12 16th St.
Seattle
WA
98114
206-583-2943
Jeri_Yessler@notaol.com
Yessler_12 16th St.@notaol.com
Luke
Suix
8 19th St.
Seattle
WA
98113
206-774-4449
Luke_Suix@notaol.com
Suix_8 19th St.@notaol.com
Luke
Red
15 16th St.
Seattle
WA
98108
206-685-2311
Luke_Red@notaol.com
Red_15 16th St.@notaol.com
Jeri
Smythe
14 19th St.
Seattle
WA
98112
206-574-6156
Jeri_Smythe@notaol.com
Smythe_14 19th St.@notaol.com
Luke
Rad
3 16th St.
Seattle
WA
98106
206-599-3338
Luke_Rad@notaol.com
Rad_3 16th St.@notaol.com
Jon
Dap
5 19th St.
Seattle
WA
98116
206-679-1485
Jon_Dap@notaol.com
Dap_5 19th St.@notaol.com
Steve
Gupis
6 17th St.
Seattle
WA
98111
206-609-1507
Steve_Gupis@notaol.com
Gupis_6 17th St.@notaol.com
Jon
Summi
4 14th St.
Seattle
WA
98104
206-733-4318
Jon_Summi@notaol.com
Summi_4 14th St.@notaol.com
Sheli
Fun
7 15th St.
Seattle
WA
98110
206-691-7629
Sheli_Fun@notaol.com
Fun_7 15th St.@notaol.com
E2
First Last Address City State Zip Phone E1
Steve
Chin
14 17th St.
Seattle
WA
98104
206-572-3409
Steve_Chin@notaol.com
Chin_14 17th St.@notaol.com
Troun
Tenn
6 19th St.
Seattle
WA
98106
206-667-3154
Troun_Tenn@notaol.com
Tenn_6 19th St.@notaol.com
Rhond
Tradfren
10 13th St.
Seattle
WA
98117
206-574-1265
Rhond_Tradfren@notaol.com
Tradfren_10 13th St.@notaol.com
Sheli
Das
2 15th St.
Seattle
WA
98111
206-637-4014
Sheli_Das@notaol.com
Das_2 15th St.@notaol.com
Jon
Teddy
3 17th St.
Seattle
WA
98114
206-515-5102
Jon_Teddy@notaol.com
Teddy_3 17th St.@notaol.com
Jeri
Yessler
12 16th St.
Seattle
WA
98114
206-583-2943
Jeri_Yessler@notaol.com
Yessler_12 16th St.@notaol.com
Luke
Suix
8 19th St.
Seattle
WA
98113
206-774-4449
Luke_Suix@notaol.com
Suix_8 19th St.@notaol.com
Luke
Red
15 16th St.
Seattle
WA
98108
206-685-2311
Luke_Red@notaol.com
Red_15 16th St.@notaol.com
Jeri
Smythe
14 19th St.
Seattle
WA
98112
206-574-6156
Jeri_Smythe@notaol.com
Smythe_14 19th St.@notaol.com
Luke
Rad
3 16th St.
Seattle
WA
98106
206-599-3338
Luke_Rad@notaol.com
Rad_3 16th St.@notaol.com
Jon
Dap
5 19th St.
Seattle
WA
98116
206-679-1485
Jon_Dap@notaol.com
Dap_5 19th St.@notaol.com
Steve
Gupis
6 17th St.
Seattle
WA
98111
206-609-1507
Steve_Gupis@notaol.com
Gupis_6 17th St.@notaol.com
Jon
Summi
4 14th St.
Seattle
WA
98104
206-733-4318
Jon_Summi@notaol.com
Summi_4 14th St.@notaol.com
Sheli
Fun
7 15th St.
Seattle
WA
98110
206-691-7629
Sheli_Fun@notaol.com
Fun_7 15th St.@notaol.com
E2
First Last Address City State Zip Phone E1
Steve
Chin
14 17th St.
Seattle
WA
98104
206-572-3409
Steve_Chin@notaol.com
Chin_14 17th St.@notaol.com
Troun
Tenn
6 19th St.
Seattle
WA
98106
206-667-3154
Troun_Tenn@notaol.com
Tenn_6 19th St.@notaol.com
Rhond
Tradfren
10 13th St.
Seattle
WA
98117
206-574-1265
Rhond_Tradfren@notaol.com
Tradfren_10 13th St.@notaol.com
Sheli
Das
2 15th St.
Seattle
WA
98111
206-637-4014
Sheli_Das@notaol.com
Das_2 15th St.@notaol.com
Jon
Teddy
3 17th St.
Seattle
WA
98114
206-515-5102
Jon_Teddy@notaol.com
Teddy_3 17th St.@notaol.com
Jeri
Yessler
12 16th St.
Seattle
WA
98114
206-583-2943
Jeri_Yessler@notaol.com
Yessler_12 16th St.@notaol.com
Luke
Suix
8 19th St.
Seattle
WA
98113
206-774-4449
Luke_Suix@notaol.com
Suix_8 19th St.@notaol.com
Luke
Red
15 16th St.
Seattle
WA
98108
206-685-2311
Luke_Red@notaol.com
Red_15 16th St.@notaol.com
Jeri
Smythe
14 19th St.
Seattle
WA
98112
206-574-6156
Jeri_Smythe@notaol.com
Smythe_14 19th St.@notaol.com
Luke
Rad
3 16th St.
Seattle
WA
98106
206-599-3338
Luke_Rad@notaol.com
Rad_3 16th St.@notaol.com
Jon
Dap
5 19th St.
Seattle
WA
98116
206-679-1485
Jon_Dap@notaol.com
Dap_5 19th St.@notaol.com
Steve
Gupis
6 17th St.
Seattle
WA
98111
206-609-1507
Steve_Gupis@notaol.com
Gupis_6 17th St.@notaol.com
Jon
Summi
4 14th St.
Seattle
WA
98104
206-733-4318
Jon_Summi@notaol.com
Summi_4 14th St.@notaol.com
Sheli
Fun
7 15th St.
Seattle
WA
98110
206-691-7629
Sheli_Fun@notaol.com
Fun_7 15th St.@notaol.com
E2
Into The Wind Sales
Jan Feb
Toys 500 2,150
Boomerangs 4,000 6,933
Kites 9,721 5,367
Total 14,221 14,450
Toys; 19%
Kites; 48%
Boomerangs; 33%
Kites; 48%
Boomerangs; 33%
Toys; 19%
16,000
14,000
12,000
10,000
8,000
Toys Boomerangs Kites
16,000
14,000
12,000
10,000
8,000
6,000
4,000
2,000
-
Jan Feb Mar
Mar
Feb
Jan
Mar
Feb
Jan
Scores
69
75 Row Labels
56 0-9
26 20-29
85 30-39
67 40-49
67 50-59
70 60-69
30 70-79
81 80-89
74 90-100
98 Grand Total
45
79 Categories - Each Integer Category Has A Range Of 10 A
72
65
68
52
82
53
64
81
88
92
64
82
34
52
74
73
66
37
8
84
68
62
38
Sales
Mar Total
8,893 11,543
9,545 20,478
15,064 30,152
33,502 62,173
9%
angs; 33%
Chart Junk.
to Mar Unnecessary repetition.
Titled pies distorts the proportions or percentages.
8%
Mar
Kites
20
0
0 2 4 6 8 10 12 14
Hours Studied - X
Count of Scores
Histogram
1
12
10
10
1 8 7
6
Frequency or Count 4 4
4
4 2 1 1 1
0
0-9 20-29 30-39 40-49 50-59 60-69 70-79
1 Categories For Counting Scores
4
10 Histograms are a certain type of column chart that have c
7 touching to indicate that no value is possible between the
7
2
37
ch Integer Category Has A Range Of 10 And Each Has No Gap Because There Are No Possibilities Between Colum
whole. Percentages.
s or percentages.
8.4518178609
8 10 12 14 16
died - X
am
10
7 7
4
2
1
These labels
will appear in
the color coded
'Legend'
Jan Feb
Toys $500 $2,150
Boomerangs $4,000 $6,933
Kites $9,721 $5,367
Add Title: Select chart, Chart Tools Layout ribbon tab, Labels group, Chart Title.
Link Chart label to cells: select label, use Formula Bar to create formula.
Select elements in chart to format either: 1) Select chart, Chart Tools
Layout ribbon tab, Current selection group, or click on element in chart
Ctrl + 1 opens format Chart dialog box
Keyboard for Default Chart on sheet: Alt + F1, New
Jan Feb Mar _x0011_ These are called 'Categories' and will appe
$500 $2,150 $8,893 _x0011_ The numbers 500, 2,150, 8,893 are called
$4,000 $6,933 $9,545 The numbers 4,000, 6,933, 9,545 are called the 'Bo
$9,721 $5,367 $15,064 The numbers 9,721, 5,367, 15,064 are called the 'K
rt on sheet: Alt + F1, New Sheet = F11.
Save Template: Select chart, Chart Tools Design ribbon tab, Type group, Save As Templates.
Set Default: Select chart, Chart Tools Design ribbon tab, Type group, Change Chart type, Set As Defau
Amount
98,847.50 ; 19%
Sales Tax; $133,947.00 ; 26%
Tax; $102,512.50 ; 20%
Income Tax; $179,193.00 ; 35%
e As Templates.
e Chart type, Set As Default.
Into The Wind Sales
Jan Feb Mar Total
Into The Wi
Toys $500 $2,150 $8,893 $11,543
Boomerang $4,000 $6,933 $9,545 $20,478 $20,000
Kites $9,721 $5,367 $15,064 $30,152 $15,000
Total $14,221 $14,450 $33,502 $62,173
$10,000
$5,000
$0
Copy chart: Ctrl + C, Click in cell, Ctrl + V Jan Feb
Change Chart Type.
Into The Wind Sales
Toys
Boomerangs
Kites
Sales by Year
$30,000
$25,000
$20,000
$15,000
$10,000
$5,000
$0
2005 2006 2007 2008 2009 2010 2011 2012
X = # police Crimes
5 41
13 45
27 15
34 9
7 43
35 11
9 24
19 21
16 19
2 Numbers: along x then along y and out put a marker.
Shows relationship between 2 numbers.
60
Test Scores - Y
40
20
0
0 2 4 6 8 10
010 2011 2012
Hours Studied - X
d out put a marker.
48.4518178609
6 8 10 12 14 16
Hours Studied - X
y intercept
Where line crosses y-axis
b
48.4518178609
slope
For 1 unit out x-axis, how far up the y-axis do you go?
m
4.8348040946
correlation Coefficient
Strength and direction of line (not causation)
r
0.7815613776
Coefficient of Determination
The amount of influence x has on y (not causation)
r^2
0.6108381869
Units Sales Total Costs Fixed Cost Net Income
0 $0 $171,563 $171,563 -$171,563
3,500 87,500 219,688 171,563 -$132,188
7,000 175,000 267,813 171,563 -$92,813
10,500 262,500 315,938 171,563 -$53,438
14,000 350,000 364,063 171,563 -$14,063
17,500 437,500 412,188 171,563 $25,313
21,000 525,000 460,313 171,563 $64,688
24,500 612,500 508,438 171,563 $104,063
28,000 700,000 556,563 171,563 $143,438
Assumptions Tables
Sell Price of
Boomerang $25.00
Units Sold 15,250
Manufacturing
Cost of
Boomerang as a
% of Sell Price 0.55