Excel Formulae01

Description
Excel formulas

Today :
OMs PS 2008
Thursday, June 25, 2015
Today: Thursday, June 25, 2015
Age Calculation Age Calculation
ABS ABS
AND AND
AutoSum shortcut ey AutoSum shortcut ey
A!"#A$" A!"#A$"
B%N2D"C B%N2D"C
Bracets in &ormula Bracets in &ormula
C"%'%N$ C"%'%N$
C"'' C"''
C(A# C(A#
C())S" C())S"
C'"AN C'"AN
C)D" C)D"
C)*B%N C)*B%N
C)NCAT"NAT" C)NCAT"NAT"
C)N!"#T C)N!"#T
C)##"' C)##"'
C)+NT C)+NT
C)+NTA C)+NTA
(ome (ome
C)+NTA C)+NTA
C)+NTB'AN, C)+NTB'AN,
C)+NT%& C)+NT%&
DAT" DAT"
DAT"D%& DAT"D%&
DAT"!A'+" DAT"!A'+"
DA!"#A$" DA!"#A$"
DA- DA-
DA-S./0 DA-S./0
DB DB
DC)+NT DC)+NT
DC)+NTA DC)+NTA
D"C2B%N D"C2B%N
D"C2("0 D"C2("0
D"'TA D"'TA
D$"T D$"T
D*A0 D*A0
D*%N D*%N
D)''A# D)''A#
DS+* DS+*
"DAT" "DAT"
")*)NT( ")*)NT(
"##)#1T-2" "##)#1T-2"
"##)#1T-2" "##)#1T-2"
"!"N "!"N
"0ACT "0ACT
&ACT &ACT
&ilename &ormula &ilename &ormula
&%ND &%ND
&%0"D &%0"D
&'))# &'))#
&)#"CAST &)#"CAST
&#"3+"NC- &#"3+"NC-
$CD $CD
$"ST"2 $"ST"2
("02D"C ("02D"C
(')),+2 (')),+2
()+# ()+#
%& %&
%ND"0 %ND"0
%ND%#"CT %ND%#"CT
%N&) %N&)
%nstant Charts %nstant Charts
%NT %NT
%SB'AN, %SB'AN,
%SB'AN, %SB'AN,
%S"## %S"##
%S"##)# %S"##)#
%S"!"N %S"!"N
%S')$%CA' %S')$%CA'
%SNA %SNA
%SN)NT"0T %SN)NT"0T
%SN+*B"# %SN+*B"#
%S)DD %S)DD
%S#"& %S#"&
%ST"0T %ST"0T
'A#$" 'A#$"
'C* 'C*
'"&T '"&T
'"N '"N
')),+245ector6 ')),+245ector6
')7"# ')7"#
*ATC( *ATC(
*A0 *A0
*"D%AN *"D%AN
*%D *%D
')),+24Array6 ')),+24Array6
*%D *%D
*%N *%N
*%N+T" *%N+T"
**+'T **+'T
*)D *)D
*)D" *)D"
*)NT( *)NT(
*#)+ND *#)+ND
N N
NA NA
N"T7)#,DA-S N"T7)#,DA-S
N)T N)T
N)7 N)7
)DD )DD
)# )#
)rdering Stoc )rdering Stoc
2ercentages 2ercentages
2"#*+T 2"#*+T
2% 2%
2)7"# 2)7"#
2#)D+CT 2#)D+CT
2#)2"# 2#)2"#
3+A#T%'" 3+A#T%'"
3+)T%"NT 3+)T%"NT
#AND #AND
#ANDB"T7""N #ANDB"T7""N
#AN, #AN,
#"2'AC" #"2'AC"
#"2T #"2T
#%$(T #%$(T
#)*AN #)*AN
#)+ND #)+ND
#)+NDD)7N #)+NDD)7N
#)+ND+2 #)+ND+2
S"C)ND S"C)ND
Sho8 All &ormula Sho8 All &ormula
S%$N S%$N
S'N S'N
S*A'' S*A''
S9lit &orename Surname S9lit &orename Surname
STD"! STD"!
STD"! STD"!
STD"!2 STD"!2
S+BST%T+T" S+BST%T+T"
S+* S+*
S+* as #unning Total S+* as #unning Total
S+* using names S+* using names
S+* 8ith )&&S"T S+* 8ith )&&S"T
S+*%& S+*%&
S+*2#)D+CT S+*2#)D+CT
S-D S-D
T T
T"0T T"0T
T%*" T%*"
T%*" Calculation T%*" Calculation
T%*"!A'+" T%*"!A'+"
T)DA- T)DA-
T#ANS2)S" T#ANS2)S"
T#"ND T#"ND
T#%* T#%*
T#+NC T#+NC
T#+NC T#+NC
+22"# +22"#
!A'+" !A'+"
!A# !A#
!A#2 !A#2
!')),+2 !')),+2
7"",DA- 7"",DA-
7)#,DA- 7)#,DA-
-"A# -"A#
-"A#&#AC -"A#&#AC
OMs
Thursday, June 25, 2015
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
YEAFA!
Pa"e #1 o$ 2#0
Start Date En% Date Fraction
1-&an-98 1-A'r-98 0(2# )YEAFA!*!+,D+-
1-&an-98 .1-Dec-98 1 )YEAFA!*!#,D#-
1-&an-98 1-A'r-98 2#/ )YEAFA!*!0,D0-
7hat Does %t Do:
12is $unction calculates t2e %i$$erence 3et4een t4o %ates an% ex'resses t2e result
as a %eci5al $raction(
Synta;
)YEAFA!*StartDate,En%Data,6asis-
6asis 7 De$ines t2e calen%ar syste5 to 3e use% in t2e $unction(
0 7 or o5itte% 8SA style .0 %ays 'er 5ont2 %i9i%e% 3y .00(
1 7 29 or .0 or .1 %ays 'er 5ont2 %i9i%e% 3y .0#(
2 7 29 or .0 or .1 %ays 'er 5ont2 %i9i%e% 3y .00(
. 7 29 or .0 0r .1 %ays 'er 5ont2 %i9i%e% 3y .0#(
+ 7 Euro'ean 29 or .0 or .1 %ays %i9i%e% 3y .00(
&ormatting
12e result 4ill 3e s2o4n as a %eci5al $raction, 3ut can 3e $or5atte% as a 'ercent(
";am9le
12e $ollo4in" ta3le 4as use% 3y a co5'any 42ic2 2ire% 'eo'le on s2ort ter5 contracts
$or a 'art o$ t2e year(
12e Pro ata Salary 42ic2 re'resents t2e annual salary is entere%(
12e Start an% En% %ates o$ t2e contract are entere%(
12e )YEAFA!*- $unction is use% to calculate Actual Salary $or t2e 'ortion o$ t2e year(
Start En% Pro ata Salary Actual Salary
1-&an-98 .1-Dec-98 :12,000 :12,000 )YEAFA!*6.2,!.2;1,+-ee@ !ost
1 :#0
2 :2#
. :2#
+ :.0
# :+0
0 :#0
C :100
1o s2o4 t2e result as t2e na5e o$ t2e %ay, use &ormat, Cells, Custom an% set
t2e Ty9e to ddd or dddd(
Bac Bac
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
FEOO?8P
Pa"e ## o$ 2#0
The column numbers are not needed.
they are part of the illustration.
col 1 col 2 col 3 col 4 col 5 col 6
&an 10 20 .0 +0 #0
Fe3 80 90 100 110 120
Mar 9C 09 +# #1 CC
1y'e a 5ont2 to loo@ $or 7 Fe3
>2ic2 colu5n nee%s to 3e 'ic@e% out 7 +
12e result is 7 100
)FEOO?8P*G11,!07A8,G12,FAESE-
7hat Does %t Do :
12is $unction scans %o4n t2e ro4 2ea%in"s at t2e si%e o$ a ta3le to $in% a s'eci$ie% ite5(
>2en t2e ite5 is $oun%, it t2en scans across to 'ic@ a cell entry(
Synta;
)FEOO?8P*Dte51oFin%,an"e1oEoo@Dn,!olu5n1oPic@Fro5,Sorte%Or8nsorte%-
12e Dte51oFin% is a sin"le ite5 s'eci$ie% 3y t2e user(
12e an"e1oEoo@Dn is t2e ran"e o$ %ata 4it2 t2e ro4 2ea%in"s at t2e le$t 2an% si%e(
12e !olu5n1oPic@Fro5 is 2o4 $ar across t2e ta3le t2e $unction s2oul% loo@ to 'ic@ $ro5(
12e Sorte%H8nsorte% is 42et2er t2e colu5n 2ea%in"s are sorte%( 18E $or yes, FAESE $or no(
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le 1
12is ta3le is use% to $in% a 9alue 3ase% on a s'eci$ie% na5e an% 5ont2(
12e )FEOO?8P*- is use% to scan %o4n to $in% t2e na5e(
12e 'ro3le5 arises 42en 4e nee% to scan across to $in% t2e 5ont2 colu5n(
1o sol9e t2e 'ro3le5 t2e )MA1!A*- $unction is use%(
12e )MA1!A*- loo@s t2rou"2 t2e list o$ na5es to $in% t2e 5ont2 4e reIuire( Dt t2en calculates
t2e 'osition o$ t2e 5ont2 in t2e list( 8n$ortunately, 3ecause t2e list o$ 5ont2s is not as 4i%e
as t2e loo@u' ran"e, t2e )MA1!A*- nu53er is 1 less t2an 4e reIuire, so an% extra 1 is
a%%e% to co5'ensate(
12e )FEOO?8P*- no4 uses t2is )MA1!A*- nu53er to loo@ across t2e colu5ns an%
'ic@s out t2e correct cell entry(
12e )FEOO?8P*- uses FAESE at t2e en% o$ t2e $unction to in%icate to Excel t2at t2e
ro4 2ea%in"s are not sorte%(
&an Fe3 Mar
6o3 10 80 9C
Eric 20 90 09
Alan .0 100 +#
Bac
Bac
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
FEOO?8P
Pa"e #0 o$ 2#0
!arol +0 110 #1
Da9i% #0 120 CC
1y'e a na5e to loo@ $or 7 eric
1y'e a 5ont2 to loo@ $or 7 5ar
12e result is 7 09
)FEOO?8P*F#0,!#07F#+,MA1!A*F#C,D+97F+9,0-;1,FAESE-
";am9le 2
12is exa5'le s2o4s 2o4 t2e )FEOO?8P*- is use% to 'ic@ t2e cost o$ a s'are 'art $or
%i$$erent 5a@es o$ cars(
12e )FEOO?8P*- scans %o4n ro4 2ea%in"s in colu5n F $or t2e s'are 'art entere% in colu5n !(
>2en t2e 5a@e is $oun%, t2e )FEOO?8P*- t2en scans across to $in% t2e 'rice, usin" t2e
result o$ t2e )MA1!A*- $unction to $in% t2e 'osition o$ t2e 5a@e o$ car(
12e $unctions use t2e a3solute ran"es in%icate% 3y t2e %ollar sy53ol ( 12is ensures t2at
42en t2e $or5ula is co'ie% to 5ore cells, t2e ran"es $or )FEOO?8P*- an% )MA1!A*- %o
not c2an"e(
Ma@er S'are !ost Eoo@u' 1a3le
Faux2all D"nition :#0 Faux2all For% F>
F> Gear6ox :000 Gear6ox #00 +#0 000
For% En"ine :1,200 En"ine 1000 1200 800
F> Steerin" :2C# Steerin" 2#0 .#0 2C#
For% D"nition :C0 D"nition #0 C0 +#
For% !YAea% :290 !YAea% .00 290 .10
Faux2all Gear6ox :#00
For% En"ine :1,200
)FEOO?8P*!81,FC#7DC9,MA1!A*681,GC+7DC+,0-;1,FAESE-
";am9le .
Dn t2e $ollo4in" exa5'le a 3uil%ers 5erc2ant is o$$erin" %iscount on lar"e or%ers(
12e 8nit !ost 1a3le 2ol%s t2e cost o$ 1 unit o$ 6ric@, >oo% an% Glass(
12e Discount 1a3le 2ol%s t2e 9arious %iscounts $or %i$$erent Iuantities o$ eac2 'ro%uct(
12e Or%ers 1a3le is use% to enter t2e or%ers an% calculate t2e 1otal(
All t2e calculations ta@e 'lace in t2e Or%ers 1a3le(
12e na5e o$ t2e Dte5 is ty'e% in colu5n ! o$ t2e Or%ers 1a3le(
12e 8nit !ost o$ t2e ite5 is t2en loo@e% u' in t2e 8nit !ost 1a3le(
12e FAESE o'tion 2as 3een use% at t2e en% o$ t2e $unction to in%icate t2at t2e 'ro%uct
na5es %o4n t2e si%e o$ t2e 8nit !ost 1a3le are not sorte%(
8sin" t2e FAESE o'tion $orces t2e $unction to searc2 $or an exact 5atc2( D$ a 5atc2 is
not $oun%, t2e $unction 4ill 'ro%uce an error(
)FEOO?8P*!120,!11+7D110,2,FAESE-
12e %iscount is t2en loo@e% u' in t2e Discount 1a3le
D$ t2e Juantity Or%ere% 5atc2es a 9alue at t2e si%e o$ t2e Discount 1a3le t2e )FEOO?8P 4ill
loo@ across to $in% t2e correct %iscount(
12e 18E o'tion 2as 3een use% at t2e en% o$ t2e $unction to in%icate t2at t2e 9alues
%o4n t2e si%e o$ t2e Discount 1a3le are sorte%(
8sin" 18E 4ill allo4 t2e $unction to 5a@e an a''roxi5ate 5atc2( D$ t2e Juantity Or%ere% %oes
A B C D E F G H I J
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
FEOO?8P
Pa"e #C o$ 2#0
not 5atc2 a 9alue at t2e si%e o$ t2e Discount 1a3le, t2e next lo4est 9alue is use%(
1ryin" to 5atc2 an or%er o$ 12# 4ill %ro' %o4n to 100, an% t2e %iscount $ro5
t2e 100 ro4 is use%(
)FEOO?8P*D120,F11+7D110,MA1!A*!120,G11.7D11.,0-;1,18E-
Discount 1a3le
8nit !ost 1a3le Bric 7ood $lass
Bric :2 1 0/ 0/ 0/
7ood :1 100 0/ ./ 12/
$lass :. .00 8/ #/ 1#/
Or%ers 1a3le
Dte5 8nits 8nit !ost Discount 1otal
6ric@ 100 :2 0/ :188
>oo% 200 :1 ./ :19+
Glass 1#0 :. 12/ :.90
6ric@ 22# :2 0/ :+2.
>oo% #0 :1 0/ :#0
Glass #00 :. 1#/ :1,2C#
For5ula $or 7
8nit !ost )FEOO?8P*!120,!11+7D110,2,FAESE-
Discount )FEOO?8P*D120,F11+7D110,MA1!A*!120,G11.7D11.,0-;1,18E-
1otal )*D120ine 8 :1.0 10 :1.(00 2#/ :10(2#
!2a5'a"ne # :200 0 :..(.. 80/ :00(00
6eer #0 :2+ 12 :2(00 20/ :2(+0
Ea"er 100 :.0 12 :2(#0 2#/ :.(1.
)D.9HE.9 )F.9;F.9N*Nu53er1ooun%,Deci5alPlaces1o8se-
&ormatting
No s'ecial $or5attin" is nee%e%(
Places 1o
oun%
oun%e%
Do4n
Bac
Bac
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
O8ND
Pa"e 92 o$ 2#0
Nu53er
1(+C#89 0 1 )O8ND*!+,D+-
1(+C#89 1 1(# )O8ND*!#,D#-
1(+C#89 2 1(+8 )O8ND*!0,D0-
1.0+.(+C#89 -1 1.0+0 )O8ND*!C,DC-
1.0+.(+C#89 -2 1.000 )O8ND*!8,D8-
1.0+.(+C#89 -. 1+000 )O8ND*!9,D9-
7hat Does %t Do :
12is $unction roun%s a nu53er to a s'eci$ie% a5ount o% %eci5al 'laces(
D$ 0 is use% t2e nu53er is roun%e% to t2e nearest 42ole nu53er(
D$ a ne"ati9e a5ount o$ roun%in" is use% t2e $i"ures to t2e le$t o$ t2e %eci5al 'oint are roun%e%(
Synta;
)O8ND*Nu53er1ooun%,Deci5alPlaces1o8se-
&ormatting
No s'ecial $or5attin" is nee%e%(
Places 1o
oun%
oun%e%
Nu53er
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
OMAN
Pa"e 9. o$ 2#0
Nu53er o5an
1 D )OMAN*!+-
2 DD )OMAN*!#-
. DDD )OMAN*!0-
# F )OMAN*!C-
10 B )OMAN*!8-
1998 M!MB!FDDD )OMAN*!9-
1998 M!MB!FDDD )OMAN*!10,0-
1998 MEMFEDDD )OMAN*!11,1-
1998 MBMFDDD )OMAN*!12,2-
1998 MFMDDD )OMAN*!1.,.-
1998 MFMDDD )OMAN*!1+,+-
1998 MEMFEDDD )OMAN*!1#,18E-
1998 M!MB!FDDD )OMAN*!10,FAESE-
7hat Does %t Do :
12is $unction 'ro%uces a nu53er s2o4n as o5an nu5erals in 9arious $or5ats(
Synta;
)OMAN*Nor5alNu53er,o5anNu53erFor5at-
12e o5anNu53erFor5at can 3e any o$ t2e $ollo4in"(
0 is !lassic( 12is is use% i$ no $or5at is s'eci$ie%(
1 is 5ore !oncise(
2 is e9en 5ore !oncise(
. is e9en 5ore !oncise still(
+ is Si5'li$ie%(
18E is !lassic
FAESE is Si5'li$ie%
&ormatting
No s'ecial $or5attin" is nee%e%(
Note
12ere is no $unction to %o t2e o''osite calculation o$ o5an to nor5al(
Bac Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
DGA1
Pa"e 9+ o$ 2#0
Alan &ones 1 s )DGA1*!+,D+-
Alan &ones 2 es )DGA1*!#,D#-
Alan &ones . nes )DGA1*!0,D0-
!ar%i$$ 0 ar%i$$ )DGA1*!C,DC-
A6!12. + !12. )DGA1*!8,D8-
7hat Does %t Do :
12is $unction %is'lays a s'eci$ie% nu53er o$ c2aracters $ro5 t2e ri"2t 2an% si%e o$ a
'iece o$ text(
Synta;
)DGA1*Ori"inal1ext,Nu53erO$!2aracterseIuire%-
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" ta3le 4as use% to extract t2e secon% na5e o$ a 'erson $ro5 t2eir $ull na5e(
12e )FDND*- $unction locates t2e 'osition o$ t2e s'ace 3et4een t2e $irst an% secon% na5e(
12e len"t2 o$ t2e secon% na5e is calculate% 3y su3tractin" t2e 'osition o$ t2e s'ace $ro5
t2e o9erall len"t2 o$ t2e $ull na5e(
12e )DGA1*- $unction can t2en extract t2e secon% na5e(
Full Na5e Secon% Na5e
Alan &ones &ones )DGA1*!28,EEN*!28--FDND*L L,!28--
6o3 S5it2 S5it2 )DGA1*!29,EEN*!29--FDND*L L,!29--
!arol >illia5s >illia5s )DGA1*!.0,EEN*!.0--FDND*L L,!.0--
Ori"inal
1ext
Nu53er O$
!2aracters
eIuire%
i"2t
Strin"
Bac Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
EP1
Pa"e 9# o$ 2#0
A . AAA )EP1*!+,D+-
A6 . A6A6A6 )EP1*!#,D#-
- 10 ---------- )EP1*!0,D0-
X 10 XXXXXXXXXX )EP1*!C,DC-
7hat Does %t Do :
12is $unction re'eats a 'iece o$ text a s'eci$ie% nu53er o$ ti5es(
You nee% to s'eci$y t2e text to 3e re'eate% an% 2o4 5any ti5es to re'eat it(
Synta;
)EP1*1ext1oe'eat,e'etitions-
12e 5axi5u5 nu53er o$ re'etitions is 200(
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le 1
12e $ollo4in" ta3le 4as use% to %is'lay a si5'le 2isto"ra5 o$ sales $i"ures(
12e )EP1*- $unction uses t2e 9alue o$ Sales, 3ut t2is is %i9i%e% 3y 100 to scale %o4n t2e
nu53er o$ re'etitions to 3elo4 t2e 5axi5u5 o$ 200(
Mont2 Sales
&an :1,000 XXXXXXXXXXXXXXXXXXXX
Fe3 :#,000 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Mar :.,000 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
A'r :2,000 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
)EP1*LXXL,D29H100-
";am9le 2
12e )EP1*- $unction 2as 3een use% to 5a@e a %i"ital %is'lay $or t2e current ti5e(
12e ti5e $unctions o$ )AO8*-, )MDN81E*- an% )SE!OND*- 2a9e 3een use% in conKunction
4it2 t2e )NO>*- as t2e 3asis $or t2e nu53er o$ re'eats(
1o u'%ate t2e cloc@ 'ress t2e $unction @ey F9(
!loc@
Aour XXXXXXXXX 09
Minute XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX +1
Secon% XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX .0
)EP1*LXL,AO8*NO>*---WL LW1EB1*AO8*NO>*--,L00L-
)EP1*LXL,MDN81E*NO>*---WL LW1EB1*MDN81E*NO>*--,L00L-
)EP1*LXL,SE!OND*NO>*---WL LW1EB1*SE!OND*NO>*--,L00L-
1ext 1o
e'eat
Nu53er O$
e'eats
e'eate%
1ext
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
EPEA!E
Pa"e 90 o$ 2#0
Ori"inal 1ext
A6!DEFGA 2 1 x Ax!DEFGA )EPEA!E*!+,D+,E+,F+-
A6!DEFGA 2 # x AxGA )EPEA!E*!#,D#,E#,F#-
A6!DEFGA 2 1 2ello A2ello!DEFGA )EPEA!E*!0,D0,E0,F0-
A6!DEFGA 2 # 2ello A2elloGA )EPEA!E*!C,DC,EC,FC-
7hat Does %t Do :
12is $unction re'laces a 'ortion o$ text 4it2 a ne4 'iece o$ text(
You nee% to s'eci$y 42ere t2e re'lace5ent s2oul% start, 2o4 5any c2aracters to
re5o9e an% 42at t2e ne4 re'lace5ent text s2oul% 3e(
Synta;
)EPEA!E*Ori"inal1ext,StartPosition,Nu53erO$!2aracters1oe'lace,Ne41ext-
&ormatting
No s'ecial $or5attin" is nee%e%(
Start
Position
!2aracters
1o e'lace
Ne4
!2aracter
Mo%i$ie%
1ext
Bac Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
AN?
Pa"e 9C o$ 2#0
Falues
C + )AN?*!+,!+7!8-
+ # )AN?*!#,!+7!8-
2# 1 )AN?*!0,!+7!8-
8 . )AN?*!C,!+7!8-
10 2 )AN?*!8,!+7!8-
Falues
C 2 )AN?*!11,!117!1#,1-
+ 1 )AN?*!12,!117!1#,1-
2# # )AN?*!1.,!117!1#,1-
8 . )AN?*!1+,!117!1#,1-
10 + )AN?*!1#,!117!1#,1-
Falues
10 # )AN?*!18,!187!22-
.0 2 )AN?*!19,!187!22-
20 + )AN?*!20,!187!22-
.0 2 )AN?*!21,!187!22-
+0 1 )AN?*!22,!187!22-
7hat Does %t Do :
12is $unction calculates t2e 'osition o$ a 9alue in a list relati9e to t2e ot2er 9alues in t2e list(
A ty'ical usa"e 4oul% 3e to ran@ t2e ti5es o$ at2letes in a race to $in% t2e 4inner(
12e ran@in" can 3e %one on an ascen%in" *lo4 to 2i"2- or %escen%in" *2i"2 to lo4- 3asis(
D$ t2ere are %u'licate 9alues in t2e list, t2ey 4ill 3e assi"ne% t2e sa5e ran@( Su3seIuent ran@s
4oul% not $ollo4 on seIuentially, 3ut 4oul% ta@e into account t2e $act t2at t2ere 4ere %u'licates(
D$ t2e nu53ers .0, 20, 20 an% 10 4ere ran@e%, .0 is ran@e% as 1, 3ot2 20Vs are ran@e% as 2, an%
t2e 10 4oul% 3e ran@e% as +(
Falue an@
.0 1 )AN?*6.+,6.+76.C-
20 2 )AN?*6.#,6.+76.C-
20 2 )AN?*6.0,6.+76.C-
10 + )AN?*6.C,6.+76.C-
Synta;
)AN?*Nu53er1oan@,EistO$Nu53ers,an@Or%er-
12e an@Or%er can 3e 0 Oero or 1(
8sin" 0 4ill ran@ lar"er nu53ers at t2e to'( *12is is o'tional, lea9in" it out 2as t2e sa5e e$$ect-(
8sin" 1 4ill ran@ s5all nu53ers at t2e to'(
&ormatting
No s'ecial $or5attin" is nee%e%(
an@in" Position
Ai"2 to Eo4
an@in" Position
Eo4 to Ai"2
an@in" Position
Ai"2 to Eo4
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
AN?
Pa"e 98 o$ 2#0
";am9le
12e $ollo4in" ta3le 4as use% to recor% t2e ti5es $or at2letes co5'etin" in a race(
12e )AN?*- $unction 4as t2en use% to $in% t2eir race 'ositions 3ase% u'on t2e $inis2in" ti5es(
At2lete 1i5e ace Position
&o2n 17.0 + )AN?*!#.,!#.7!#8,1-
Alan 17+# 0 )AN?*!#+,!#.7!#8,1-
Da9i% 1702 1 )AN?*!##,!#.7!#8,1-
6rian 17.0 # )AN?*!#0,!#.7!#8,1-
Sue 172C . )AN?*!#C,!#.7!#8,1-
Alex 170. 2 )AN?*!#8,!#.7!#8,1-
A B C D E F G H I
47
48
49
50
51
52
53
54
55
56
57
58
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
AND6E1>EEN
Pa"e 99 o$ 2#0
Eo4 Ai"2 an%o5
# 10 10 )AND6E1>EEN*!+,D+-
1 +9 8 )AND6E1>EEN*!#,D#-
7hat Does %t Do :
12is $unction 'ro%uces a ran%o5 42ole nu53er 3et4een t4o s'eci$ie% nu53ers(
12e ran%o5 nu53er 4ill c2an"e eac2 ti5e t2e s'rea%s2eet is recalculate% or F9 is 'resse%(
Synta;
)ANDOM6E1>EEN*Eo4Ei5it,Ai"2Ei5it-
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" ta3le s2o4s 2o4 t2e )AND6E1>EEN*- 2as 3een use% to "enerate six
nu53ers to use $or t2e National Eottery(
Note t2at t2e $unction %oes not c2ec@ to ensure all nu53ers are uniIue, t2e sa5e nu53er
coul% 3e "enerate% t4ice or 5ore(
Eottery Nu53ers
1 +9 0 )AND6E1>EEN*R!R2+,RDR2+- Nu53er 1
2# )AND6E1>EEN*R!R2+,RDR2+- Nu53er 2
Press $unction ?ey 21 )AND6E1>EEN*R!R2+,RDR2+- Nu53er .
F9 to recalculate( 9 )AND6E1>EEN*R!R2+,RDR2+- Nu53er +
2. )AND6E1>EEN*R!R2+,RDR2+- Nu53er #
22 )AND6E1>EEN*R!R2+,RDR2+- Nu53er 0
2C )AND6E1>EEN*R!R2+,RDR2+- 6onus 3all
All O?
P)DF*S8M*1H!O8N1DF*E2+7E.0,E2+7E.0--YSC,LDu'licatesM S'in a"ainL,LAll O?L-Q
12is $or5ula is use% to %eter5ine 42et2er all t2e nu53ers are %i$$erent(
Dt is entere% as an array usin" !trl;S2i$t;Enter(
12e >innin"
1ic@etM
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
AND
Pa"e 100 o$ 2#0
an%o5 "reater t2an or eIual to 0 3ut less t2an 1(
0(1.09220.1 )AND*-
an%o5 "reater t2an or eIual to 0 3ut less t2an 10
C(+091C8.#+ )AND*-are
2ouse
!osts
Annual
1otal
12e 6est
Or%erin"
Dnter9al
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Or%erin" Stoc@
Pa"e 110 o$ 2#0
+2 +20 : .1,#00 : .1# 9 : 22# : 1,890 : 1,800 -
++ ++0 : ..,000 : ..0 9 : 22# : 1,980 : 1,8C# -
+0 +00 : .+,#00 : .+# 8 : 200 : 2,0C0 : 1,92# -
+8 +80 : .0,000 : .00 8 : 200 : 2,100 : 2,000 -
#0 #00 : .C,#00 : .C# 8 : 200 : 2,2#0 : 2,0C# -
#2 #20 : .9,000 : .90 8 : 200 : 2,.+0 : 2,1#0 -
#+ #+0 : +0,#00 : +0# C : 1C# : 2,+.0 : 2,200 -
#0 #00 : +2,000 : +20 C : 1C# : 2,#20 : 2,2C# -
#8 #80 : +.,#00 : +.# C : 1C# : 2,010 : 2,.#0 -
00 000 : +#,000 : +#0 C : 1C# : 2,C00 : 2,+2# -
Things To Try
!2an"e t2e Discount / to 0/ an% 0/(
!2an"e t2e Or%erin" Dnter9al to 1 or .0(
!2an"e t2e !ost o$ t2e Ex2aust 5a@in" it c2ea'er or 5ore ex'ensi9e(
!2an"e t2e Juantity use% 'er %ay to a lar"er or s5aller nu53er(
";9lanation
!olu5n A )rdering %nter5al %n Days
12e $irst o$ t2ese cells 2as t2e 9alue 1 entere% in it(
12is is t2e s5allest or%erin" 'erio%, 42ic2 4oul% reIuire stoc@ to 3e or%ere% e9ery %ay(
12e secon% cell 'ic@s t2e or%erin" inter9al $ro5 t2e Dn'ut Data ta3le(
12e t2ir% an% su3seIuent cells a%% t2e or%erin" inter9al to t2e 're9ious cell to create
a list o$ 9alues o$ t2e sa5e inter9al(
!olu5n 6 3uantity 2er )rder
12is is t2e nu53er o$ Ex2austs 42ic2 4ill nee% to 3e or%ere%(
!alculation 7 Or%erin"Dnter9al < Juantity8se%PerDay
!olu5n ! )rder !alue
12is is t2e 9alue o$ t2e Or%er 3e$ore any %iscount(
!alculation 7 JuantityOr%ere% < !ostO$Ex2aust
!olu5n D )rder Discount
12e %iscount 42ic2 can 3e su3tracte% $ro5 t2e or%er 9alue(
12e %iscount is only "i9en on or%ers 42ic2 are eIual to or "reater t2an t2e
Price 6rea@ 9alues set 3y t2e su''lier(
!alculation 7 Or%erFalue < Su''lierDiscount
12e su''lier %iscount is calculate% usin" t2e )DF*- an% t2e )AND*- $unctions(
D$ t2e Or%erJuantity is eIual to or a3o9e t2e $irst Price 6rea@, 3ut 3elo4
t2e secon% Price 6rea@, t2en t2e $irst Price 6rea@ %iscount is use%(
D$ t2e Or%erJuantity is eIual to or a3o9e t2e secon% Price 6rea@,
t2e secon% Price 6rea@ %iscount is use%(
D$ t2e Or%erJuantity %oes not Iuali$y $or a %iscount, Oero %iscount is use%(
)!29,B2AFE$E256,E(E2>,DF*629S)RGR2#,RAR2#,0--
)!29O?DAYS
Pa"e 110 o$ 2#0
Start Date En% Date >or@ Days
1-Mar-98 C-Mar-98 # )NE1>O?DAYS*!+,D+-
2#-A'r-98 .0-&ul-98 09 )NE1>O?DAYS*!#,D#-
2+-Dec-98 #-&an-99 9 )NE1>O?DAYS*!0,D0-
7hat Does %t Do:
12is $unction 4ill calculate t2e nu53er o$ 4or@in" %ays 3et4een t4o %ates(
Dt 4ill exclu%e 4ee@en%s an% any 2oli%ays(
Synta;
)NE1>O?DAYS*StartDate,En%Date,Aoli%ays-
Aoli%ays 7 12is is a list o$ %ates 42ic2 4ill 3e exclu%e% $ro5 t2e calculation, suc2 as B5as
an% 6an@ 2oli%ays(
&ormatting
12e result 4ill 3e s2o4n as a nu53er(
Note
12e calculation %oes not inclu%e t2e last %ay( 12e result o$ usin" 1-&an-98 an% #-&an-98 4ill
"i9e a result o$ +( 1o correct t2is a%% 1 to t2e result( )NE1>O?DAYS*Start,En%,Aoli%ays-;1
";am9le
12e $ollo4in" exa5'le s2o4s 2o4 a list o$ Aoli%ays can 3e create%(
Start Date En% Date >or@ Days
Mon 02-Mar-98 Fri 00-Mar-98 # )NE1>O?DAYS*628,!28,!..7!.C-
Mon 02-Mar-98 Fri 1.-Mar-98 10 )NE1>O?DAYS*629,!29,!..7!.C-
Mon 2C-A'r-98 Fri 01-May-98 + )NE1>O?DAYS*6.0,!.0,!..7!.C-
Aoli%ays
6an@ Aoli%ay 1-May-98
B5as 2#-Dec-98
Ne4 Year 1-&an-9C
Ne4 Year 1-&an-98
Ne4 Year 1-&an-99
Bac Bac
A B C D E F
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
NA
Pa"e 11C o$ 2#0
=NHA )NA*-
Falue 1est
10 11 )DF*DS6EAN?*!0-,NA*-,!0;1-
=NHA )DF*DS6EAN?*!C-,NA*-,!C;1-
.0 .1 )DF*DS6EAN?*!8-,NA*-,!8;1-
Sales
Nort2 100
Sout2 =NHA )NA*-
East =NHA )NA*-
>est 200
1otal =NHA )S8M*D117D1+-
7hat Does %t Do :
12is $unction is a 'lace 5ar@er use% to in%icate t2at reIuire% in$or5ation is Not A9aila3le(
Dt can 3e ty'e %irectly in to a cell as )NA*- or it can 3e use% as 'art o$ a calculation(
>2en t2e )NA*- is use%, any calculations 42ic2 %e'en% u'on t2e cell 4ill also s2o4 =NA(
Dt is use% to in%icate t2at all t2e %ata 2as not yet 3een entere% in to t2e s'rea%s2eet(
Synta;
)NA*-
&ormatting
No s'ecial $or5attin" is reIuire%(
";am9le
12e $ollo4in" ta3le 4as use% 3y a co5'any to calculate t2e 5ont2ly >a"e o$ an e5'loyee(
12e Salary an% 1ax 'ercenta"e are entere%(
12e 1ax is t2en %e%ucte% $ro5 t2e Salary to calculate t2e >a"e(
1a3le 1 s2o4s t2at 42en t2e 1ax is not entere%, t2e >a"e is still calculate%(
On a lar"e s'rea%s2eet t2is 5ay "o unnotice% an% t2e 4ron" >a"e 'ai%(
1a3le 1
Salary 1ax / Pay
Alan 1000 2#/ C#0 )!.9-!.92ite
.C,000 2#,#00 21,#00
P)MM8E1*!.27E.2,!207E28-Q
$n all three cells
(o8 %t 7as Done
!ells !.0 to E.0 4ere selecte%(
12e $or5ula )MM8E1*!.27E.2,!207E28- 4as ty'e%, *3ut not yet entere%-(
12e @eys !trl;S2i$t;Enter 4ere 'resse% to con$ir5 t2e entry as an array(
12e $or5ula t2en s2o4e% t2e correct result(
$etting The Dimensions Correct
12e %i5ensions o$ t2e esult ran"e are %irectly relate% to t2e t4o in'ut ran"es(
12e nu53er o$ ro4s in t2e esult s2oul% 3e eIual to t2e ro4s in an"e1(
12e nu53er o$ colu5ns in t2e esult s2oul% 3e eIual to t2e colu5ns in an"e2(
Bac
Bac
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
MM8E1
Pa"e 12# o$ 2#0
";am9le 2
12e $ollo4in" ta3les 4ere use% 3y t2e c2ocolate co5'any to calculate t2e a5ount o$
in"re%ients nee%e% to 'ro%uce 3atc2es o$ c2ocolate(
12e co5'any 2as $our $actories, eac2 o$ 42ic2 2as to or%er enou"2 6utter, E""s an% Su"ar
to ensure t2ey can 5eet 'ro%uction tar"ets(
an"e 1 contains t2e 'lanne% 'ro%uction o$ Mil@ an% Dar@ c2ocolate $or eac2 $actory(
an"e 2 contains t2e a5ount 6utter, E""s an% Su"ar nee%e% to 5a@e 1 unit o$ Mil@ or Plain(
12e esult ran"e s2o4s t2e Iuantities o$ eac2 in"re%ient t2at 4ill 2a9e to 3e or%ere% to
5eet t2e 'ro%uction tar"et(
Note t2e %e't2 o$ t2e esult is t2e sa5e as t2e %e't2 o$ an"e 1, an% t2e 4i%t2 o$
t2e esult is t2e sa5e as t2e 4i%t2 o$ an"e 2(
an"e 1 an"e 2
Pro%uction Mil@ Dar@ Dn"re%ients 6utter E""s Su"ar
Factory 1 20 0 Mil@ 1 . 10
Factory 2 20 1 Dar@ 2 2 #
Factory . 10 #
Factory + 20 10
esult
Dn"re%ients 1o Or%er 6utter E""s Su"ar
Factory 1 20 00 200
Factory 2 22 02 20#
Factory . 20 +0 12#
Factory + +0 80 2#0
P)MM8E1*!097DC2,G097DC0-Q
$n all cells
(int
1o "et a $eel $or 2o4 t2e )MM8E1*- $unction o'erates, set all 9alues in an"e1 an% an"e2
to Oero 0, t2en c2an"e a sin"le 9alue in eac2(
A B C D E F G H I J
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
MDN81E
Pa"e 120 o$ 2#0
Nu53er Minute
0H2#H201# 97+1 +1 )MDN81E*D+-
971#700 PM 1# )MDN81E*D#-
0(02 28 )MDN81E*D0-
0(#2 28 )MDN81E*DC-
1(#2 28 )MDN81E*D8-
7hat Does %t Do:
12e $unction 4ill s2o4 t2e 5inute o$ t2e 2our 3ase% u'on a ti5e or a nu53er(
Only t2e $raction 'art o$ t2e nu53er is use% as it is t2is 42ic2 relates to ti5e o$ %ay(
Synta;
)MDN81E*Nu53er-
&ormatting
12e result 4ill 3e s2o4n as a nor5al nu53er 3et4een 0 an% #9(
";am9le
12e )EP1*- $unction 2as 3een use% to 5a@e a %i"ital %is'lay $or t2e current ti5e(
12e ti5e $unctions o$ )AO8*-, )MDN81E*- an% )SE!OND*- 2a9e 3een use% in conKunction
4it2 t2e )NO>*- as t2e 3asis $or t2e nu53er o$ re'eats(
1o u'%ate t2e cloc@ 'ress t2e $unction @ey F9(
!loc@
Aour XXXXXXXXX 09
Minute XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX +1
Secon% XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX .1
)EP1*LXL,AO8*NO>*---WL LW1EB1*AO8*NO>*--,L00L-
)EP1*LXL,MDN81E*NO>*---WL LW1EB1*MDN81E*NO>*--,L00L-
)EP1*LXL,SE!OND*NO>*---WL LW1EB1*SE!OND*NO>*--,L00L-
#elated %n2at1oEoo@For,>2ere1oEoo@,1y'eO$Matc2-
12e 1y'eO$Matc2 eit2er 0, 1 or -1(
8sin" 0 4ill loo@ $or an exact 5atc2( D$ no 5atc2 is $oun% t2e =NA error 4ill 3e s2o4n(
8sin" 1 4ill loo@ $or an exact 5atc2, or t2e next lo4est nu53er i$ no exact 5atc2 exists(
D$ t2ere is no 5atc2 or next lo4est nu53er t2e error =NA is s2o4n(
12e list o$ 9alues 3ein" exa5ine% 5ust 3e sorte% $or t2is to 4or@ correctly(
8sin" -1 4ill loo@ $or an exact 5atc2, or t2e next 2i"2est nu53er i$ no exact 5atc2 exists(
D$ t2ere is no exact 5atc2 or next 2i"2est nu53er t2e error =NA is s2o4n(
12e list 5ust 3e sorte% $or t2is to 4or@ 'ro'erly(
";am9les 1
8sin" t2e 0 o'tion suita3le $or an exact 5atc2(
Ascen%in" Descen%in" >ron" Falue
10 +0 10
20 .0 20
.0 20 .0
+0 10 +0
20 20 2#
2 . =NHA
)MA1!A*G+#,G+07G+.,0-
";am9le 2
8sin" t2e 1 o'tion suita3le $or a ascen%in" list to $in% an exact or next lo4est 5atc2(
12e Ascending list "i9es t2e exact 5atc2(
12e Descending list "i9es t2e exact 5atc2(
12e 7rong !alue list cannot $in% an exact 5atc2, so t2e =NA is s2o4n(
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
MA1!A
Pa"e 1.+ o$ 2#0
Ascen%in" Descen%in" >ron" Falue
10 +0 10
20 .0 20
.0 20 .0
+0 10 +0
20 20 2#
2 =NHA 2
)MA1!A*G02,G#C7G00,1-
";am9le .
8sin" t2e -1 o'tion suita3le $or a %escen%in" list to $in% an exact or next 2i"2est 5atc2(
Ascen%in" Descen%in" >ron" Falue
10 +0 +0
20 .0 .0
.0 20 20
+0 10 10
20 20 2#
2 . 2
)MA1!A*GC9,GC+7GCC,-1-
";am9le >
12e ta3les 3elo4 4ere use% to 3y a 3us co5'any ta@in" 3oo@in" $or 3us tours(
12ey nee% to allocate a 3us 4it2 enou"2 seats $or t2e all t2e 'assen"ers(
12e list o$ 3us siOes 2as 3een entere% in a list(
12e nu53er o$ 'assen"ers on t2e tour is t2en entere%(
12e )MA1!A*- $unction loo@s %o4n t2e list to $in% t2e 3us 4it2 enou"2 seats(
D$ t2e nu53er o$ 'assen"ers is not an exact 5atc2, t2e next 3i""est 3us 4ill 3e 'ic@e%(
A$ter t2e )MA1!A*- $unction 2as $oun% t2e 3us, t2e )DNDEB*- $unction 2as 3een use%
to loo@ %o4n t2e list a"ain an% 'ic@ out t2e actual 3us siOe reIuire%(
6us SiOe Passen"ers on t2e tour 7 2.
6us 1 #+ 6us siOe nee%e% 7 #0
6us 2 #0 )DNDEB*D9#7D99,MA1!A*A9+,D9#7D99,-1-,0-
6us . 22
6us + 1#
6us # 0
";am9le 5
12e ta3les 3elo4 4ere use% 3y a sc2ool to calculate t2e exa5 "ra%es $or 'u'ils(
12e list o$ "ra%e 3rea@'oints 4as entere% in a list(
12e Ascending list "i9es t2e exact 5atc2(
12e Descending list "i9es t2e =NA error(
12e 7rong !alue list $in%s t2e ne;t lo8est nu53er((
12e Ascending list "i9es t2e =NA error(
12e Descending list "i9es t2e exact 5atc2(
12e 7rong !alue list $in%s t2e ne;t highest nu53er(
A B C D E F G H I
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
MA1!A
Pa"e 1.# o$ 2#0
12e 'u'ils scores 4ere entere% in anot2er list(
12e 'u'ils scores are co5'are% a"ainst t2e 3rea@'oints(
D$ an exact 5atc2 is not $oun%, t2e next lo4est 3rea@'oint is use%(
12e )DNDEB*- $unction t2en loo@s %o4n t2e Gra%e list to $in% t2e "ra%e(
Exa5 Score Gra%e Pu'il Score Gra%e
0 Fail Alan 00 Pass
#0 Pass 6o3 0 Fail
90 Merit !arol 9C Distinction
9# Distinction Da9i% 89 Pass
)DNDEB*D1117D11+,MA1!A*G11+,!1117!11+,1-,0-
A B C D E F G H I
105
106
107
108
109
110
111
112
113
114
115
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
EO>E
Pa"e 1.0 o$ 2#0
8''er !ase 1ext Eo4er !ase
AEAN &ONES alan Kones )EO>E*!+-
6O6 SMD1A 3o3 s5it2 )EO>E*!#-
!AOE >DEEDAMS carol 4illia5s )EO>E*!0-
!ADDFF car%i$$ )EO>E*!C-
A6!12. a3c12. )EO>E*!8-
7hat Does %t Do :
12is $unction con9erts all c2aracters in a 'iece o$ text to lo4er case(
Synta;
)EO>E*1ext1o!on9ert-
&ormatting
No s'ecial $or5attin" is nee%e%(
Bac
Bac
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
EOO?8P *Fector-
Pa"e 1.C o$ 2#0
Na5e &an Fe3 Mar
Alan 10 B0 9C
Bo= 20 A0 09
Carol .0 100 +#
Da5id +0 110 #1
"ric #0 120 CC
&rancis 00 1.0 28
$ail C0 1>0 C.
1y'e a Na5e in t2is cell 7 Eric
120 )EOO?8P*F12,D+7G10,F+7F10-
7hat Does %t Do :
12is $unction loo@s $or a 'iece o$ in$or5ation in a list, an% t2en 'ic@s an ite5 $ro5
a secon% ran"e o$ cells(
Synta;
)EOO?8P*>2at1oEoo@For,an"e1oEoo@Dn,an"e1oPic@Fro5-
12e >2at1oEoo@For s2oul% 3e a sin"le ite5(
12e an"e1oEoo@ in can 3e eit2er 2oriOontal or 9ertical(
12e an"e1oPic@Fro5 5ust 2a9e t2e sa5e nu53er o$ cells in it as t2e an"e1oEoo@in(
6e care$ul not to inclu%e unnecessary 2ea%in" in t2e ran"es as t2ese 4ill cause errors(
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" exa5'le s2o4s 2o4 t2e )EOO?8P*- $unction 4as use% to 5atc2 a na5e ty'e%
in cell G+1 a"ainst t2e list o$ na5es in !.87!+.( >2en a 5atc2 is $oun% t2e )EOO?8P*- t2en
'ic@s $ro5 t2e secon% ran"e E.87&.8(
D$ t2e na5e !arol is use%, t2e 5atc2 is 5a%e in t2e t2ir% cell o$ t2e list o$ na5es, an% t2en
t2e $unction 'ic@s t2e t2ir% cell $ro5 t2e list o$ 9alues(
an"e1oEoo@Dn an"e1oPic@Fro5
Alan # 10 1# 20 2# .0
6o3
!arol
Da9i% 1y'e a na5e 7 !arol
Eric Falue 7 1#
Fre% )EOO?8P*G+1,!.87!+.,E.87&.8-
2ro=lems
12e list o$ in$or5ation to 3e loo@e% t2rou"2 5ust 3e sorte% in ascen%in" or%er, ot2er4ise errors
4ill occur, eit2er as =NHA or incorrect results(
12e &e= 9alue $or t2is 'erson is 7
Bac Bac
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
EOO?8P *Array-
Pa"e 1.8 o$ 2#0
Na5e &an Fe3 Mar
Alan 10 80 AG
Bo= 20 90 /A
Carol .0 100 >5
Da5id +0 110 51
"ric #0 120 GG
&rancis 00 1.0 2B
$ail C0 1+0 G.
1y'e a Na5e in t2is cell 7 Eric
CC )EOO?8P*F12,D+7G10-
7hat Does %t Do :
12is $unction loo@s $or a 'iece o$ in$or5ation in a list, an% t2en 'ic@s an ite5 $ro5 t2e
last cell in t2e a%Kacent ro4 or colu5n(
Dt al4ays 'ic@s t2e %ata $ro5 t2e en% o$ t2e ro4 or colu5n, so it is no "oo% i$ you nee%
to 'ic@ %ata $ro5 'art 4ay across a list, *use FEOO?8P or AEOO?8P-(
12e 4ay in 42ic2 t2e $unction %eci%es 42et2er to 'ic@ $ro5 t2e ro4 or colu5n is 3ase%
on t2e siOe o$ t2e ta3le(
tryin" to $in% a 5atc2 $or t2e 'iece o$ in$or5ation
you as@e% it to loo@ $or(
>2en a 5atc2 is $oun%, t2e $unction 4ill loo@
across to t2e ri"2t 5ost colu5n to 'ic@ t2e
last entry on t2e ro4(
4or@ in Kust t2e sa5e 4ay as i$ t2e ta3le 2a% 5ore
ro4s t2an colu5ns, as in t2e %escri'tion a3o9e(
to $in% a 5atc2 $or t2e 'iece o$ in$or5ation you
2a9e as@e% it to loo@ $or(
>2en a 5atc2 is $oun%, t2e $unction 4ill t2en loo@
%o4n to t2e 3otto5 cell o$ t2e colu5n to 'ic@
t2e last entry o$ t2e colu5n(
Synta;
)EOO?8P*>2at1oEoo@For,an"e1oEoo@Dn-
12e >2at1oEoo@For s2oul% 3e a sin"le ite5(
12e an"e1oEoo@ in can 3e eit2er 2oriOontal or 9ertical(
6e care$ul not to inclu%e unnecessary 2ea%in" in t2e ran"e as t2ese 4ill cause errors(
";am9le 1 ";am9le 2
Dn t2is ta3le t2ere are 5ore Dn t2is ta3le t2ere are 5ore colu5ns t2an ro4s, so
12e *arch 9alue $or t2is 'erson is 7
D$ t2e ta3le 2as 5ore ro8s t2an colu5ns 7 t2e $unction 4ill loo@ do8n t2e le$t 5ost colu5n
D$ t2e ta3le 2as t2e same a5ount o$ ro4s an% colu5ns 7
t2e $unction 4ill loo@ do8n t2e le$t 5ost colu5n an%
D$ t2e ta3le 2as 5ore columns t2an ro4s 7 t2e $unction 4ill loo@ across t2e to' ro4 tryin"
Bac
Bac
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
EOO?8P *Array-
Pa"e 1.9 o$ 2#0
ro4s t2an colu5ns, so t2e t2e ro4 2ea%in" o$ &an is not inclu%e% in t2e
colu5n 2ea%in" o$ &an is loo@u' ran"e(
not inclu%e% in t2e loo@u'
ran"e( Alan 6o3 !arol Da9i%
&an &an 100 100 100 100
Alan 100
6o3 100
!arol 100
Da9i% 100
Eric 100
Fre% 100
&ormatting
No s'ecial $or5attin" is nee%e%(
2ro=lems
12e list o$ in$or5ation to 3e loo@e% t2rou"2 5ust 3e sorte% in ascen%in" or%er, ot2er4ise errors
4ill occur, eit2er as =NHA or incorrect results(
1a3le 1 s2o4s t2e Na5e colu5n sorte% al'2a3etically, t2e results o$ usin" )EOO?8P*- 4ill
3e correct(
1a3le 2 s2o4s t2e sa5e %ata, 3ut not sorte%( So5eti5es t2e results 4ill 3e correct, 3ut ot2er
ti5es t2e result 4ill 3e an =NHA error or incorrect $i"ure(
Ta=le 1 Ta=le 2
Na5e &an Fe3 Mar Na5e &an Fe3 Mar
Alan 10 80 AG Da5id +0 110 51
Bo= 20 90 /A "ric #0 120 GG
Carol .0 100 >5 Alan 10 80 AG
Da5id +0 110 51 Bo= 20 90 /A
"ric #0 120 GG Carol .0 100 >5
&rancis 00 1.0 2B &rancis 00 1.0 2B
$ail C0 1+0 G. $ail C0 1+0 G.
Na5e 7 Eric Na5e 7 Eric
Falue 7 CC Falue 7 CC
)EOO?8P*!88,6807E80- )EOO?8P*A88,G807&80-
A B C D E F G H I J
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
EEN
Pa"e 1+0 o$ 2#0
1ext Een"t2
Alan &ones 10 )EEN*!+-
6o3 S5it2 9 )EEN*!#-
!arol >illia5s 1+ )EEN*!0-
!ar%i$$ C )EEN*!C-
A6!12. 0 )EEN*!8-
7hat Does %t Do :
12is $unction counts t2e nu53er o$ c2aracters, inclu%in" s'aces an% nu53ers, in a 'iece o$ text(
Synta;
)EEN*1ext-
&ormatting
No S'ecial $or5attin" is nee%e%(
";am9le
12is exa5'le s2o4s 2o4 t2e )EEN*- $unction is use% in a $or5ula 42ic2 extracts t2e
secon% na5e $ro5 a text entry containin" 3ot2 $irst an% secon% na5es(
Ori"inal 1ext
!arol >illia5s 0 )FDND*L L,!2+-
12is is t2e 'osition o$ t2e s'ace(
!arol >illia5s 8 )EEN*!2+--FDND*L L,!2+-
12is is t2e len"t2 o$ t2e secon% na5e(
!alculate% 3y ta@in" t2e o9erall len"t2 o$ t2e co5'lete
na5e an% su3tractin" t2e 'osition o$ t2e s'ace(
)DGA1*!2+,EEN*!2+--FDND*L L,!2+--
12is is Kust t2e secon% na5e(
!alculate% 3y usin" t2e )DGA1*- $unction to extract
t2e ri"2t5ost c2aracters u' to t2e len"t2 o$
t2e secon% na5e(
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
EEF1
Pa"e 1+1 o$ 2#0
1ext Ee$t Strin"
Alan &ones 1 A )EEF1*!+,D+-
Alan &ones 2 Al )EEF1*!#,D#-
Alan &ones . Ala )EEF1*!0,D0-
!ar%i$$ 0 !ar%i$ )EEF1*!C,DC-
A6!12. + A6!1 )EEF1*!8,D8-
7hat Does %t Do :
12is $unction %is'lays a s'eci$ie% nu53er o$ c2aracters $ro5 t2e le$t 2an% si%e o$ a
'iece o$ text(
Synta;
)EEF1*Ori"inal1ext,Nu53erO$!2aracterseIuire%-
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" ta3le 4as use% to extract t2e $irst na5e o$ a 'erson $ro5 t2eir $ull na5e(
12e )FDND*- $unction 4as use% to locate 'osition o$ t2e s'ace 3et4een t2e $irst an% secon% na5e(
12e len"t2 o$ t2e $irst na5e is t2ere$ore t2e 'osition o$ t2e s'ace 5inus one c2aracter(
12e )EEF1*- $unction can no4 extract t2e $irst na5e 3ase% on t2e 'osition o$ t2e s'ace(
Full Na5e First Na5e
Alan &ones Alan )EEF1*!2C,FDND*L L,!2C--1-
6o3 S5it2 6o3 )EEF1*!28,FDND*L L,!28--1-
!arol >illia5s !arol )EEF1*!29,FDND*L L,!29--1-
Nu53er O$
!2aracters
eIuire%
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
E!M
Pa"e 1+2 o$ 2#0
Nu53ers
0 20 00 )E!M*!+,D+-
12 18 .0 )E!M*!#,D#-
.+ 90 10.2 )E!M*!0,D0-
7hat Does %t Do :
12is $unction calculate t2e Eeast !o55on Multi'le, 42ic2 is t2e s5allest nu53er
t2at can 3e %i9i%e% 3y eac2 o$ t2e "i9en nu53ers(
Synta;
)E!M*Nu53er1,Nu53er2,Nu53er.((( t2rou"2 to Nu53er29-
&ormatting
No s'ecial $or5attin" is nee%e%(
Eeast
!o55on
Multi'le
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
EAGE
Pa"e 1+. o$ 2#0
Falues Ai"2est Falue 800 )EAGE*!+7!8,1-
120 2n% Ai"2est Falue 2#0 )EAGE*!+7!8,2-
800 .r% Ai"2est Falue 120 )EAGE*!+7!8,.-
100 +t2 Ai"2est Falue 120 )EAGE*!+7!8,+-
120 #t2 Ai"2est Falue 100 )EAGE*!+7!8,#-
2#0
7hat Does %t Do :
12is $unction exa5ines a list o$ 9alues an% 'ic@s t2e 9alue at a user s'eci$ie% 'osition
in t2e list(
Synta;
)EAGE*EistO$Nu53ers1oExa5ine,Position1oPic@Fro5-
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" ta3le 4as use% to calculate t2e to' . sales $i"ures 3et4een &an, Fe3 an% Mar(
Sales &an Fe3 Mar
Nort2 :#,000 :0,000 :+,#00
Sout2 :#,800 :C,000 :.,000
East :.,#00 :2,000 :10,000
>est :12,000 :+,000 :0,000
Ai"2est Falue :12,000 )EAGE*D2+7F2C,1-
2n% Ai"2est Falue :10,000 )EAGE*D2+7F2C,2-
.r% Ai"2est Falue :C,000 )EAGE*D2+7F2C,.-
Note
Anot2er 4ay to $in% t2e Ai"2est an% Eo4est 9alues 4oul% 2a9e 3een to use
t2e )MAB*- an% )MDN*- $unctions(
Ai"2est :12,000 )MAB*D2+7F2C-
Eo4est :2,000 )MDN*D2+7F2C-
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
DS1EB1
Pa"e 1++ o$ 2#0
!ell 1o 1est esult
Aello 1 )DS1EB1*D+-
1 0 )DS1EB1*D#-
2#-Dec-98 0 )DS1EB1*D0-
0 )DS1EB1*DC-
7hat Does %t Do :
12is $unctions tests an entry to %eter5ine 42et2er it is text(
D$ t2e entry is text is s2o4s 18E(
D$ t2e entry is any ot2er ty'e it s2o4s FAESE(
Synta;
)DS1EB1*!ell1o1est-
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" ta3le 4as use% 3y a 'ersonnel %e'art5ent to loo@u' t2e salary o$ an e5'loyee(
12e e5'loyee can 3e entere% as a Na5e or as a Nu5eric 9alue(
12e )DS1EB1*- $unction 2as 3een use% to i%enti$y t2e ty'e o$ entry 5a%e, an% t2en
t2e )DF*- %eci%es 42ic2 FEOO?8P to 'er$or5(
DD No( Na5e Salary
1 Alan :10,000
2 Eric :12,000
. !arol :8,000
+ 6o3 :1#,000
# Da9i% :12,000
1y'e E5'loyee Na5e or DD 7 .
12e Salary is 7 : 8,000
)DF*DS1EB1*E..-,FEOO?8P*E..,D2C7E.1,2,FAESE-,FEOO?8P*E..,!2C7E.1,.,FAESE--
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
DSEF
Pa"e 1+# o$ 2#0
1 )DSEF*A1-
0 )DSEF*699-
=NAMEN )DSEF*Aello-
0 )DSEF*10-
0 )DSEF*NO>*--
0 )DSEF*LA1L-
=NAMEN )DSEF*BB99-
7hat Does %t Do :
12is $unction s2o4s 18E i$ "i9en a cell a%%ress, or FAESE $or any ot2er ty'e o$ 9alue(
Dts a 3it o$ an o%% one, an% is nor5ally use% in 5acros rat2er t2an on t2e 4or@s2eet(
Synta;
)DSEF*Falue1o1est-
12e Falue1o1est can 3e any ty'e o$ %ata, 3ut 42en use% on t2e 4or@s2eet, it cannot 3e a
re$erence to t2e contents o$ anot2er cell, as t2e re$erence 4ill itsel$ 3e e9aluate% 3y t2e $unction(
&ormatting
No s'ecial $or5attin" is nee%e%(
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
DSODD
Pa"e 1+0 o$ 2#0
Nu53er Ds it O%%
1 1 )DSODD*!+-
2 0 )DSODD*!#-
2(# 0 )DSODD*!0-
2(0 0 )DSODD*!C-
.(# 1 )DSODD*!8-
.(0 1 )DSODD*!9-
Aello =FAE8EM )DSODD*!10-
1-Fe3-98 1 )DSODD*!11-
1-Fe3-90 0 )DSODD*!12-
7hat Does %t Do :
12is $unction tests a nu53er to %eter5ine 42et2er it is o%%(
An o%% nu53er is s2o4n as 18E an e9en nu53er is s2o4n as FAESE(
Note t2at %eci5al $ractions are i"nore%(
Note t2at %ates can 3e o%% or e9en(
Note t2at text entries result in t2e =FAE8EM error(
Synta;
)DSODD*!ell1o1est-
&ormatting
No s'ecial $or5attin" is reIuire%(
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
DSN8M6E
Pa"e 1+C o$ 2#0
!ell Entry esult
1 1 )DSN8M6E*D+-
1-&an-98 1 )DSN8M6E*D#-
0 )DSN8M6E*D0-
=DDFH0M 0 )DSN8M6E*DC-
Aello 0 )DSN8M6E*D8-
7hat Does %t Do :
12is $unction exa5ines a cell or calculation to %eter5ine 42et2er it is a nu5eric 9alue(
D$ t2e cell or calculation is a nu5eric 9alue t2e result 18E is s2o4n(
D$ t2e cell or calculation is not nu5eric, or is 3lan@, t2e result FAESE is s2o4n(
Synta;
)DSN8M6E*!ell1o1est-
12e cell to test can 3e a cell re$erence or a calculation(
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" ta3le 4as use% 3y a 'ersonnel %e'art5ent to loo@u' t2e salary o$ an e5'loyee(
12e e5'loyee can 3e entere% as a Na5e or as a Nu5eric 9alue(
12e )DSN8M6E*- $unction 2as 3een use% to i%enti$y t2e ty'e o$ entry 5a%e, an% t2en
t2e )DF*- %eci%es 42ic2 FEOO?8P to 'er$or5(
DD No( Na5e Salary
1 Alan :10,000
2 Eric :12,000
. !arol :8,000
+ 6o3 :1#,000
# Da9i% :12,000
1y'e E5'loyee Na5e or DD 7 eric
12e Salary is 7 : 12,000
)DF*DSN8M6E*E.#-,FEOO?8P*E.#,!297E..,.,FAESE-,FEOO?8P*E.#,D297E..,2,FAESE--
Bac
Bac
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
DSNON1EB1
Pa"e 1+8 o$ 2#0
Dte5 1o 1est Ds Dt A Nu53erN
10 1 )DSNON1EB1*!+-
Aello 0 )DSNON1EB1*!#-
1 )DSNON1EB1*!0-
1-&an-98 1 )DSNON1EB1*!C-
1OO 0 )DSNON1EB1*!8-
7hat Does %t Do:
12is $unctions tests an entry to %eter5ine 42et2er it is a nu53er, rat2er t2an text(
Dt 4oul% 3e use% to ensure t2at only nu5eric entries are use% in calculations, rat2er
t2an text 42ic2 loo@s li@e a nu53er, suc2 as ty'in" t2e letter O instea% o$ Oero 0(
12e $unction is nor5ally use% 4it2 ot2er $unction suc2 as t2e )DF*- $unction(
Synta;
)DSNON1EB1*!ell1o1est-
&ormatting
No s'ecial $or5attin"(
";am9les
12e $ollo4in" ta3le is use% 3y an electrical retailer to calculate t2e sellin" 'rice
o$ an ite5 3ase% on t2e 3uyin" 'rice an% t2e s2o' 5ar@-u'(
1a3le 1 s2o4s t2e =FAE8EM error "enerate% 42en a nu53er, .00, is entere%
usin" t2e letter O instea% o$ t2e Oero 0(
Ta=le 1
Dte5 6uyin" Price Mar@-u' Pro$it
a%io +00 1#0/ 000
1F 800 200/ 1000
Fi%eo .)) 1#0/ =FAE8EM )D.2ee@s 1 2 . +
1 :#00 :.00 :2#0 :200
2 :000 :+00 :.00 :2#0
. :C00 :#00 :.#0 :.00
Ao4 5any 4ee@s reIuire% 7 2
Ao4 5any 'eo'le in t2e 'arty 7 +
!ost 'er 'erson is 7 2#0 )DNDEB*DC7G9,G11,G12-
7hat Does %t Do :
12is $unction 'ic@s a 9alue $ro5 a ran"e o$ %ata 3y loo@in" %o4n a s'eci$ie% nu53er
o$ ro4s an% t2en across a s'eci$ie% nu53er o$ colu5ns(
Dt can 3e use% 4it2 a sin"le 3loc@ o$ %ata, or non-continuos 3loc@s(
Synta;
12ere are 9arious $or5s o$ syntax $or t2is $unction(
Synta; 1
)DNDEB*an"e1oEoo@Dn,!oor%inate-
12is is use% 42en t2e an"e1oEoo@Dn is eit2er a sin"le colu5n or ro4(
12e !o-or%inate in%icates 2o4 $ar %o4n or across to loo@ 42en 'ic@in" t2e %ata $ro5 t2e ran"e(
6ot2 o$ t2e exa5'les 3elo4 use t2e sa5e syntax, 3ut t2e !o-or%inate re$ers to a ro4 42en
t2e ran"e is 9ertical an% a colu5n 42en t2e ran"e is 2oriOontal(
!olours
e%
Green
6lue SiOe Ear"e Me%iu5 S5all
1y'e eit2er 1, 2 or . 7 2 1y'e eit2er 1, 2 or . 7 2
12e colour is 7 Green 12e siOe is 7 Me%iu5
)DNDEB*D.27D.+,D.0- )DNDEB*G.+7D.+,A.0-
Synta; 2
)DNDEB*an"e1oEoo@Dn,o4!oor%inate,!olu5n!olu5n!or%inate-
12is syntax is use% 42en t2e ran"e is 5a%e u' o$ ro4s an% colu5ns(
!ountry !urrency Po'ulation !a'itol
En"lan% Sterlin" #0 M Eon%on
France Franc +0 M Paris
Ger5any DM 00 M 6onn
S'ain Peseta .0 M 6arcelona
1y'e 1,2,. or + $or t2e country 7 2
1y'e 1,2 or . $or statistics 7 .
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
DNDEB
Pa"e 100 o$ 2#0
12e result is 7 Paris )DNDEB*D+#7F+8,F#0,F#1-
Synta; .
)DNDEB*Na5e%an"e1oEoo@Dn,o4!oor%inate,!olu5n!olu5n!or%inate,Area1oPic@Fro5-
8sin" t2is syntax t2e ran"e to loo@ in can 3e 5a%e u' o$ 5ulti'le areas(
12e easiest 4ay to re$er to t2ese areas is to select t2e5 an% "i9e t2e5 a sin"le na5e(
12e Area1oPic@Fro5 in%icates 42ic2 o$ t2e 5ulti'le areas s2oul% 3e use%(
Dn t2e $ollo4in" exa5'le t2e $i"ures $or Nort2 an% Sout2 2a9e 3een na5e% as one
ran"e calle% Nort2An%Sout2(
N)#T( Jtr1 Jtr2 Jtr. Jtr+
6ric@s :1,000 :2,000 :.,000 :+,000
>oo% :#,000 :0,000 :C,000 :8,000
Glass :9,000 :10,000 :11,000 :12,000
S)+T( Jtr1 Jtr2 Jtr. Jtr+
6ric@s :1,#00 :2,#00 :.,#00 :+,#00
>oo% :#,#00 :0,#00 :C,#00 :8,#00
Glass :9,#00 :10,#00 :11,#00 :12,#00
1y'e 1, 2 or . $or t2e 'ro%uct 7 1
1y'e 1, 2, . or + $or t2e Jtr 7 .
1y'e 1 $or Nort2 or 2 $or Sout2 7 2
12e result is 7 Err7#0+ )DNDEB*Nort2An%Sout2,FC0,FCC,FC8-
";am9le
12is is an exten%e% 9ersion o$ t2e 're9ious exa5'le(
Dt allo4s t2e na5es o$ 'ro%ucts an% t2e Iuarters to 3e entere%(
12e )MA1!A*- $unction is use% to $in% t2e ro4 an% colu5n 'ositions o$ t2e na5es entere%(
12ese 'ositions are t2en use% 3y t2e )DNDEB*- $unction to loo@ $or t2e %ata(
"AST 3tr1 3tr2 3tr. 3tr>
Brics :1,000 :2,000 :.,000 :+,000
7ood :#,000 :0,000 :C,000 :8,000
$lass :9,000 :10,000 :11,000 :12,000
7"ST Jtr1 Jtr2 Jtr. Jtr+
6ric@s :1,#00 :2,#00 :.,#00 :+,#00
>oo% :#,#00 :0,#00 :C,#00 :8,#00
Glass :9,#00 :10,#00 :11,#00 :12,#00
1y'e 1, 2 or . $or t2e 'ro%uct 7 8ood
1y'e 1, 2, . or + $or t2e Jtr 7 Htr2
1y'e 1 $or Nort2 or 2 $or Sout2 7 8est
12e result is 7 Err7#0+
)DNDEB*EastAn%>est,MA1!A*F100,!917!9.,0-,MA1!A*F101,D907G90,0-,DF*F102)!90,1,DF*F102)!9#,2---
A B C D E F G H I
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
DF
Pa"e 101 o$ 2#0
Na5e Sales 1ar"et esult
Alan 1000 #000 Not Ac2ie9e% )DF*!+S)D+,LAc2ie9e%L,LNot Ac2ie9e%L-
6o3 0000 #000 Ac2ie9e% )DF*!#S)D#,LAc2ie9e%L,LNot Ac2ie9e%L-
!arol 2000 +000 Not Ac2ie9e% )DF*!0S)D0,LAc2ie9e%L,LNot Ac2ie9e%L-
7hat Does %t Do:
12is $unction tests a con%ition(
D$ t2e con%ition is 5et it is consi%ere% to 3e 18E(
D$ t2e con%ition is not 5et it is consi%ere% as FAESE(
De'en%in" u'on t2e result, one o$ t4o actions 4ill 3e carrie% out(
Synta;
)DF*!on%ition,ActionD$1rue,ActionD$False-
12e !on%ition is usually a test o$ t4o cells, suc2 as A1)A2(
12e ActionD$1rue an% ActionD$False can 3e nu53ers, text or calculations(
&ormatting
No s'ecial $or5attin" is reIuire%(
";am9le 1
12e $ollo4in" ta3le s2o4s t2e Sales $i"ures an% 1ar"ets $or sales re's(
Eac2 2as t2eir o4n tar"et 42ic2 t2ey 5ust reac2(
12e )DF*- $unction is use% to co5'are t2e Sales 4it2 t2e 1ar"et(
D$ t2e Sales are "reater t2an or eIual to t2e 1ar"et t2e result o$ Ac2ie9e% is s2o4n(
D$ t2e Sales %o not reac2 t2e tar"et t2e result o$ Not Ac2ie9e% is s2o4n(
Note t2at t2e text use% in t2e )DF*- $unction nee%s to 3e 'lace% in %ou3le Iuotes LAc2ie9e%L(
Na5e Sales 1ar"et esult
Alan 1000 #000 Not Ac2ie9e% )DF*!.1S)D.1,LAc2ie9e%L,LNot Ac2ie9e%L-
6o3 0000 #000 Ac2ie9e% )DF*!.2S)D.2,LAc2ie9e%L,LNot Ac2ie9e%L-
!arol 2000 +000 Not Ac2ie9e% )DF*!..S)D..,LAc2ie9e%L,LNot Ac2ie9e%L-
";am9le 2
12e $ollo4in" ta3le is si5ilar to t2at in Exa5'le 1(
12is ti5e t2e !o55ission to 3e 'ai% to t2e sales re' is calculate%(
D$ t2e Sales are "reater t2an or eIual to t2e 1ar"et, t2e !o55ission is 10/ o$ Sales(
D$ t2e Sales %o not reac2 1ar"et, t2e !o55ission is only #/ o$ Sales(
Na5e Sales 1ar"et !o55ission
Alan 1000 #000 #0 )DF*!+.S)D+.,!+.oo% an% Glass(
12e Discount 1a3le 2ol%s t2e 9arious %iscounts $or %i$$erent Iuantities o$ eac2 'ro%uct(
12e Or%ers 1a3le is use% to enter t2e or%ers an% calculate t2e 1otal(
All t2e calculations ta@e 'lace in t2e Or%ers 1a3le(
12e na5e o$ t2e Dte5 is ty'e% in colu5n !(
12e 8nit !ost o$ t2e ite5 is t2en loo@e% u' in t2e 8nit !ost 1a3le(
12e FAESE o'tion 2as 3een use% at t2e en% o$ t2e $unction to in%icate t2at t2e 'ro%uct
na5es across t2e to' o$ t2e 8nit !ost 1a3le are not sorte%(
8sin" t2e FAESE o'tion $orces t2e $unction to searc2 $or an exact 5atc2( D$ a 5atc2 is
not $oun%, t2e $unction 4ill 'ro%uce an error(
)AEOO?8P*!12C,E1117G112,2,FAESE-
12e %iscount is t2en loo@e% u' in t2e Discount 1a3le
D$ t2e Juantity Or%ere% 5atc2es a 9alue at t2e to' o$ t2e Discount 1a3le t2e )AEOO?8P 4ill
loo@ %o4n t2e colu5n to $in% t2e correct %iscount(
12e 18E o'tion 2as 3een use% at t2e en% o$ t2e $unction to in%icate t2at t2e 9alues
across t2e to' o$ t2e Discount 1a3le are sorte%(
8sin" 18E 4ill allo4 t2e $unction to 5a@e an a''roxi5ate 5atc2( D$ t2e Juantity Or%ere% %oes
not 5atc2 a 9alue at t2e to' o$ t2e Discount 1a3le, t2e next lo4est 9alue is use%(
1ryin" to 5atc2 an or%er o$ 12# 4ill %ro' %o4n to 100, an% t2e %iscount $ro5
A B C D E F G H I J
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
AEOO?8P
Pa"e 100 o$ 2#0
t2e 100 colu5n is use%(
)AEOO?8P*D12C,E11#7G118,MA1!A*!12C,D1107D118,0-;1,18E-
8nit !ost 1a3le
Bric 7ood $lass
:2 :1 :.
Discount 1a3le
1 100 .00
Bric 0/ 0/ 8/
7ood 0/ ./ #/
$lass 0/ 12/ 1#/
Or%ers 1a3le
Dte5 8nits 8nit !ost Discount 1otal
6ric@ 100 :2 0/ :188
>oo% 200 :1 ./ :19+
Glass 1#0 :. 12/ :.90
6ric@ 22# :2 0/ :+2.
>oo% #0 :1 0/ :#0
Glass #00 :. 1#/ :1,2C#
8nit !ost )AEOO?8P*!12C,E1117G112,2,FAESE-
Discount )AEOO?8P*D12C,E11#7G118,MA1!A*!12C,D1107D118,0-;1,18E-
A B C D E F G H I J
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
AEB2DE!
Pa"e 10C o$ 2#0
Aexa%eci5al Deci5al Nu53er
0 0 )AEB2DE!*!+-
1 1 )AEB2DE!*!#-
2 2 )AEB2DE!*!0-
. . )AEB2DE!*!C-
1A 20 )AEB2DE!*!8-
16 2C )AEB2DE!*!9-
CFFFFFFFFF #+9,C##,81.,88C )AEB2DE!*!10-
8000000000 -#+9,C##,81.,888 )AEB2DE!*!11-
FFFFFFFFFF -1 )AEB2DE!*!12-
FFFFFFFFFE -2 )AEB2DE!*!1.-
FFFFFFFFFD -. )AEB2DE!*!1+-
7hat Does %t Do :
12is $unction con9erts a 2exa%eci5al nu53er to its %eci5al eIui9alent(
Synta;
)AEB2DE!*AexaDeci5alNu53er-
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" ta3le 4as use% to a%% t4o 2exa%eci5al 9alues to"et2er(
Aexa%eci5al
Falue 1 F
Falue 2 1A
esult 29 )DE!2AEB*AEB2DE!*!29-;AEB2DE!*!.0--
Bac
Bac
A B C D E F G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
GES1EP
Pa"e 108 o$ 2#0
Nu53er1 Nu53er2 GES1EP
10 20 0 )GES1EP*!+,D+-
#0 20 1 )GES1EP*!#,D#-
99 100 0 )GES1EP*!0,D0-
100 100 1 )GES1EP*!C,DC-
101 100 1 )GES1EP*!8,D8-
2 1 )GES1EP*!9,D9-
2 0 )GES1EP*!10,D10-
7hat Does %t Do :
12is $unction test a nu53er to see i$ it is "reater t2an or eIual to anot2er nu53er(
D$ t2e nu53er is "reater t2an or eIual, t2e result o$ 1 4ill 3e s2o4n, ot2er4ise 0 is s2o4n(
Synta;
)GES1EP*Nu53er1o1est,Nu53er1o1estA"ainst-
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" ta3le 4as use% to calculate 2o4 5any sales sta$$ ac2ie9e% t2eir tar"ets(
12e )GES1EP*- $unction co5'ares t2e Sales 4it2 1ar"et, an% t2e results are totalle%(
Na5e Sales 1ar"et GES1EP
Alan :.,000 :+,000 0 )GES1EP*D2C,E2C-
6o3 :#,000 :+,000 1 )GES1EP*D28,E28-
!arol :1,000 :2,000 0 )GES1EP*D29,E29-
Da9i% :2,000 :2,000 1 )GES1EP*D.0,E.0-
Eric :8,000 :C,000 1 )GES1EP*D.1,E.1-
1ar"ets Ac2ie9e% . )S8M*F2C7F.1-
Bac
Bac
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
G!D
Pa"e 109 o$ 2#0
Nu53ers
0 1# . )G!D*!+,D+-
28 +9 C )G!D*!#,D#-
# 99 1 )G!D*!0,D0-
Nu53ers
18 C2 90 0 )G!D*!9,D9,E9-
.00 #00 200 100 )G!D*!10,D10,E10-
2(# + 0 0(# )G!D*!11,D11,E11-
7hat Does %t Do :
12is $unction calculates t2e lar"est nu53er 42ic2 can 3e use% to %i9i%e% all t2e
9alues s'eci$ie%(
12e result is al4ays a 42ole nu53er(
>2ere t2ere is no co55on %i9isor t2e 9alue o$ 1 is use%(
Deci5al $ractions are i"nore%(
Synta;
)G!D*Nu53er1,Nu53er2,Nu53er.((( t2rou"2 to Nu53er29-
&ormatting
No s'ecial $or5attin" is nee%e%(
Greatest
Di9isor
Greatest
Di9isor
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
FEJ8EN!Y
Pa"e 1C0 o$ 2#0
&an Fe3 Mar
Nort2 :#,000 :0,000 :+,#00
Sout2 :#,800 :C,000 :.,000
East :.,#00 :2,000 :10,000
>est :12,000 :+,000 :0,000
Sales :+,000 an% 3elo4( :+,000 + P)FEJ8EN!Y*D+7FC,E97E11-Q
Sales a3o9e :+,000 u' to :0,000 :0,000 # P)FEJ8EN!Y*D+7FC,E97E11-Q
Sales a3o9e :0,000 :999,999 . P)FEJ8EN!Y*D+7FC,E97E11-Q
7hat Does %t Do :
12is $unction co5'ares a ran"e o$ %ata a"ainst a list o$ inter9als(
12e result s2o4s 2o4 5any ite5s in t2e ran"e o$ %ata $all 3et4een t2e inter9als(
12e $unction is entere% in t2e cells as an array, t2at is 42y it is enclose% in P Q 3races(
Synta;
)FEJ8EN!Y*an"eO$Data,EistO$Dnter9als-
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le 1
12e $ollo4in" ta3les 4ere use% to recor% t2e 4ei"2t o$ a "rou' o$ c2il%ren(
12e )FEJ8EN!Y*- $unction 4as t2en use% to calculate t2e nu53er o$ c2il%ren 42ose
4ei"2ts $ell 3et4een s'eci$ie% inter9als(
>ei"2t ?" Nu53er O$ !2il%ren7
!2il% 1 20(+C 6et4een 0 - 1# ?" 2
!2il% 2 22(8. A3o9e 1# 3ut less t2an or eIual to 20 ?" +
!2il% . 1#(C+ A3o9e 20 ?" .
!2il% + 10(80 P)FEJ8EN!Y*!.07!.8,!+17!+.-Q
!2il% # 8(28 P)FEJ8EN!Y*!.07!.8,!+17!+.-Q
!2il% 0 20(00 P)FEJ8EN!Y*!.07!.8,!+17!+.-Q
!2il% C 1C(.0
!2il% 8 10(0C
!2il% 9 18(01
?" >ei"2t Dnter9als
1#
20
100
";am9le 2
12is exa5'le uses c2aracters instea% o$ 9alues(
A restaurant 2as as@e% +0 custo5ers $or t2eir ratin" o$ t2e $oo% in t2e restaurant(
12e ratin"s 4ere entere% into a ta3le as a sin"le letter, E, F, A, P or D(
12e 5ana"er no4 4ants to calculate 2o4 5any res'onses $ell into eac2 cate"ory(
8n$ortunately, t2e )FEJ8EN!Y*- $unction i"nores text entries, so 2o4 can t2e $reIuency
o$ text 3e calculate%N
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
FEJ8EN!Y
Pa"e 1C1 o$ 2#0
12e ans4er is to use t2e )!ODE*- an% )8PPE*- $unctions(
12e )8PPE*- $orces all t2e text entries to 3e consi%ere% as ca'ital letters(
12e )!ODE*- $unction calculates t2e uniIue ANSD co%e $or eac2 c2aracter(
As t2is co%e is a nu5eric 9alue, t2e )FEJ8EN!Y*- $unction can t2en 3e use%M
atin" FreIuency
Excellent E 0 P)FEJ8EN!Y*!ODE*8PPE*60C7DC1--,!ODE*8PPE*!007!0+---Q
Fery Goo% F 8 P)FEJ8EN!Y*!ODE*8PPE*60C7DC1--,!ODE*8PPE*!007!0+---Q
A9era"e A 9 P)FEJ8EN!Y*!ODE*8PPE*60C7DC1--,!ODE*8PPE*!007!0+---Q
Poor P 8 P)FEJ8EN!Y*!ODE*8PPE*60C7DC1--,!ODE*8PPE*!007!0+---Q
Dis"ustin" D 9 P)FEJ8EN!Y*!ODE*8PPE*60C7DC1--,!ODE*8PPE*!007!0+---Q
!usto5er atin"s
F D F A ' A D D
F P a D A P F %
A F E P ' E D A
A E % F D P a E
F e P P A F E D
A B C D E F G H I
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
FOE!AS1
Pa"e 1C2 o$ 2#0
Mont2 Sales
1 :1,000
2 :2,000
. :2,#00
+ :.,#00
# :.,800
0 :+,000
1y'e t2e 5ont2 nu53er to 're%ict 7 12
12e Forecast sales $i"ure is 7 :C,99C )FOE!AS1*E11,F+7F9,E+7E9-
7hat Does %t Do :
12is $unction uses t4o sets o$ 9alues to 're%ict a sin"le 9alue(
12e 're%icte% 9alue is 3ase% on t2e relations2i' 3et4een t2e t4o ori"inal sets o$ 9alues(
D$ t2e 9alues are sales $i"ures $or 5ont2s 1 to 0, *&an to &un-, you can use t2e $unction
to 're%ict 42at t2e sales $i"ure 4ill 3e in any ot2er 5ont2(
12e 4ay in 42ic2 t2e 're%iction is calculate% is 3ase% u'on t2e assu5'tion o$ a Einear 1ren%(
Synta;
)FOE!AS1*Dte51oFore!ast,an"eY,an"eB-
Dte51oForecast is t2e 'oint in t2e $uture, *or 'ast-, $or 42ic2 you nee% t2e $orecast(
an"eY is t2e list o$ 9alues 42ic2 contain t2e 2istorical %ata to 3e use% as t2e 3asis
o$ t2e $orecast, suc2 as Sales $i"ures(
an"eB is t2e inter9als use% 42en recor%in" t2e 2istorical %ata, suc2 as Mont2 nu53er(
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" ta3le 4as use% 3y a co5'any consi%erin" ex'ansion o$ t2eir sales tea5(
12e SiOe an% Per$or5ance o$ t2e 're9ious tea5s o9er a 'erio% o$ t2ree years 4ere entere%(
12e siOe o$ t2e Ne4 Sales tea5 is entere%(
12e )FOE!AS1*- $unction is use% to calculate t2e 're%icte% 'er$or5ance $or t2e ne4 sales
tea5 3ase% u'on a linear tren%(
Year
1990 10 :#,000
199C 20 :8,000
1998 .0 :8,#00
SiOe O$ 12e Ne4 Sales 1ea5 7 +0
Esti5ate% Forecast O$ Per$or5ance 7 :10,00C )FOE!AS1*E+.,E.97E+1,D.97D+1-
SiOe O$
Sales 1ea5
?no4n
Per$or5ance
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
FEOO
Pa"e 1C. o$ 2#0
Nu53er oun%e% Do4n
1(# 1 )FEOO*!+,1-
2(. 2 )FEOO*!#,1-
2(9 2 )FEOO*!0,1-
12. 100 )FEOO*!C,#0-
1+# 100 )FEOO*!8,#0-
1C# 1#0 )FEOO*!9,#0-
7hat Does %t Do :
12is $unction roun%s a 9alue %o4n to t2e nearest 5ulti'le s'eci$ie% 3y t2e user(
Synta;
)FEOO*Nu53er1ooun%,Si"ni$icantFalue-
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" ta3le 4as use% to calculate co55ission $or 5e53ers o$ a sales tea5(
!o55ission is only 'ai% $or e9ery :1000 o$ sales(
12e )FEOO*- $unction 2as 3een use% to roun% %o4n t2e Actual Sales to t2e
nearest 1000, 42ic2 is t2en use% as t2e 3asis $or !o55ission(
Na5e Actual Sales ele9ant Sales !o55ission
Alan :2.,#00 :2.,000 :2.0
6o3 :#0,890 :#0,000 :#00
!arol :18,12# :18,000 :180
)FEOO*D29,1000-
Bac
Bac
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
FDBED
Pa"e 1C+ o$ 2#0
10 10(00 )FDBED*!+-
10 10 )FDBED*!#,0-
10 10(0 )FDBED*!0,1-
10 10(00 )FDBED*!C,2-
10(2# 10(2# )FDBED*!8-
10(2# 10 )FDBED*!9,0-
10(2# 10(. )FDBED*!10,1-
10(2# 10(2# )FDBED*!11,2-
1000 1,000(00 )FDBED*!12-
1000(2. 1,000 )FDBED*!1.,0-
1000(2. 1000 )FDBED*!1+,0,18E-
7hat Does %t Do :
12is $unction con9erts a nu5eric 9alue to text(
Durin" t2e con9ersion t2e 9alue can 3e roun%e% to a s'eci$ic nu53er o$ %eci5al 'laces,
an% co55as can 3e inserte% at t2e 1,000Vs(
Synta;
)FDBED*Nu53er1o!on9ert,Deci5alPlaces,!o55as-
D$ Deci5alPlaces 'laces is not s'eci$ie% t2e $unction 4ill assu5e 2(
12e !o55as o'tion can 3e 18E $or co55as or FAESE $or no co55as(
D$ t2e !o55as is not s'eci$ie% t2e $unction 4ill assu5e 18E(
&ormatting
No s'ecial $or5attin" is nee%e%(
Note t2at any $urt2er $or5attin" 4it2 t2e For5at, !ells, Nu53er co55an% 4ill not 2a9e any e$$ect(
Ori"inal
Nu53er
!on9erte%
1o 1ext
Bac
Bac
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
FDND
Pa"e 1C# o$ 2#0
1ext Eetter 1o Fin% Position O$ Eetter
Aello e 2 )FDND*D+,!+-
Aello A 1 )FDND*D#,!#-
Aello o # )FDND*D0,!0-
Alan >illia5s a . )FDND*DC,!C-
Alan >illia5s a 11 )FDND*D8,!8,0-
Alan >illia5s 1 =FAE8EM )FDND*D9,!9-
7hat Does %t Do:
12is $unction loo@s $or a s'eci$ie% letter insi%e anot2er 'iece o$ text(
>2en t2e letter is $oun% t2e 'osition is s2o4n as a nu53er(
D$ t2e text contains 5ore t2an one re$erence to t2e letter, t2e $irst occurrence is use%(
An a%%itional o'tion can 3e use% to start t2e searc2 at a s'eci$ic 'oint in t2e text, t2us
ena3lin" t2e searc2 to $in% %u'licate occurrences o$ t2e letter(
D$ t2e letter is not $oun% in t2e text, t2e result =FAE8E is s2o4n(
Synta;
)FDND*Eetter1oEoo@For,1ext1oEoo@Dnsi%e,StartPosition-
Eetter1oEoo@For 7 12is nee%s to 3e a sin"le c2aracter(
1ext1oEoo@Dnsi%e 7 12is is t2e 'iece o$ text to 3e searc2e% t2rou"2(
StartPosition 7 12is is o'tional, it s'eci$ies at 42ic2 'oint in t2e text t2e searc2 s2oul% 3e"in(
&ormatting
No s'ecial $or5attin" is nee%e%, t2e result 4ill 3e s2o4n as a nu53er(
Bac
Bac
A B C D E F G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
FA!1
Pa"e 1C0 o$ 2#0
Nu53er Factorial
. 0 )FA!1*!+-
.(# 0 )FA!1*!#-
# 120 )FA!1*!0-
10 .,028,800 )FA!1*!C-
20 2,+.2,902,008,1C0,0+0,000 )FA!1*!8-
7hat Does %t Do :
12is $unction calculates t2e $actorial o$ a nu53er(
12e $actorial is calculate% as 1atta"e
6ul3 100
12e lo4est Falue O$ Stoc@ is 7 :12(#0 )DMDN*6.7D19,LFalue O$ Stoc@L,E007F01-
The lo8est !alue )< Stoc o< a Bul= =et8een t8o 7attage 5alues1
Pro%uct >atta"e >atta"e
6ul3 S)80 Y)100
12e lo4est Falue O$ Stoc@ is 7 :12(00 )DMDN*6.7D19,LFalue O$ Stoc@L,E0C7G08-
A B C D E F G H I J K
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
DMAB
Pa"e 18C o$ 2#0
2roduct 7attage Brand +nit Cost
6ul3 200 .000 AoriOon :+(#0 + . :#+(00
Neon 100 2000 AoriOon :2(00 1# 2 :00(00
S'ot 00 :0(00
Ot2er 10 8000 Sun3ea5 :0(80 2# 0 :120(00
6ul3 80 1000 AoriOon :0(20 +0 . :2+(00
S'ot 100 un@no4n AoriOon :1(2# 10 + :#0(00
S'ot 200 .000 AoriOon :2(#0 1# 0 :0(00
Ot2er 2# un@no4n Sun3ea5 :0(#0 10 . :1#(00
6ul3 200 .000 Sun3ea5 :#(00 . 2 :.0(00
Neon 100 2000 Sun3ea5 :1(80 20 # :180(00
6ul3 100 un@no4n Sun3ea5 :0(2# 10 # :12(#0
6ul3 10 800 AoriOon :0(20 2# 2 :10(00
6ul3 00 1000 Sun3ea5 :0(1# 2# 0 :0(00
6ul3 80 1000 Sun3ea5 :0(20 .0 2 :12(00
6ul3 100 2000 AoriOon :0(80 10 # :+0(00
6ul3 +0 1000 AoriOon :0(10 20 # :10(00
1o calculate lar"est Falue O$ Stoc@ o$ a 'articular 6ran% o$ 3ul3(
6ran%
1y'e t2e 3ran% na5e 7 AoriOon
12e MAB 9alue o$ AoriOon is 7 :00(00 )DMAB*6.7D19,D.,E2.7E2+-
7hat Does %t Do :
12is $unction exa5ines a list o$ in$or5ation an% 'ro%uces t2e lar"est 9alue $ro5 a s'eci$ie% colu5n(
Synta;
)DMAB*Data3asean"e,Fiel%Na5e,!riteriaan"e-
$iel% na5es at t2e to' o$ t2e colu5ns(
12e $irst set o$ in$or5ation is t2e na5e, or na5es, o$ t2e Fiel%s*s- to 3e use% as t2e 3asis
$or selectin" t2e recor%s, suc2 as t2e cate"ory 6ran% or >atta"e(
12e secon% set o$ in$or5ation is t2e actual recor%, or recor%s, 42ic2 are to 3e selecte%, suc2
as AoriOon as a 3ran% na5e, or 100 as t2e 4atta"e(
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9les
The largest !alue )< Stoc o< a 9articular 2roduct o< a 9articular Brand1
Pro%uct 6ran%
6ul3 sun3ea5
12e lar"est 9alue is 7 :.0(00 )DMAB*6.7D19,D.,E+97F#0-
12is is t2e sa5e calculation 3ut usin" t2e na5e LFalue O$ Stoc@L instea% o$ t2e cell a%%ress(
This is the Database ran"e.
'iin%o4 1-&un-98 #0
1yres 1-A'r-98 200
1yres 1-Mar-98 100
!lutc2 1-May-98 2#0
Ao4 5any 6ra@e S2oes Aa9e 3een 3ou"2t( 2 )!O8N1DF*!+7!12,L6ra@esL-
Ao4 5any 1yres 2a9e 3een 3ou"2t( . )!O8N1DF*!+7!12,L1yresL-
Ao4 5any ite5s cost :100 or a3o9e( # )!O8N1DF*E+7E12,LS)100L-
1y'e t2e na5e o$ t2e ite5 to count( ser9ice 2 )!O8N1DF*!+7!12,E18-
7hat Does %t Do :
12is $unction counts t2e nu53er o$ ite5s 42ic2 5atc2 criteria set 3y t2e user(
Synta;
)!O8N1DF*an"eO$12in"s1o6e!ounte%,!riteria1o6eMatc2e%-
12e criteria can 3e ty'e% in any o$ t2e $ollo4in" 4ays(
&ormatting
No s'ecial $or5attin" is nee%e%(
1o 5atc2 a s'eci$ic nu53er ty'e t2e nu53er, suc2 as )!O8N1DF*A17A#,100-
1o 5atc2 a 'iece o$ text ty'e t2e text in Iuotes, suc2 as )!O8N1DF*A17A#,I(elloI-
1o 5atc2 usin" o'erators surroun% t2e ex'ression 4it2 Iuotes, suc2 as )!O8N1DF*A17A#,ID100I-
Bac
Bac
A B C D E F G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
!O8N16EAN?
Pa"e 209 o$ 2#0
an"e 1o 1est 6lan@s
1 2 )!O8N16EAN?*!+7!11-
Aello
.
0
1-&an-98
#
7hat Does %t Do :
12is $unction counts t2e nu53er o$ 3lan@ cells in a ran"e(
Synta;
)!O8N16EAN?*an"e1o1est-
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" ta3le 4as use% 3y a co5'any 42ic2 4as 3allotin" its 4or@ers on 42et2er
t2e co5'any s2oul% 2a9e a no s5o@in" 'olicy(
Eac2 o$ t2e %e'art5ents in t2e 9arious $actories 4ere Iuestione%(
12e res'onse to t2e Iuestion coul% 3e Y or N(
As t2e results o$ t2e 9ote 4ere collate% t2ey 4ere entere% in to t2e ta3le(
12e )!O8N16EAN?*- $unction 2as 3een use% to calculate t2e nu53er o$ %e'art5ents 42ic2
2a9e no yet re"istere% a 9ote(
A%5in Accounts Pro%uction Personnel
Factory 1 Y N
Factory 2 Y Y N
Factory .
Factory + N N N
Factory # Y Y
Factory 0 Y Y Y N
Factory C N Y
Factory 8 N N Y Y
Factory 9 Y
Factory 10 Y N Y
Fotes not 9et re"istere% 7 10 )!O8N16EAN?*!.27F+1-
Fotes $or Yes 7 1+ )!O8N1DF*!.27F+1,LYL-
Fotes $or No 7 10 )!O8N1DF*!.27F+1,LNL-
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
!O8N1A
Pa"e 210 o$ 2#0
Entries 1o 6e !ounte% !ount
10 20 .0 . )!O8N1A*!+7E+-
10 0 .0 . )!O8N1A*!#7E#-
10 -20 .0 . )!O8N1A*!07E0-
10 1-&an-88 .0 . )!O8N1A*!C7EC-
10 217.0 .0 . )!O8N1A*!87E8-
10 0(98.899 .0 . )!O8N1A*!97E9-
10 .0 2 )!O8N1A*!107E10-
10 Aello .0 . )!O8N1A*!117E11-
10 =DDFH0M .0 . )!O8N1A*!127E12-
7hat Does %t Do :
12is $unction counts t2e nu53er o$ nu5eric or text entries in a list(
Dt 4ill i"nore 3lan@s(
Synta;
)!O8N1A*an"e1,an"e2,an"e.((( t2rou"2 to an"e.0-
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" ta3le 4as use% 3y a sc2ool to @ee' trac@ o$ t2e exa5inations ta@en 3y eac2 'u'il(
Eac2 exa5 'asse% 4as "ra%e% as 1, 2 or .(
A $ailure 4as entere% as Fail(
12e sc2ool nee%e% to @no4n 2o4 5any 'u'ils sat eac2 exa5(
12e sc2ool also nee%e% to @no4 2o4 5any exa5s 4ere ta@en 3y eac2 'u'il(
12e )!O8N1A*- $unction 2as 3een use% 3ecause o$ its a3ility to count text an% nu5eric entries(
Mat2s En"lis2 Art Aistory
Alan Fail 1 2
6o3 2 1 . .
!arol 1 1 1 .
Da9i% Fail Fail 2
Elaine 1 . 2 Fail +
)!O8N1A*D.97G.9-
Ao4 5any 'u'ils sat eac2 Exa5(
Mat2s En"lis2 Art Aistory
+ . # 2
)!O8N1A*D.#7D.9-
Exa5s 1a@en
6y Eac2 Pu'il
Bac
Bac
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
!O8N1
Pa"e 211 o$ 2#0
Entries 1o 6e !ounte% !ount
10 20 .0 . )!O8N1*!+7E+-
10 0 .0 . )!O8N1*!#7E#-
10 -20 .0 . )!O8N1*!07E0-
10 1-&an-88 .0 . )!O8N1*!C7EC-
10 217.0 .0 . )!O8N1*!87E8-
10 0(89C01C .0 . )!O8N1*!97E9-
10 .0 2 )!O8N1*!107E10-
10 Aello .0 2 )!O8N1*!117E11-
10 =DDFH0M .0 2 )!O8N1*!127E12-
7hat Does %t Do :
12is $unction counts t2e nu53er o$ nu5eric entries in a list(
Dt 4ill i"nore 3lan@s, text an% errors(
Synta;
)!O8N1*an"e1,an"e2,an"e.((( t2rou"2 to an"e.0-
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" ta3le 4as use% 3y a 3uil%ers 5erc2ant to calculate t2e nu53er o$ sales
$or 9arious 'ro%ucts in eac2 5ont2(
Dte5 &an Fe3 Mar
6ric@s :1,000
>oo% :#,000
Glass :2,000 :1,000
Metal :1,000
!ount . 2 0
)!O8N1*D297D.2-
Bac
Bac
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
!OEE
Pa"e 212 o$ 2#0
1a3le 1 1a3le 2
Mont2 A9" 1e5' Sales
&an 20 100 :2,000 :20,000
Fe3 .0 200 :1,000 :.0,000
Mar .0 .00 :#,000 :20,000
A'r +0 200 :1,000 :+0,000
May #0 +00 :8,000 :+0,000
&un #0 +00 :1,000 :20,000
!orrelation 0(80+ !orrelation 28/
)!OEE*D#7D10,E#7E10- )!OEE*G#7G10,A#7A10-
7hat Does %t Do :
12is $unction exa5ines t4o sets o$ %ata to %eter5ine t2e %e"ree o$ relations2i'
3et4een t2e t4o sets(
12e result 4ill 3e a %eci5al 3et4een 0 an% 1(
12e lar"er t2e result, t2e "reater t2e correlation(
Dn 1a3le 1 t2e Mont2ly te5'erature is co5'are% a"ainst t2e Sales o$ air con%itionin" units(
12e correlation s2o4s t2at t2ere is an 0(80+ realtions2i' 3et4een t2e %ata(
Dn 1a3le 2 t2e !ost o$ a%9ertisin" 2as 3een co5'are% to Sales(
Dt can 3e $or5atte% as 'ercenta"e / to s2o4 a 5ore 5eanin" $ull result(
12e correlation s2o4s t2at t2ere is an 28/ realtions2i' 3et4een t2e %ata(
Synta;
)!OEE*an"e1,an"e2-
&ormatting
12e result 4ill nor5ally 3e s2o4n in %eci5al $or5at(
Air !on%
Sales
A%9ertisin"
!osts
Bac
Bac
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
!ONFE1
Pa"e 21. o$ 2#0
1 in c5 2(#+ )!ONFE1*!+,D+,E+-
1 $t 5 0(.0+8 )!ONFE1*!#,D#,E#-
1 y% 5 0(91++ )!ONFE1*!0,D0,E0-
1 yr %ay .0#(2# )!ONFE1*!8,D8,E8-
1 %ay 2r 2+ )!ONFE1*!9,D9,E9-
1(# 2r 5n 90 )!ONFE1*!10,D10,E10-
0(# 5n sec .0 )!ONFE1*!11,D11,E11-
7hat Does %t Do :
12is $unction con9erts a 9alue 5easure in one ty'e o$ unit, to t2e sa5e 9alue ex'resse%
in a %i$$erent ty'e o$ unit, suc2 as Dnc2es to !enti5etres(
Synta;
)!ONFE1*A5ount1o!on9ert,8nit1o!on9ertFro5,8nit1o!on9ert1o-
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" ta3le 4as use% 3y an D5'ort H Ex'ortin" co5'any to con9ert t2e 4ei"2t
an% siOe o$ 'ac@a"es $ro5 ol% style 8? 5easurin" syste5 to Euro'ean syste5(
Poun%s Ounces ?ilo"ra5s
>ei"2t # . 2(.#.01
)!ONFE1*D28,Ll35L,L@"L-;!ONFE1*E28,LoO5L,L@"L-
Feet Dnc2es Metres
Aei"2t 12 0 .(81
Een"t2 8 . 2(#1+0
>i%t2 # 2 1(#C+8
)!ONFE1*D.+,L$tL,L5L-;!ONFE1*E.+,LinL,L5L-
A==re5iations
12is is a list o$ all t2e 'ossi3le a33re9iations 42ic2 can 3e use% to %enote 5easurin" syste5s(
7eight J *ass Distance
Gra5 " Meter 5
?ilo"ra5 @" Statute 5ile 5i
Slu" s" Nautical 5ile N5i
Poun% 5ass l35 Dnc2 in
8 *ato5ic 5ass- u Foot $t
Ounce 5ass oO5 Yar% y%
An"stro5 an"
Time Pica *1HC2 in(- Pica
Year yr
Day %ay 2ressure
Aour 2r Pascal Pa
A5ount
1o !on9ert
!on9ertin"
Fro5
!on9ertin"
1o
!on9erte%
A5ount
Bac
Bac
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
!ONFE1
Pa"e 21+ o$ 2#0
Minute 5n At5os'2ere at5
Secon% sec 55 o$ Mercury 55A"
Tem9erature 'iHuid
De"ree !elsius ! 1eas'oon ts'
De"ree Fa2ren2ei F 1a3les'oon t3s
De"ree ?el9in ? Flui% ounce oO
!u' cu'
&orce Pint 't
Ne4ton N Juart It
Dyne %yn Gallon "al
Poun% $orce l3$ Eiter l
"nergy 2o8er
&oule & Aorse'o4er AP
Er" e >att >
c
D1 calorie cal *agnetism
Electron 9olt eF 1esla 1
Aorse'o4er-2our AP2 Gauss "a
>att-2our >2
Foot-'oun% $l3
618 618
12ese c2aracters can 3e use% as a 're$ix to access $urt2er units o$ 5easure(
Pre$ix Multi'lier A33re9iation Pre$ix Multi'lier A33re9iation
exa 1(00E;18 E %eci 1(00E-01 %
'eta 1(00E;1# P centi 1(00E-02 c
tera 1(00E;12 1 5illi 1(00E-0. 5
"i"a 1(00E;09 G 5icro 1(00E-00 u
5e"a 1(00E;00 M nano 1(00E-09 n
@ilo 1(00E;0. @ 'ico 1(00E-12 '
2ecto 1(00E;02 2 $e5to 1(00E-1# $
%e@ao 1(00E;01 e atto 1(00E-18 a
12er5o%yna5ic
calorie
8sin" LcL as a 're$ix to 5eters LmL 4ill allo4 centi5etres LcmL to 3e calculate%(
A B C D E F G H
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
!ON!A1ENA1E
Pa"e 21# o$ 2#0
Na5e 1 Na5e 2 !oncatenate% 1ext
Alan &ones Alan&ones )!ON!A1ENA1E*!+,D+-
6o3 >illia5s 6o3>illia5s )!ON!A1ENA1E*!#,D#-
!arol Da9ies !arolDa9ies )!ON!A1ENA1E*!0,D0-
Alan &ones Alan &ones )!ON!A1ENA1E*!C,L L,DC-
6o3 >illia5s >illia5s, 6o3 )!ON!A1ENA1E*D8,L, L,!8-
!arol Da9ies Da9ies, !arol )!ON!A1ENA1E*D9,L, L,!9-
7hat Does %t Do:
12is $unction Koins se'arate 'ieces o$ text into one ite5(
Synta;
)!ON!A1ENA1E*1ext1,1ext2,1ext.(((1ext.0-
8' to t2irty 'ieces o$ text can 3e Koine%(
&ormatting
No s'ecial $or5attin" is nee%e%, t2e result 4ill 3e s2o4n as nor5al text(
Note
Na5e 1 Na5e 2 !oncatenate% 1ext
Alan &ones Alan&ones )!2#WD2#
6o3 >illia5s 6o3>illia5s )!20WD20
!arol Da9ies !arolDa9ies )!2CWD2C
Alan &ones Alan &ones )!28WL LWD28
6o3 >illia5s >illia5s, 6o3 )D29WL, LW!29
!arol Da9ies Da9ies, !arol )D.0WL, LW!.0
You can ac2ie9e t2e sa5e result 3y usin" t2e J o'erator(
Bac
Bac
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
!OM6DN
Pa"e 210 o$ 2#0
Pool O$ Dte5s Dte5s Dn A Grou' Possi3le Grou's
+ 2 0 )!OM6DN*!+,D+-
+ . + )!OM6DN*!#,D#-
20 2 .2# )!OM6DN*!0,D0-
7hat Does %t Do :
12is $unction calculates t2e 2i"2est nu53er o$ co53inations a9aila3le 3ase% u'on
a $ixe% nu53er o$ ite5s(
12e internal or%er o$ t2e co53ination %oes not 5atter, so A6 is t2e sa5e as 6A(
Synta;
)!OM6DN*Ao4ManyDte5s,Grou'SiOe-
&ormatting
No s'ecial $or5attin" is reIuire%(
";am9le 1
12is exa5'le calculates t2e 'ossi3le nu53er o$ 'airs o$ letters a9aila3le
$ro5 t2e $our c2aracters A6!D(
1otal !2aracters Grou' SiOe !o53inations
+ 2 0 )!OM6DN*!2#,D2#-
12e 'roo$ M 12e $our letters 7 A6!D
Pair 1 A6
Pair 2 A!
Pair . AD
Pair + 6!
Pair # 6D
Pair 0 !D
";am9le 2
A %ecorator is as@e% to %esi"n a colour sc2e5e $or a ne4 o$$ice(
12e %ecorator is "i9en $i9e colours to 4or@ 4it2, 3ut can only use t2ree in any sc2e5e(
Ao4 5any colours sc2e5es can 3e create% N
A9aila3le !olours !olours Per Sc2e5e 1otals Sc2e5es
# . 10 )!OM6DN*!+1,D+1-
12e colours
e%
Green
6lue
Yello4
6lac@
Sc2e5e 1 Sc2e5e 2 Sc2e5e . Sc2e5e + Sc2e5e #
e% e% e% e% e%
Green Green Green 6lue 6lue
6lue Yello4 6lac@ Yello4 6lac@
Bac Bac
A B C D E F G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
!OM6DN
Pa"e 21C o$ 2#0
Sc2e5e 0 Sc2e5e C Sc2e5e 8 Sc2e5e 9 Sc2e5e 10
Green Green Green 6lue NNNNNN
6lue 6lue Yello4 Yello4
Yello4 6lac@ 6lac@ 6lac@
A B C D E F G
55
56
57
58
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
!ODE
Pa"e 218 o$ 2#0
Eetter ANSD !o%e
A 0# )!ODE*!+-
6 00 )!ODE*!#-
! 0C )!ODE*!0-
a 9C )!ODE*!C-
3 98 )!ODE*!8-
c 99 )!ODE*!9-
Alan 0# )!ODE*!10-
6o3 00 )!ODE*!11-
!arol 0C )!ODE*!12-
7hat Does %t Do:
12is $unction s2o4s t2e ANSD 9alue o$ a sin"le c2aracter, or t2e $irst c2aracter in a 'iece
o$ text(
12e ANSD c2aracter set is use% 3y >in%o4s to i%enti$y eac2 @ey3oar% c2aracter 3y usin"
a uniIue nu53er(
12ere are 2## c2aracters in t2e ANSD set(
Synta;
)!ODE*1ext-
&ormatting
No s'ecial $or5attin" is nee%e%, t2e result 4ill 3e s2o4n as a nu53er 3et4een 1 an% 2##(
";am9le
See t2e exa5'le $or FEJ8EN!Y(
Bac Bac
A B C D E F G H I J K
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
!EEAN
Pa"e 219 o$ 2#0
Dirty 1ext !lean 1ext
Aello Aello )!EEAN*!+-
Aello Aello )!EEAN*!#-
Aello Aello )!EEAN*!0-
7hat Does %t Do:
12is $unction re5o9es any non'rinta3le c2aracters $ro5 text(
12ese non'rintin" c2aracters are o$ten $oun% in %ata 42ic2 2as 3een i5'orte%
$ro5 ot2er syste5s suc2 as %ata3ase i5'orts $ro5 5ain$ra5es(
Synta;
)!EEAN*1ext1o6e!leane%-
&ormatting
No s'ecial $or5attin" is nee%e%( 12e result 4ill s2o4 as nor5al text(
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
!AOOSE
Pa"e 220 o$ 2#0
esult
1 Alan )!AOOSE*!+,LAlanL,L6o3L,L!arolL-
. !arol )!AOOSE*!#,LAlanL,L6o3L,L!arolL-
2 6o3 )!AOOSE*!0,LAlanL,L6o3L,L!arolL-
. 18/ )!AOOSE*!C,10/,1#/,18/-
1 10/ )!AOOSE*!8,10/,1#/,18/-
2 1#/ )!AOOSE*!9,10/,1#/,18/-
7hat Does %t Do:
12is $unction 'ic@s $ro5 a list o$ o'tions 3ase% u'on an Dn%ex 9alue "i9en to 3y t2e user(
Synta;
)!AOOSE*8serFalue, Dte51, Dte52, Dte5. t2rou"2 to Dte529-
&ormatting
No s'ecial $or5attin" is reIuire%(
";am9le
12e $ollo4in" ta3le 4as use% to calculate t2e 5e%als $or at2letes ta@in" 'art in a race(
12e 1i5e $or eac2 at2lete is entere%(
12e )AN?*- $unction calculates t2e $inis2in" 'osition o$ eac2 at2lete(
12e )!AOOSE*- t2en allocates t2e correct 5e%al(
12e )DF*- 2as 3een use% to $ilter out any 'ositions a3o9e ., as t2is 4oul% cause
t2e error o$ =FAE8E to a''ear, %ue to t2e $act t2e )!AOOSE*- 2as only t2ree ite5s in it(
Na5e 1i5e Position Me%al
Alan 17.0 2 Sil9er )DF*D.0Y).,!AOOSE*D.0,LGol%L,LSil9erL,L6ronOeL-,Lun'lace%L-
6o3 171# + un'lace% )DF*D.1Y).,!AOOSE*D.1,LGol%L,LSil9erL,L6ronOeL-,Lun'lace%L-
!arol 27+# 1 Gol% )DF*D.2Y).,!AOOSE*D.2,LGol%L,LSil9erL,L6ronOeL-,Lun'lace%L-
Da9i% 170# # un'lace% )DF*D..Y).,!AOOSE*D..,LGol%L,LSil9erL,L6ronOeL-,Lun'lace%L-
Eric 1720 . 6ronOe )DF*D.+Y).,!AOOSE*D.+,LGol%L,LSil9erL,L6ronOeL-,Lun'lace%L-
)AN?*!.+,!.07!.+-
Dn%ex
Falue
Bac
Bac
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
!AA
Pa"e 221 o$ 2#0
ANSD Nu53er !2aracter
0# A )!AA*G+-
00 6 )!AA*G#-
109 ? )!AA*G0-
7hat Does %t Do:
12is $unction con9erts a nor5al nu53er to t2e c2aracter it re'resent in t2e ANSD
c2aracter set use% 3y >in%o4s(
Synta;
)!AA*Nu53er-
12e Nu53er 5ust 3e 3et4een 1 an% 2##(
&ormatting
12e result 4ill 3e a c2aracter 4it2 no s'ecial $or5attin"(
";am9le
12e $ollo4in" is a list o$ all 2## nu53ers an% t2e c2aracters t2ey re'resent(
Note t2at 5ost >in%o4s 3ase% 'ro"ra5 5ay not %is'lay so5e o$ t2e s'ecial c2aracters,
t2ese 4ill 3e %is'laye% as a s5all 3ox(
1 20 #1 . C0 E 101 e 120 [ 1#1 ? 1C0 ? 201 ? 220 ? 2#1
2 2C #2 + CC M 102 $ 12C \ 1#2 ? 1CC ? 202 ? 22C ? 2#2 ]
. 28 #. # C8 N 10. " 128 ? 1#. ? 1C8 ? 20. ? 228 ? 2#. ?
+ 29 #+ 0 C9 O 10+ 2 129 ? 1#+ ? 1C9 ? 20+ ? 229 ? 2#+
# .0 ## C 80 P 10# i 1.0 ? 1## ? 180 ? 20# ? 2.0 ? 2##
0 .1 #0 8 81 J 100 K 1.1 ? 1#0 ? 181 ? 200 ? 2.1 ?
C .2 #C 9 82 10C @ 1.2 ? 1#C ? 182 ? 20C ? 2.2 ?
8 .. M #8 7 8. S 108 l 1.. ? 1#8 ? 18. ? 208 ? 2.. ?
9 .+ L #9 U 8+ 1 109 5 1.+ ? 1#9 ? 18+ ? 209 ? 2.+ ?
10 .# = 00 Y 8# 8 110 n 1.# ? 100 18# ? 210 ? 2.# ?
11 .0 R 01 ) 80 F 111 o 1.0 ? 101 ? 180 ? 211 ? 2.0 ?
12 .C / 02 S 8C > 112 ' 1.C ? 102 ? 18C ? 212 ? 2.C ?
1. .8 W 0. N 88 B 11. I 1.8 ? 10. ? 188 ? 21. ? 2.8 ?
1+ .9 V 0+ ^ 89 Y 11+ r 1.9 ? 10+ ? 189 ? 21+ ? 2.9 ?
1# +0 * 0# A 90 _ 11# s 1+0 ? 10# 190 ! 21# " 2+0
10 +1 - 00 6 91 ` 110 t 1+1 ? 100 # 191 ? 210 ? 2+1
1C +2 < 0C ! 92 a 11C u 1+2 ? 10C $ 192 ? 21C ? 2+2
18 +. ; 08 D 9. b 118 9 1+. ? 108 % 19. ? 218 & 2+.
19 ++ , 09 E 9+ Z 119 4 1++ 109 ? 19+ ' 219 ( 2++
20 +# - C0 F 9# T 120 x 1+# ! 1C0 ) 19# * 220 + 2+#
21 +0 ( C1 G 90 c 121 y 1+0 " 1C1 , 190 - 221 ? 2+0
22 +C H C2 A 9C a 122 O 1+C # 1C2 . 19C / 222 ? 2+C
2. +8 0 C. D 98 3 12. P 1+8 $ 1C. 0 198 1 22. ? 2+8
2+ +9 1 C+ & 99 c 12+ X 1+9 % 1C+ 2 199 3 22+ & 2+9  
2# #0 2 C# ? 100 % 12# Q 1#0 ' 1C# 4 200 ? 22# ( 2#0 ?
Note
Nu53er .2 %oes not s2o4 as it is t2e SPA!E6A c2aracter(
Bac Bac
A B C D E F G H I J K L M N O P Q R S T U ! "
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
12is is t2e cell an% contents to test( 1C(#0/
12e cell a%%ress(
RDR. )!EEE*La%%ressL,D.-
12e colu5n nu53er(
+ )!EEE*LcolL,D.-
12e ro4 nu53er(
. )!EEE*Lro4L,D.-
12e actual contents o$ t2e cell(
0(1C# )!EEE*LcontentsL,D.-
9 )!EEE*Lty'eL,D.-
\ )!EEE*L're$ixL,D.-
12e 4i%t2 o$ t2e cell(
12 )!EEE*L4i%t2L,D.-
P2 )!EEE*L$or5atL,D.-
0 )!EEE*L'arent2esesL,D.-
0 )!EEE*LcolorL,D.-
1 )!EEE*L'rotectL,D.-
12e $ilena5e containin" t2e cell(
V$ile7HHH9arH444Ha''sHcon9ersionHt5'Hscratc2T0H2C+#01C0#(xlsV=R!EEE
)!EEE*L$ilena5eL,D.-
7hat Does %t Do :
12is $unction exa5ines a cell an% %is'lays in$or5ation a3out t2e contents, 'osition an% $or5attin"(
Synta;
)!EEE*L1y'eO$Dn$oeIuire%L,!ell1o1est-
12e 1y'eO$Dn$oeIuire% is a text entry 42ic2 5ust 3e surroun%e% 4it2 Iuotes L L(
&ormatting
No s'ecial $or5attin" is nee%e%(
!o%es use% to s2o4 t2e $or5attin" o$ t2e cell(
Numeric &ormat Code
General G
0 F0
=,==0 ,0
0(00 F2
=,==0(00 ,2
R=,==0T-U*R=,==0- !0
R=,==0T-U`e%b*R=,==0- !0-
R=,==0(00T-U*R=,==0(00- !2
R=,==0(00T-U`e%b*R=,==0(00- !2-
0/ P0
0(00/ P2
0(00E;00 S2
= NHN or = NNHNN G
5H%Hyy or 5H%Hyy 2755 or 55H%%Hyy( D+
%-555-yy or %%-555-yy D1
%-555 or %%-555 D2
555-yy D.
55H%% D#
2755 AMHPM DC
27557ss AMHPM D0
2755 D9
27557ss D8
12e ty'e o$ entry in t2e cell(
S2o4n as = $or 3lan@, l $or text, 5 $or 9alue(
12e ali"n5ent o$ t2e cell(
S2o4n as K $or le$t, L $or centre, I $or ri"2t(
Not2in" is s2o4n $or nu5eric entries(
12e nu53er $or5at $o t2e cell(
*See t2e ta3le s2o4n 3elo4-
For5atte% $or 3races * - on 'ositi9e 9alues(
1 $or yes, 0 $or no(
For5atte% $or coloure% ne"ati9es(
1 $or yes, 0 $or no(
12e ty'e o$ cell 'rotection(
1 $or a loc@e%, 0 $or unloc@e%(
Bac Bac
";am9le
12e $ollo4in" exa5'le uses t2e )!EEE*- $unction as 'art o$ a $or5ula 42ic2 extracts t2e $ilena5e(
12e na5e o$ t2e current $ile is 7 =FAE8EM
)MDD*!EEE*L$ilena5eL-,FDND*L`L,!EEE*L$ilena5eL--;1,FDND*LbL,!EEE*L$ilena5eL---FDND*L`L,!EEE*L$ilena5eL---1-
V$ile7HHH9arH444Ha''sHcon9ersionHt5'Hscratc2T0H2C+#01C0#(xlsV=R!EEE
Nu53er aise% 8'
2(1 . )!EDEDNG*!+,1-
1(# 2 )!EDEDNG*!#,1-
1(9 2 )!EDEDNG*!0,1-
20 .0 )!EDEDNG*!C,.0-
2# .0 )!EDEDNG*!8,.0-
+0 00 )!EDEDNG*!9,.0-
7hat Does %t Do :
12is $unction roun%s a nu53er u' to t2e nearest 5ulti'le s'eci$ie% 3y t2e user(
Synta;
)!EDEDNG*Falue1ooun%,Multi'le1ooun%8'1o-
12e Falue1ooun% can 3e a cell a%%ress or a calculation(
&ormatting
No s'ecial $or5attin" is nee%e%(
";am9le 1
12e $ollo4in" ta3le 4as use% 3y a estate a"ent rentin" 2oli%ay a'art5ents(
12e 'ro'erties 3ein" rente% are only a9aila3le on a 4ee@ly 3asis(
>2en t2e custo5er su''lies t2e nu53er o$ %ays reIuire% in t2e 'ro'erty t2e )!EDEDNG*-
$unction roun%s it u' 3y a 5ulti'le o$ C to calculate t2e nu53er o$ $ull 4ee@s to 3e 3ille%(
Days eIuire%
!usto5er 1 . C )!EDEDNG*D28,C-
!usto5er 2 + C )!EDEDNG*D29,C-
!usto5er . 10 1+ )!EDEDNG*D.0,C-
";am9le 2
12e $ollo4in" ta3le 4as use% 3y a 3uil%ers 5erc2ant %eli9erin" 'ro%ucts to a construction site(
12e 5erc2ant nee%s to 2ire truc@s to 5o9e eac2 'ro%uct(
Eac2 'ro%uct nee%s a 'articular ty'e o$ truc@ o$ a $ixe% ca'acity(
1a3le 1 calculates t2e nu53er o$ truc@s reIuire% 3y %i9i%in" t2e 8nits 1o 6e Mo9e% 3y
t2e !a'acity o$ t2e truc@(
12is results o$ t2e %i9ision are not 42ole nu53ers, an% t2e 3uil%er cannot 2ire Kust 'art
o$ a truc@(
1a3le 1
Dte5
6ric@s 1000 .00 .(.. )D+#HE+#
>oo% #000 000 8(.. )D+0HE+0
!e5ent 2000 .#0 #(C1 )D+CHE+C
1a3le 2 s2o4s 2o4 t2e )!EDEDNG*- $unction 2as 3een use% to roun% u' t2e result o$
t2e %i9ision to a 42ole nu53er, an% t2us "i9en t2e exact a5ount o$ truc@s nee%e%(
1a3le 2
Dte5
6ric@s 1000 .00 + )!EDEDNG*D#+HE#+,1-
>oo% #000 000 9 )!EDEDNG*D##HE##,1-
Days 1o
6e 6ille%
8nits 1o
6e Mo9e%
1ruc@
!a'acity
1ruc@s
Nee%e%
8nits 1o
6e Mo9e%
1ruc@
!a'acity
1ruc@s
Nee%e%
Bac Bac
!e5ent 2000 .#0 0 )!EDEDNG*D#0HE#0,1-
";am9le .
12e $ollo4in" ta3les 4ere use% 3y a s2o'@ee'er to calculate t2e sellin" 'rice o$ an ite5(
12e s2o'@ee'er 3uys 'ro%ucts 3y t2e 3ox(
12e cost o$ t2e ite5 is calculate% 3y %i9i%in" t2e 6ox !ost 3y t2e 6ox Juantity(
12e s2o'@ee'er al4ays 4ants t2e 'rice to en% in 99 'ence(
1a3le 1 s2o4s 2o4 Kust a nor5al %i9ision results in 9aryin" Dte5 !osts(
1a3le 1
Dte5 6ox Jnty 6ox !ost !ost Per Dte5
Plu"s 11 :20 1(81818 )D09H!09
Soc@ets C :18(2# 2(00C1+ )DC0H!C0
&unctions # :28(10 #(02000 )DC1H!C1
A%a'ters 10 :28 1(C#000 )DC2H!C2
1a3le 2 s2o4s 2o4 t2e )!EDEDNG*- $unction 2as 3een use% to raise t2e Dte5 !ost to
al4ays en% in 99 'ence(
1a3le 2
Dte5 Dn 6ox 6ox !ost !ost Per Dte5 aise% !ost
Plu"s 11 :20 1(81818 1(99
Soc@ets C :18(2# 2(00C1+ 2(99
&unctions # :28(10 #(02000 #(99
A%a'ters 10 :28 1(C#000 1(99
)DN1*E8.-;!EDEDNG*MOD*E8.,1-,0(99-
";9lanation
)DN1*E8.- !alculates t2e inte"er 'art o$ t2e 'rice(
)MOD*E8.,1- !alculates t2e %eci5al 'art o$ t2e 'rice(
)!EDEDNG*MOD*E8.-,0(99- aises t2e %eci5al to 0(99
6inary Nu53er Deci5al EIui9alent
0 =ADDDNN )6DN2DE!*!+-
1 1 )6DN2DE!*!#-
10 2 )6DN2DE!*!0-
11 . )6DN2DE!*!C-
111111111 #11 )6DN2DE!*!8-
1111111111 -1 )6DN2DE!*!9-
1111111110 -2 )6DN2DE!*!10-
1111111101 -. )6DN2DE!*!11-
1000000000 -#12 )6DN2DE!*!12-
11111111111 Err7#02 )6DN2DE!*!1.-
7hat Does %t Do :
12is $unction con9erts a 3inary nu53er to %eci5al(
Ne"ati9e nu53ers are re'resente% usin" t4oVs-co5'le5ent notation(
Synta;
)6DN2DE!*6inaryNu53er-
12e 3inary nu53er 2as a li5it o$ ten c2aracters(
&ormatting
No s'ecial $or5attin" is nee%e%(
Bac Bac
Mon 1ue >e% 12u Fri Sat Sun A9era"e
1e5' .0 .1 .2 29 20 28 2C 29 )AFEAGE*D+7&+-
ain 0 0 0 + 0 . 1 2 )AFEAGE*D#7&#-
Mon 1ue >e% 12u Fri Sat Sun A9era"e
1e5' .0 .2 29 20 28 2C 28(000C )AFEAGE*D87&8-
ain 0 0 + 0 . 1 2(..... )AFEAGE*D97&9-
Mon 1ue >e% 12u Fri Sat Sun A9era"e
1e5' .0 No .2 29 20 28 2C 28(000C )AFEAGE*D127&12-
ain 0 ea%in" 0 + 0 . 1 2(..... )AFEAGE*D1.7&1.-
7hat Does %t Do :
12is $unction calculates t2e a9era"e $ro5 a list o$ nu53ers(
D$ t2e cell is 3lan@ or contains text, t2e cell 4ill not 3e use% in t2e a9era"e calculation(
D$ t2e cell contains Oero 0, t2e cell 4ill 3e inclu%e% in t2e a9era"e calculation(
Synta;
)AFEAGE*an"e1,an"e2,an"e.((( t2rou"2 to an"e.0-
&ormatting
No s'ecial $or5attin" is nee%e%(
Note
1o calculate t2e a9era"e o$ cells 42ic2 contain text or 3lan@s use )S8M*- to "et t2e total an%
t2en %i9i%e 3y t2e count o$ t2e entries usin" )!O8N1A*-(
Mon 1ue >e% 12u Fri Sat Sun A9era"e
1e5' .0 No .2 29 20 28 2C 2+(#C1+ )S8M*D.17&.1-H!O8N1A*D.17&.1-
ain 0 ea%in" 0 + 0 . 1 2 )S8M*D.27&.2-H!O8N1A*D.27&.2-
Mon 1ue >e% 12u Fri Sat Sun A9era"e
1e5' .0 .2 29 20 28 2C 28(000C )S8M*D.#7&.#-H!O8N1A*D.#7&.#-
ain 0 0 + 0 . 1 2(..... )S8M*D.07&.0-H!O8N1A*D.07&.0-
Furt2er 8sa"e
Bac Bac
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
AEAS
Pa"e 2.0 o$ 2#0
Pin@ Na5e A"e Err7#0+ )AEAS*Peo'leEists-
Alan 18
6o3 1C
!arol 20
Green Na5e A"e
Da9i% 20
Eric 10
Fre% 19
7hat Does %t Do:
12is $unction tests a ran"e to %eter5ine 42et2er it is a sin"le 3loc@ o$ %ata, or 42et2er
it is a 5ulti'le selection(
D$ it is a sin"le 3loc@ t2e result 4ill 3e 1(
D$ it is a 5ulti'le 3loc@ t2e result 4ill 3e t2e nu53er o$ ran"es selecte%(
12e $unction is %esi"ne% to 3e use% in 5acros(
Synta;
)AEAS*an"e1o1est-
&ormatting
12e result 4ill 3e s2o4n as a nu53er(
";am9le
12e exa5'le at t2e to' o$ t2is 'a"e s2o4s t4o ran"es coloure% 'in@ an% "reen(
12ese ran"es 2a9e 3een "i9en t2e na5e Peo'leEists(
12e )AEAS*Peo'leEists- "i9es a result o$ 2 in%icatin" t2at t2ere are t4o se'arate
selections 42ic2 $or5 t2e Peo'leEists ran"e(
Note
1o na5e 5ulti'le ran"es t2e !1E @ey 5ust 3e use%(
Dn t2e a3o9e exa5'le t2e 'in@ ran"e 4as selecte% as nor5al, t2en t2e !trl @ey
4as 2el% %o4n 3e$ore selectin" t2e "reen ran"e(
>2en a an"e Na5e is create% it 4ill consi%er 3ot2 Pin@ an% Green as 3ein" one ran"e(
Bac
Bac
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Dte5s 1o 1est esult
#00 800 1 )AND*!+S)100,D+S)100-
#00 2# 0 )AND*!#S)100,D#S)100-
2# #00 0 )AND*!0S)100,D0S)100-
12 1 )AND*DCS)1,DCY)#2-
7hat Does %t Do:
12is $unction tests t4o or 5ore con%itions to see i$ t2ey are all true(
Dt can 3e use% to test t2at a series o$ nu53ers 5eet certain con%itions(
Dt can 3e use% to test t2at a nu53er or a %ate $alls 3et4een an u''er an% lo4er li5it(
Nor5ally t2e AND*- $unction 4oul% 3e use% in conKunction 4it2 a $unction suc2 as )DF*-(
Synta;
)AND*1est1,1est2-
Note t2at t2ere can 3e u' to .0 'ossi3le tests(
&ormatting
>2en use% 3y itsel$ it 4ill s2o4 18E or FAESE(
";am9le 1
12e $ollo4in" exa5'le s2o4s a list o$ exa5ination results(
12e teac2er 4ants to $in% t2e 'u'ils 42o score% a3o9e a9era"e in all t2ree exa5s(
12e )AND*- $unction 2as 3een use% to test t2at eac2 score is a3o9e t2e a9era"e(
12e result o$ 18E is s2o4n $or 'u'ils 42o 2a9e score% a3o9e a9era"e in all t2ree exa5s(
Na5e Mat2s En"lis2 P2ysics Passe%
Alan 80 C# 8# 1
6o3 #0 .0 +0 0
!arol 00 C0 #0 0
Da9i% 90 8# 9# 1
Eric 20 .0 A3sent 0
Fre% +0 00 80 0
Gail 10 90 80 0
Aarry 80 C0 00 1
Dan .0 10 20 0
&anice 10 20 .0 0
)AND*!.8S)AFEAGE*R!R297R!R.8-,D.8S)AFEAGE*RDR297RDR.8-,E.8S)AFEAGE*RER297RER.8--
A9era"es +C #+ 00
Bac Bac
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
ADDESS
Pa"e 2.2 o$ 2#0
1y'e a colu5n nu53er 7 2
1y'e a ro4 nu53er 7 .
1y'e a s2eet na5e 7 Aello
R6R. )ADDESS*F+,F.,1,18E-
6R. )ADDESS*F+,F.,2,18E-
R6. )ADDESS*F+,F.,.,18E-
6. )ADDESS*F+,F.,+,18E-
.!2 )ADDESS*F+,F.,1,FAESE-
.!`2b )ADDESS*F+,F.,2,FAESE-
`.b!2 )ADDESS*F+,F.,.,FAESE-
`.b!`2b )ADDESS*F+,F.,+,FAESE-
Aello(R6R. )ADDESS*F+,F.,1,18E,F#-
Aello(6R. )ADDESS*F+,F.,2,18E,F#-
Aello(R6. )ADDESS*F+,F.,.,18E,F#-
Aello(6. )ADDESS*F+,F.,+,18E,F#-
7hat Does %t Do :
12is $unction creates a cell re$erence as a 'iece o$ text, 3ase% on a ro4 an% colu5n
nu53ers "i9en 3y t2e user(
12is ty'e o$ $unction is use% in 5acros rat2er t2an on t2e actual 4or@s2eet(
Synta;
)ADDESS*o4Nu53er,!olNu53er,A3solute,A1or1!1,S2eetNa5e-
12e o4Nu53er is t2e nor5al ro4 nu53er $ro5 1 to 10.8+(
12e !olNu53er is $ro5 1 to 2#0, cols A to DF(
12e A3solute can 3e 1,2,. or +(
>2en 1 t2e re$erence 4ill 3e in t2e $or5 RAR1, colu5n an% ro4 a3solute(
>2en 2 t2e re$erence 4ill 3e in t2e $or5 AR1, only t2e ro4 a3solute(
>2en . t2e re$erence 4ill 3e in t2e $or5 RA1, only t2e colu5n a3solute(
>2en + t2e re$erence 4ill 3e in t2e $or5 A1, neit2er col or ro4 a3solute(
12e A1or1!1 is eit2er 18E o$ FAESE(
>2en 18E t2e re$erence 4ill 3e in t2e $or5 A1, t2e nor5al style $or cell a%%resses(
>2en FAESE t2e re$erence 4ill 3e in t2e $or5 1!1, t2e alternati9e style o$ cell a%%ress(
12e S2eetNa5e is a 'iece o$ text to 3e use% as t2e 4or@s2eet na5e in t2e re$erence(
12e S2eetNa5e %oes not actually 2a9e to exist(
Bac
Bac
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
Nu53er A3solute Falue
10 10 )A6S*!+-
-10 10 )A6S*!#-
1(2# 1(2# )A6S*!0-
-1(2# 1(2# )A6S*!C-
7hat Does it Do :
12is $unction calculates t2e 9alue o$ a nu53er, irres'ecti9e o$ 42et2er it is 'ositi9e or ne"ati9e(
Synta;
)A6S*!ellA%%ress or Nu53er-
&ormatting
12e result 4ill 3e s2o4n as a nu53er, no s'ecial $or5attin" is nee%e%(
";am9le
12e $ollo4in" ta3le 4as use% 3y a co5'any testin" a 5ac2ine 42ic2 cuts ti53er(
12e 5ac2ine nee%s to cut ti53er to an exact len"t2(
12ree 'ieces o$ ti53er 4ere cut an% t2en 5easure%(
Dn calculatin" t2e %i$$erence 3et4een t2e eIuire% Een"t2 an% t2e Actual Een"t2 it %oes
not 5atter i$ t2e 4oo% 4as cut too lon" or s2ort, t2e 5easure5ent nee%s to 3e ex'resse% as
an a3solute 9alue(
1a3le 1 s2o4s t2e ori"inal calculations(
12e Di$$erence $or 1est . is s2o4n as ne"ati9e, 42ic2 2as a @noc@ on e$$ect
42en t2e Error Percenta"e is calculate%(
>2et2er t2e 4oo% 4as too lon" or s2ort, t2e 'ercenta"e s2oul% still 3e ex'resse%
as an a3solute 9alue(
1a3le 1
Di$$erence
1est 1 120 120 0 0/
1est 2 120 90 .0 2#/
1est . 120 1#0 -.0 -2#/
)D.0-E.0
1a3le 2 s2o4s t2e sa5e %ata 3ut usin" t2e )A6S*- $unction to correct t2e calculations(
1a3le 2
Di$$erence
1est 1 120 120 0 0/
1est 2 120 90 .0 2#/
1est . 120 1#0 .0 2#/
)A6S*D+#-E+#-
1est
!ut
eIuire%
Een"t2
Actual
Een"t2
Error
Percenta"e
1est
!ut
eIuire%
Een"t2
Actual
Een"t2
Error
Percenta"e
Bac Bac
Dnstea% o$ usin" t2e AutoSu5 3utton $ro5 t2e tool3ar,
1ry it 2ere 7
or
&an Fe3 Mar 1otal
Nort2 10 #0 90 1#0
Sout2 20 00 100 180
East .0 C0 200 ..0
>est +0 80 .00 +20
1otal 100 200 090 10#0
you can 'ress Alt an% ? to ac2ie9e t2e sa5e result(
Mo9e to a 3lan@ cell in t2e 1otal ro4 or colu5n, t2en 'ress Alt an% ?(
Select a ro4, colu5n or all cells an% t2en 'ress Alt an% ?(
Bac Bac
You can calculate a 'ersons a"e 3ase% on t2eir 3irt2%ay an% to%ays %ate(
12e DA1EDDF*- is not %ocu5ente% in Excel #, C or 9C, 3ut it is in 2000(
*Ma@es you 4on%er 42at else Microso$t $or"ot to tell usM-
6irt2 %ate 7 29-A'r-C.
Years li9e% 7 +2 )DA1EDDF*!8,1ODAY*-,LyL-
an% t2e 5ont2s 7 1 )DA1EDDF*!8,1ODAY*-,Ly5L-
an% t2e %ays 7 2C )DA1EDDF*!8,1ODAY*-,L5%L-
You can 'ut t2is all to"et2er in one calculation, 42ic2 creates a text 9ersion(
A"e is +2 Years, 1 Mont2s an% 2C Days
="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days"
Another 8ay to calculate age
12is 5et2o% "i9es you an a"e 42ic2 5ay 'otentially 2a9e %eci5al 'laces re'resentin" t2e 5ont2s(
D$ t2e a"e is 20(#, t2e (# re'resents 0 5ont2s(
6irt2 %ate 7 1-&an-00
A"e is 7 ##(+8 )*1ODAY*--!2.-H.0#(2#
12e calculation uses t2e DA1EDDF*- $unction(
Bac Bac
So5eti5es you 4ill nee% to use 3rac@ets, *also @no4n as V3racesV-, in $or5ula(
12is is to ensure t2at t2e calculations are 'er$or5e% in t2e or%er t2at you nee%(
Exa5'le 1 7 12e 4ron" ans4er M
10
20
2
#0 )!12;!1.est 20 #0 #
1otal
%< it does not 8or P
12e $eature 5ay 2a9e 3een s4itc2e% o$$ on your co5'uter(
Go to cell C1/ an% t2en enter t2e $or5ula ?S+*4Can6
12is $or5ula can 3e co'ie% to D1/ an% "1/, an% t2e na5es c2an"e to &e= an% *ar(
You can s4itc2 it on 3y usin" Tools, )9tions, Calculation, Acce9t 'a=els in &ormula(
Bac
Bac
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
S2o4 all $or5ula
Pa"e 2+0 o$ 2#0
Press t2e sa5e co53ination to see t2e ori"inal 9ie4(
10 20 .0
.0 +0 C0
#0 00 00
C0 80 .0
You can 9ie4 all t2e $or5ula on t2e 4or@s2eet 3y 'ressin" Ctrl an% Q(
12e K is t2e le$t sin"le Iuote usually $oun% on t2e @ey to le$t o$ nu53er 1(
Press Ctrl an% Q to see t2e $or5ula 3elo4( &The screen may loo' a bit odd.(
Bac
Bac
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Percenta"es
Pa"e 2+1 o$ 2#0
12ere are no s'eci$ic $unctions $or calculatin" 'ercenta"es(
You 2a9e to use t2e s@ills you 4ere tau"2t in your 5at2s class at sc2oolM
&inding a 9ercentage o< a 5alue
Dnitial 9alue 120
/ to $in% 2#/
Percenta"e 9alue .0 )D8est 8,000 9,000 0,000 #,000 1otal
1otal 20,000 2.,000 .1,000 20,000 100,000
'ast years 3uarters as R o< last years Total
e"ion J1 J2 J. J+
Nort2 9/ 2/ 9/ C/ )GC+HRARC8
Sout2 C/ +/ 9/ #/ )GC#HRARC8
East 2/ 8/ C/ ./ )GC0HRARC8
>est 8/ 9/ 0/ #/ )GCCHRARC8
1otal 20/ 2./ .1/ 20/ )GC8HRARC8
Ne;t years =udget 1#0,000
Ne;t years estimated =udget reHuirements
e"ion J1 J2 J. J+
Nort2 1.,#00 .,000 1.,#00 10,#00 )G82illia5s >illia5s
=/I01T(C.2,"E3(C.2)4FI3D("5",6$76TIT$TE(C.2," ","5","E3(C.2)4"E3(6$76TIT$TE(C.2," ","")))))
&inding the *iddle name
Full Na5e Mi%%le Na5e
Alan Da9i% &ones Da9i%
6o3 &o2n S5it2 &o2n
!arol Susan >illia5s Susan
="EFT(/I01T(C-,"E3(C-)4FI3D(" ",C-,')),FI3D(" ",/I01T(C-,"E3(C-)4FI3D(" ",C-,')),'))
Bac Bac
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
1i5e !alculation
Pa"e 2+# o$ 2#0
Excel can 4or@ 4it2 ti5e 9ery easily(
1i5e can 3e entere% in 9arious %i$$erent $or5ats an% calculations 'er$or5e%(
12ere are one or t4o o%%ities, 3ut not2in" 42ic2 s2oul% 'ut you o$$ 4or@in" 4it2 it(
Ty9ing time
>2en ti5e is entere% into 4or@s2eet it s2oul% 3e entere% 4it2 a colon 3et4een
17.0 127.0 2071# 227+#
Excel can co'e 4it2 eit2er t2e 2+2our syste5 or t2e a5H'5 syste5(
You 5ust lea9e a s'ace 3et4een t2e nu53er an% t2e text(
17.0 AM 17.0 PM 1071# AM 1071# PM
&inding the di