The top of your Excel screen looks something like this:
The Format menu item is good.
It looks like this:
You can select some cells
(by "dragging" your mouse across the cells)
and choose Number or Font or Border etc. etc.
The Charting icon is also good.
The Formula Bar is good, too.
Let's proceed ...
LESSONS in EXCEL
Alignment
Hello
15.295
25.60%
Hello has Left Alignment 15.295 has Centre 25.60% has Right Alignment.
To change Alignment: select Cells, choose Format/Cells/Alignment and get a choice of Left, Right, etc.
Worksheet Display
To place a Border about certain Cells: select the Cells, then:
Choose Format/Cells/Border and pick Outline, Color, etc.
Copy, Paste & Formats
Aug 11/90
Aug 18/90
Aug 25/90
###
Sep 8/90
The above are DATES, with Code mmm d/yy (Month Day/Year).
Change the format by selecting Format/Cells/Number/Date and picking a format
25.60
$32.60
39.60
46.60
53.60
To change the number format, choose Format/Cells then Number or Currency etc. etc.
Cell C19 has a FORMULA, namely: =B19+7
This formula is COPIED and PASTED into cells D19 to
Practice
DO
Change the numbers in Column B, below, to the Format indicated:
1234.567
Change to Dollars and Cents, aligned Left.
-1234.567
Change to Dollars and Cents, aligned Right.
-1234.567
Change to a Number, with TWO decimal places, aligned Right.
0.123456
0.123456
0.123456
12/24/1990
12/24/1990
12/24/1990
DO
Change to Percentage, with TWO decimal places, aligned Centre.
Change to Percentage, with THREE decimal places (i.e. 12.346%).
Change to Percentage, with NO decimal places, aligned Right.
Change so it reads: Dec 25, 1994 and aligned Left.
Change so it reads: Dec 25/94 and aligned Right.
Change so it reads: Dec25:1994 and aligned Left.
Put Borders around each of the Cell Collections below by choosing Format/Cells/Border, as indicated:
(a)This is the 1st Collection
of Cells: make a Plain Outline
(b) This is another Collection of Cells.
Make a double-lined Border on the Left
and a Dotted Border on the Right.
like
so
like
this
(a)This is the 1st Collection
of Cells: make a Plain Outline
(c) Put EACH of the six Cells below inside a box:
12.34
23.45
22.34
33.45
which looks like so:
12.34
23.45
22.34
33.45
DO
Plot a Graph of Income, Expenses and Profit, for the Months of Jan, Feb, Mar
Jan 30/90
1234.56
299.99
934.57
Income
Expenses
Profit
Feb 27/90
2899.99
365.4
2534.59
###
2156.69 <<< USE THIS DATA
2345.67
-188.98
Just select everything from B52 down to E55, then click on the icon
3500
and choose Column
Be sure to:
(1) Change the Date Format (double-click on the Ho
3000
(2) Change the Currency Format (double-click on th
2500
(4) Double click the Vertical Axis and
(3) Change the Legend format (double-click on the
2000
Income
Expenses
Profit
1500
1000
change the Font to something else.
(5) Do the same for the Horizontal Axis.
(6) ... and the Legend.
(7) Add a Title by typng right on the chart
and change its Font (by double-clicking on the Title
and add a Border around the Title.
500
(8) Double-click on graph Bars to change
0
-500
colours and patterns. (Try Fill Effects.)
Jan 1/90
Feb 1/90
Mar 1/90
(9) Type anywhere on Graph to generate Text,
then double-click text to change its appearance
(10) etc. etc. Can you make it look like this ??
DO
COPY the Cells inside the double-lined box (below) to the location indicated:
Income
Expenses
Profit
Jan 30/90
1234.56
299.99
934.57
Feb 27/90
2899.99
365.4
2534.59
###
2156.69
2345.67
-188.98
COPY these Cells
Put the COPY here
DO
Modify the numbers (in the COPY, above) so it gives the Percentage change in Income, Expenses and Profit
... for Feb and Mar, like so:
Jan
Feb
Mar
Income
$1,234.56
135%
-26%
<< shows a 135% increase in Income, from Jan
Expenses
$299.99
22%
542% << shows a 542% increase in Expenses, Feb to
Profit
$934.57
171%
-107% << shows a 107% decrease in Profit, Feb to Ma
600%
Income
Expenses
Prof it
500%
400%
300%
200%
100%
0%
-100%
-200%
Feb
Mar
600%
Income
Expenses
Prof it
500%
400%
300%
DO
Make a Chart of the Percentage changes ... like so
Put it here:
200%
100%
0%
-100%
-200%
Have a break!
Feb
Mar
hoice of Left, Right, etc.
Sep 15/90
Sep 22/90
60.60
67.60
PIED and PASTED into cells D19 to H19.
Border, as indicated:
Plain Outline
e six Cells below inside a box:
34.56
44.56
34.56
44.56
E THIS DATA
Income
Expenses
Profit
Jan
$1,234.56
$299.99
$934.57
Feb
$2,899.99
$365.40
$2,534.59
oose Column
nge the Date Format (double-click on the Horizontal axis)
nge the Currency Format (double-click on the Vertical axis)
nge the Legend format (double-click on the Legend)
ble click the Vertical Axis and
Hogsville Variety
Store
3500
3000
2500
ange the Font to something else.
the same for the Horizontal Axis.
2000
Incom e
Expense s
1500
a Title by typng right on the chart
ange its Font (by double-clicking on the Title)
d a Border around the Title.
ble-click on graph Bars to change
Profit
1000
500
ours and patterns. (Try Fill Effects.)
e anywhere on Graph to generate Text,
en double-click text to change its appearance.
c. etc. Can you make it look like this ??
OPY these Cells
ut the COPY here
come, Expenses and Profit
ws a 135% increase in Income, from Jan to Feb.
ws a 542% increase in Expenses, Feb to Mar.
ws a 107% decrease in Profit, Feb to Mar.
Income
Feb
Expenses
Prof it
Mar
0
-500
Jan 1/90
Feb 1/90
Mar 1/90
Income
Feb
Expenses
Prof it
Mar
Mar
$2,156.69
$2,345.67
($188.98)
Incom e
Expense s
Profit
INDEX(cell:range, number)
1
2
3
4
5
6
7
8
9
###
Here's a bunch of dollar values:
$191.03
Suppose you want to pick out the 5th number.
$194.62
The "formula" is
$176.21
=INDEX($B$4:$B$13,5)
$122.45
where the $B$4:$B$13 means "look in column B, from B4 to B13"
$100.13
and the "5" means (what else?), pick out the 5th number!
$171.60
It gives:
$150.63
$100.13
Look at this cell to see the command
$135.43
$154.26
Okay, YOU try it here ... and pick out the 9th number:
$180.88
Now we want to pick out every SECOND number and make a list, like so:
2
$194.62
4
$122.45
6
$171.60
8
$135.43
### $180.88
What we want is =INDEX($B$4:$B$13,2)
followed by =INDEX($B$4:$B$13,4)
followed by =INDEX($B$4:$B$13,6)
etc. etc.
Aaah ... too much work, so we do the following:
First, we type the "2" ... as in =INDEX($B$4:$B$13,2)
2
4
Now we type =F28+2 which will give "4", like so:
6
Then we copy cell F29 and paste it into F30, F31, F32, etc.
8
(This gives the numbers 2, 4, 6, etc, which we want.)
10
Now, in G28, type our first INDEX command, namely
=INDEX($B$4:$B$13,F28)
YOU DO IT!!
You should get 194.62
Note that you didn't type =INDEX($B$4:$B$13,2)
but, instead, you typed
=INDEX($B$4:$B$13,F28)
so the Index Number "2" was picked up from cell F28. NEAT!
Now, copy the formula in cell G28 and paste it into cells G29, G30, G31, etc.
YOU DO IT!!
You'll get the numbers you want!
Now, change the Index Number "2" in cell F28 to a "1".
Then you'll get the 1st, 3rd, 5th, etc. numbers.
YOU DO IT!!
Practice
Here are two columns, "copied" from the the Hogsville Variety Store Ledger then "pasted" here.
0.00
Calendar Here's your assignment:
($303)
Make a list of Sales (starting with the number $179,797)
Paid Out
Note that we're working with column C, from C48 to C??.
(YOU find the end of the data!!)
$3,210
DAY 54
Note, too, that the first "Sales" figure we want is at Index Number 9,
Deposit
Withdrawal counting from the beginning of the data (at C48).
3336.75
400.00 Note, too, that the "Sales" figures occur every 28 rows.
CUMULATIVE
Sales $179,797
Taxable Income
$24,599
Index Number
Sales
Total SALES
$31,729 YOU DO IT!!
Total Paid Out
$32,514
GST payable
($660)
PST payable
$15
ACCTs rcvble
$3,645
Gross Income
($785)
Bank Balance
$4,022
Future Balance
$8,335
Accts Payable
$1
CP Received
$4,808
CP Paid Out
$4,809
CP Sales
15.2%
Expenses
$8,085
Cash in Hand
$1,323
Checks in Hand
$668.47
Av. Sales/day
$588
+ G/PST
1101.45
CIH
$1,020
0.00
Calendar
Paid Out
+ G/PST
($304)
133.54
CIH
$1,253
DAY 55
Deposit
Withdrawal
CUMULATIVE
Sales $180,144
Taxable Income
$24,674
Total SALES
$32,379
Total Paid Out
$32,637
GST payable
($637)
PST payable
$31
ACCTs rcvble
$3,592
Gross Income
($258)
Bank Balance
$4,022
Future Balance
$8,669
Accts Payable
$1
CP Received
$4,903
CP Paid Out
$4,809
CP Sales
15.1%
It should look like this
(sort of)
You didn't really HAVE to find
the end of the data.
You can just use
$C$48:$C$1000
and ignore the numbers
at the end!
In fact, you could use
$C$1:$C$1000
and start with
"Index Number"= 56
(as I did here), then increase
by 28 then another 28
etc. etc.
Index Number
56
84
112
140
168
196
224
252
280
308
336
364
392
420
448
Sales
$179,797
$180,144
$182,795
$183,878
$184,726
$185,222
$185,526
$185,677
$185,308
$185,837
$185,995
$0
$0
$0
$0
Expenses
Cash in Hand
Checks in Hand
Av. Sales/day
$8,085
$1,556
$1,056
$589
0.00
Calendar
Paid Out
+ G/PST
($305)
473.59
Deposit
CIH
$1,299
DAY 56
Withdrawal
CUMULATIVE
Sales $182,795
Taxable Income
$25,242
Total SALES
$33,453
Total Paid Out
$33,087
GST payable
($600)
PST payable
$55
ACCTs rcvble
$3,720
Gross Income
$366
Bank Balance
$4,022
Future Balance
$9,308
Accts Payable
$1
CP Received
$5,195
CP Paid Out
$4,809
CP Sales
15.5%
Expenses
$8,085
Cash in Hand
$1,604
Checks in Hand
$1,566
Av. Sales/day
$597
0.00
Calendar
Paid Out
+ G/PST
($358)
645.99
Deposit
476
504
532
560
588
616
644
672
700
CIH
$865
DAY 57
Withdrawal
CUMULATIVE
Sales $183,878
Taxable Income
$25,474
Total SALES
$34,252
Total Paid Out
$33,715
GST payable
($585)
PST payable
$64
Bye now!
$0
$0
$0
$0
$0
$0
$0
$0
$0
ACCTs rcvble
Gross Income
Bank Balance
Future Balance
Accts Payable
CP Received
CP Paid Out
CP Sales
Expenses
Cash in Hand
Checks in Hand
Av. Sales/day
$3,700
$537
$4,022
$9,877
$1
$5,457
$4,809
15.9%
$8,149
$1,223
$2,155
$601
0.00
Calendar
Paid Out
+ G/PST
($383)
589.40
Deposit
CIH
$1,201
DAY 58
Withdrawal
CUMULATIVE
Sales $184,726
Taxable Income
$25,656
Total SALES
$35,013
Total Paid Out
$34,304
GST payable
($561)
PST payable
$87
ACCTs rcvble
$3,569
Gross Income
$709
Bank Balance
$3,571
Future Balance
$9,761
Accts Payable
$1
CP Received
$5,491
CP Paid Out
$4,809
CP Sales
15.7%
Expenses
$8,149
Cash in Hand
$1,584
Checks in Hand
$2,621
Av. Sales/day
$604
0.00
Calendar
Paid Out
+ G/PST
($382)
203.69
Deposit
CIH
$1,375
DAY 59
Withdrawal
CUMULATIVE
Sales $185,222
Taxable Income
$25,762
Total SALES
$35,713
Total Paid Out
$36,371
GST payable
($699)
PST payable
$109
ACCTs rcvble
$3,462
Gross Income
($658)
Bank Balance
$1,154
Future Balance
$7,773
Accts Payable
$5
CP Received
$5,512
CP Paid Out
$6,682
CP Sales
15.4%
Expenses
$8,899
Cash in Hand
$1,757
Checks in Hand
###
Av. Sales/day
$605
0.00
Calendar
Paid Out
+ G/PST
$120
664.07
Deposit
3162.23
CIH
$1,120
DAY 60
Withdrawal
CUMULATIVE
Sales $185,526
Taxable Income
$25,827
Total SALES
$36,378
Total Paid Out
$37,004
GST payable
($688)
PST payable
$125
ACCTs rcvble
$3,517
Gross Income
($627)
Bank Balance
$4,237
Future Balance
$8,086
Accts Payable
$5
CP Received
$5,547
CP Paid Out
$6,682
CP Sales
15.2%
Expenses
$8,899
Cash in Hand
$1,000
Checks in Hand
$337.15
Av. Sales/day
$606
0.00
Calendar
Paid Out
+ G/PST
$120
289.96
Deposit
CIH
$1,410
DAY 61
Withdrawal
CUMULATIVE
Sales $185,677
Taxable Income
$25,860
Total SALES
$37,014
Total Paid Out
$37,290
GST payable
($677)
PST payable
$139
ACCTs rcvble
$3,644
Gross Income
($276)
Bank Balance
$3,910
Future Balance
$8,037
Accts Payable
$5
CP Received
$5,642
CP Paid Out
$6,682
CP Sales
15.2%
Expenses
$9,044
Cash in Hand
$1,290
Checks in Hand
$488.33
Av. Sales/day
$607
0.00
Calendar
Paid Out
+ G/PST
$120
834.24
Deposit
CIH
$1,140
DAY 62
Withdrawal
CUMULATIVE
Sales $185,308
Taxable Income
$25,781
Total SALES
$37,546
Total Paid Out
$38,103
GST payable
($680)
PST payable
$148
ACCTs rcvble
$3,762
Gross Income
($557)
Bank Balance
$3,559
Future Balance
$7,986
Accts Payable
CP Received
CP Paid Out
CP Sales
Expenses
Cash in Hand
Checks in Hand
Av. Sales/day
$5
$5,710
$6,682
15.2%
$9,095
$1,020
$669.78
$606
0.00
Calendar
Paid Out
+ G/PST
$119
331.03
Deposit
CIH
$1,276
DAY 63
Withdrawal
CUMULATIVE
Sales $185,837
Taxable Income
$25,894
Total SALES
$38,261
Total Paid Out
$38,423
GST payable
($663)
PST payable
$167
ACCTs rcvble
$3,981
Gross Income
($162)
Bank Balance
$3,559
Future Balance
$8,280
Accts Payable
$5
CP Received
$5,781
CP Paid Out
$6,682
CP Sales
15.1%
Expenses
$9,095
Cash in Hand
$1,157
Checks in Hand
$745.63
Av. Sales/day
$607
0.00
Calendar
Paid Out
+ G/PST
$118
274.65
Deposit
CIH
$1,423
DAY 64
Withdrawal
CUMULATIVE
Sales $185,995
Taxable Income
Total SALES
Total Paid Out
GST payable
PST payable
ACCTs rcvble
Gross Income
Bank Balance
Future Balance
Accts Payable
CP Received
CP Paid Out
CP Sales
Expenses
Cash in Hand
Checks in Hand
Av. Sales/day
$25,928
$38,901
$38,691
($649)
$177
$4,216
$210
$3,446
$8,522
$5
$5,783
$6,682
14.9%
$9,102
$1,305
$865.40
$608
umber $179,797)
ndex Number 9,
ignore all
this stuff
-
MATCH(Item-to-Match,cell:range)
Here's a bunch of Months and Sales:
Month
Sales
Jan
$1,910
Suppose you want to pick out the Sales corresponding to "Jul"
Feb
$1,946
We start with the formula:
Mar
$1,762
=MATCH("Jul",$B$4:$B$13,0)
Apr
$1,225
where the $B$4:$B$13 means "look in column B, from B4 to B13"
May
$1,001
and the "Jul" means (what else?), find "Jul".
Jun
$1,716
It gives the Index Number
Jul
$1,506
7
meaning it's the 7th member of the list: B4 to B13.
Aug
$1,354
Sep
$1,543
Okay, YOU try it here ... and pick out the Index Number for "Apr":
Oct
$1,809
(You can peek at cell D10 to see how it's done.)
Now we want to pick out the "Sales" corresponding to "Jul". We know the Index Number, namely 7, so it's
cell number 7, BUT it's in the "Sales List", from C4 to C13 (as opposed to the "Month List" in B4 to B13)
=INDEX($C$4:$C$13,7)
This will do it.
Of course, we got the number 7 from =MATCH("Jul",$B$4:$B$13,0)
so we could just replace the 7, in INDEX($C$4:$C$13,7), by this MATCH expression and get:
=INDEX($C$4:$C$13,MATCH("Jul",$B$4:$B$13,0))
Okay, now YOU do the same below, but pick out the "Sales" for "Apr":
You should get $1,225
Okay, now stare at the following INDEX/MATCH formula (in cell C26)
Apr
=INDEX($C$4:$C$13,MATCH(B26,$B$4:$B$13,0))
It's just like before, except that instead of writing "Apr" or "Jul", the "Month" is taken from cell B26.
Now YOU type the INDEX/MATCH formula in the box below so it picks out the "Month" from cell B29:
Apr
Do it here
When you're finished, you should get $1,225
NOW ... change the Month in cell B29, from Apr to Jan or Feb or Mar ... etc. etc.
Practice
Here are two columns which give a Date and a "Sales" Amount:
Dec 31/93 $1,225.22 Here's your assignment:
Jan 1/94
$1,062.17 Write a formula so you can type in a Date such as 12/01/94
Jan 2/94
$1,052.38 (into cell D40) and you get the Sales for that Date (in E40).
Jan 3/94
$1,060.68
Jan 4/94
$1,430.25 Date goes here "Sales" goes here
Jan 5/94
$1,141.31
Jan 6/94
$1,013.04
Jan 7/94
Bye now!
$1,276.08
Jan 8/94
$1,334.10
Jan 9/94
$1,199.75
Jan 10/94 $1,392.89
Jan 11/94 $1,215.64
Jan 12/94 $1,428.99
Jan 12/94
$1,428.99
don't peek here !!
Jan 13/94 $1,087.04
Jan 14/94 $1,365.59
Jan 15/94
Jan 16/94
Jan 17/94
Jan 18/94
Jan 19/94
Jan 20/94
Jan 21/94
Jan 22/94
Jan 23/94
Jan 24/94
Jan 25/94
Jan 26/94
Jan 27/94
Jan 28/94
Jan 29/94
Jan 30/94
Jan 31/94
Feb 1/94
Feb 2/94
Feb 3/94
Feb 4/94
Feb 5/94
Feb 6/94
Feb 7/94
Feb 8/94
Feb 9/94
Feb 10/94
Feb 11/94
Feb 12/94
Feb 13/94
Feb 14/94
Feb 15/94
Feb 16/94
Feb 17/94
Feb 18/94
Feb 19/94
Feb 20/94
Feb 21/94
Feb 22/94
Feb 23/94
Feb 24/94
Feb 25/94
Feb 26/94
Feb 27/94
Feb 28/94
Mar 1/94
Mar 2/94
Mar 3/94
Mar 4/94
Mar 5/94
Mar 6/94
$1,307.53
$1,278.45
$1,089.98
$1,364.57
$1,152.30
$1,434.48
$1,430.49
$1,277.04
$1,244.14
$1,491.51
$1,069.53
$1,125.49
$1,155.38
$1,046.13
$1,070.51
$1,156.19
$1,127.89
$1,489.26
$1,216.16
$1,288.88
$1,167.57
$1,481.88
$1,189.63
$1,256.14
$1,163.03
$1,069.45
$1,164.90
$1,476.74
$1,331.67
$1,080.56
$1,262.45
$1,378.49
$1,318.53
$1,274.28
$1,045.79
$1,095.30
$1,423.53
$1,279.28
$1,373.25
$1,433.18
$1,456.40
$1,105.93
$1,323.29
$1,383.64
$1,254.08
$1,442.03
$1,270.02
$1,312.76
$1,248.26
$1,318.67
$1,265.22
Mar 7/94
Mar 8/94
Mar 9/94
Mar 10/94
Mar 11/94
Mar 12/94
Mar 13/94
Mar 14/94
Mar 15/94
Mar 16/94
$1,423.95
$1,248.38
$1,009.99
$1,186.07
$1,459.84
$1,016.33
$1,272.72
$1,138.29
$1,208.64
$1,020.27
ow it's done.)
namely 7, so it's
t" in B4 to B13)
ssion and get:
om cell B29:
h as 12/01/94 (meaning Jan 12/94)
Date (in E40).
more STUFF:
Sample array:
1.1
3.1
1.2
3.2
1.3
3.3
1.4
3.4
1.5
3.5
MATCH(x,A:B,0)
example
Do this
Do this
=MATCH(2.2,F2:F7,0)
Pick out the cell in D3:D7 matching 3.4
INDEX(A:B,m,n)
example
=INDEX(C2:G7,3,2)
example
=INDEX(C2:C7,MATCH(2.2,F2:F7,0))
Pick out the cell in row 4, column 3 in C2:F7
C2: F7
4.1
4.2
4.3
4.4
4.5
2.1
2.2
2.3
2.4
2.5
hello
Scan the array A:B, stopping at the first cell whose value
Return the relative location of that cell.
2
It's a "2" because the SECOND cell in F2:F7 matc
(You should get a "3")
Move through array A:B to row m, column n and display
3.3
Go to row 3, column 2 of C2:G7. Display the valu
1.2
In F2:F7, 2nd cell matches 2.2 so get value in 2nd
(You should get 4.4)
the TEXT command
Change the MONTH and the SALES figure:
and watch the change below!
MONTH SALES
Mar
###
Err:502
and CONCATENATION
Change the two NAMEs:
and watch the change below!
NAME NAME
Peter Heidi
Peter and Heidi
3.2
Here's how ...
TEXT(A,"f")
Means: change cell A, formatted according to "f".
Example
=TEXT(E28,"0%")
320% Display contents of E28 as text, formatted as a p
Example
=TEXT(E28,"$0.00")
$3.20 Display contents of E28 as text, formatted in doll
Example
="abc" & E28
Example
="I made "&TEXT(E28,"$0.00")
Example
="Value = "&TEXT(HLOOKUP(4,C2:F7,2),"0.0")
Do this
DO
THIS
Do this
abc3.2 CONCATENATE pieces of text
I made $3.20
Value = 3.2
CHANGE THE CONTENTS OF E28 AND WATCH THE CHANGES
Jan
$569
Use the TEXT command and CONCATENATION to make the cell G46 read:
Sales for (whatever is in cell C44) are (whatever is in cell D44)
CHANGE THE CONTENTS OF C44 AND D44
Here's how it's done:
Err:502
HLOOKUP(X,A:B,N,0)
Looks for x in the FIRST row of array A:B.
If X is 127, you'd get to the 3rd column.
If X is 267, you'd get to the 4th column.
If X is 134, you'd just get to the 1st column.
Okay, having identified the column, this
command now goes to row N and displays
THAT value.
Choose X = 266 (to get column 2) and N=4 (row 4):
134
553
284
251
250
588
267
358
280
386
561
138
=HLOOKUP(266,C52:F57,4 106
Here's another array from D60 to F63
We want Jan sales for week 3 (namely $386)
Jan
=HLOOKUP(3,D60:F63,2)
Feb
moves along the FIRST row to the number "3"
Mar
(meaning "week 3") then down to the 2nd row (giving us "Jan"
But we can also use the MATCH command to get the 2nd row!
=MATCH("Jan",C60:C63,0)
gives
Altogether now!
=HLOOKUP(3,D60:F63,MATCH("Jan",C60:C63,0),0)
DO
THIS
Do this
266
242
120
106
313
462
127
428
580
479
176
365
1
$106
$313
$462
sales).
2
$479
$176
$365
3
$386
$561
$138
$386
(by MATCHING the cells from C60 to C63
gives
$386
Use the HLOOKUP command to pick out the sales THIS week
and THIS month
2
Jan
Put your command here.
It should read
$479
(don't peek in E74 till YOU d
CHANGE THE CONTENTS OF CELLS F71 and/or F72 and WATCH!
an Exercise
1 On the right are a bunch of Years and Growth percent
Generate a command which, when you type in
a Year, will display the Growth
Year here
Your command here
$1,940
2 Type a number in the Year cell (A83). Does it work?
3 Change the format in the Year cell (A83)
so it's a Number with 0 decimal places.
4 Change the format in cell (B83)
so it's a Percentage with 2 decimal places.
5 Change the format in cell (B83)
so it's a Percentage with 2 decimal places.
6 Select all the Growth cells (D80 to D100)
then click on the chart icon and choose a chart type
You'll get a boring gray chart :^(
7 Double-click on the Area and choose a nice colour
for the Border and the Area.
8 Click on the graph itself and see something like:
=SERIES(,,'Lesson-4'!$D$80:$D$100,1)
in the Formula Bar.
Year
Growth
1928
45.90%
1929
16.57%
1930
-13.15%
1931
-44.24%
1932
-48.26%
1933
-25.28%
1934
-17.51%
1935
16.96%
1936
46.67%
1937
-2.02%
1938
29.92%
1939
40.31%
1940
27.40%
1941
11.73%
1942
26.48%
1943
53.55%
1944
78.12%
1945
132.98%
1946
117.37%
1947
124.54%
150.00%
100.00%
50.00%
Co
0.00%
-50.00%
-100.00%
123456789111111111122
012345678901
9 Copy the part: 'Lesson-4'!$D$80:$D$100
1948
132.40%
and paste it between the two commas, like so:
=SERIES(,'Lesson-4'!$D$80:$D$100,'Lesson-4'!$D$80:$D$100,1)
and change the Ds to Cs, like so:
=SERIES(,'Lesson-4'!$C$80:$C$100,'Lesson-4'!$D$80:$D$100,1)
This "Formula" says: SERIES(title,horizontal axis labels, vertical axis numbers,chart #1)
10 Alas, there is no title, so click where the Title goes
then click on cell C107.
put Title Here
=SERIES('Lesson-4'!C107,'Lesson-4'!$C$80:$C$100,'Lesson-4'!$D$80:$D$100,1)
11 Double-click on each axis and change Number or Alignment or ...
put Title Here
12 Double-click on the chart itself and change Patterns.
150.00%
13 Double-click at the edge of the chart and change Patterns.
14 Relax ... then check out
http://home.golden.net/~pjponzo/Excel_charts.htm
100.00%
P.S. Double-click on one of the points on the chart
(or double-click the "area") to see how it's been formatted.
50.00%
-100.00%
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
0.00%
P.S. I forgot. Click on the chart then choose Menu item Chart.
-50.00%
Stick in some Gridlines ... then
double-click on the gridline(s), choose Patterns and change them to light gray.
ping at the first cell whose value matches x.
on of that cell.
se the SECOND cell in F2:F7 matches 2.2
to row m, column n and display that value.
umn 2 of C2:G7. Display the value.
ll matches 2.2 so get value in 2nd cell of C2:C7.
This is cell E28
rmatted according to "f".
s of E28 as text, formatted as a percentage.
s of E28 as text, formatted in dollars and cents.
pieces of text
a Horizontal LOOKUP command !!
(see explanation below)
This is cell G46
Here's an array C52:F57
That's what we get!
week number
See?
TCHING the cells from C60 to C63 with "Jan")
(either 1, 2 or 3)
(either Jan, Feb or Mar)
ur command here.
(don't peek in E74 till YOU do it)
00%
00%
00%
Column D
00%
00%
00%
123456789111111111122
012345678901
-50.00%
-100.00%
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
mbers,chart #1)
$D$100,1)
150.00%
put Title Here
100.00%
50.00%
0.00%
