Excel...10...10 #####...10 #VALUE!...10 #DIV/0!... 11 #NAME?... 11 #N/A... 11 #REF!...12 #NUM!...12 #NULL!...13...13...14...14...15 /...15...16...17...18 GETPIVOTDATA...20 /...21 DATE NOW...21 DATEDIF...22 DATEVALUE...22 DAY 1 31...23 DAYS360...23 EDATE...24 EOMONTH EOMONTH...24 HOUR 0 ( 0 ) 23 ( 11 )...24 MINUTE 0 () 59 ()...25 MONTH 1 () 12 ()...25 NETWORKDAYS...25 NOW...26 SECOND 0 () 59 () SECOND...26 TIME 0 0.99999999 0:00:00 ( 0 ) 23:59:59 ( 11 59 59 )...27 TIMEVALUE 0 0.99999999 0:00:00 ( 0 ) 23:59:59 ( 11 59 59 )...27 TODAY Excel NOW...27 WEEKDAY...27 WEEKNUM...28 WORKDAY [ ] 1
[ ] [ ] [ ] [ ] [ ]...29...29...30 YEAR 1900 () 9999 ()..30 YEARFRAC 2 ( ) 1...30...31 BESSELI In(x) Jn...32 BESSELJ Jn(x)...32 BESSELK Kn(x) Jn Yn...33 BIN2DEC 2 10...33 BIN2HEX...34 BIN2OCT 2 8...34 COMPLEX "x+yi" "x+yj"...35 CONVERT...35 DEC2BIN 10 2...37 DEC2HEX...38 DEC2OCT 10 8...38 DELTA 2 1 = 2 1 0 DELTA...39 ERF...39 ERFC x...40 GESTEP 1 0 ( ) GESTEP...40 HEX2BIN 16 2...41 HEX2DEC 16 10...41 HEX2OCT...42 IMABS "x+yi" "x+yj"...42 IMAGINARY "x+yi" "x+yj"...43 IMARGUMENT ( )...43 IMCONJUGATE "x+yi" "x+yj"...43 IMCOS "x+yi" "x+yj"...44 IMDIV "x+yi" "x+yj" 2...44 IMEXP "x+yi" "x+yj"...44 IMLN "x+yi" "x+yj"...45 IMLOG10 "x+yi" "x+yj" 10 ( )...45 IMLOG2 "x+yi" "x+yj" 2...45 IMPOWER "x+yi" "x+yj"...46 IMPRODUCT "x+yi" "x+yj" 2 29...46 IMREAL "x+yi" "x+yj"...46 IMSIN "x+yi" "x+yj"...47 IMSQRT "x+yi" "x+yj"...47 IMSUB "x+yi" "x+yj" 2...48 OCT2BIN 8 2...48 2
OCT2DEC 8 10...49 OCT2HEX8 16...49...49 CELL...50 COUNTBLANK...52 ERROR.TYPE Excel ERROR.TYPE ERROR.TYPE...52 INFO...53 IS Excel 9...53 N...54 NA #N/A #N/A " " NA #N/A ( #N/A #N/A )...55 PHONETIC...55 TYPE TYPE...56...56 AND TRUE TRUE 1 FALSE FALSE...56 FALSE FALSE...57 IF TRUE () FALSE ()...57 NOT TRUE FALSE FALSE TRUE...58 OR TRUE TRUE FALSE FALSE...58 TRUE TRUE...59 /...59 ADDRESS ( ) ADDRESS...60 AREAS 1...60 CHOOSE 1 CHOOSE 29 1 1 7 1 1 7...61 COLUMN...61 COLUMNS...62 HLOOKUP HLOOKUP VLOOKUP...62 HYPERLINK...63 INDEX...64 INDIRECT...67 LOOKUP 1 1 LOOKUP 2...68 MATCH 3
/ MATCH...70 OFFSET ( )...71 ROW...72 ROWS...72 TRANSPOSE TRANSPOSE TRANSPOSE LINEST...72 y TRANSPOSE LINEST y...73 VLOOKUP VLOOKUP HLOOKUP...73 /...74 ABS (+-)...75 ACOS 0( ) ( )...75 ACOSH ( ) 1 ( ) ACOSH(COSH( )) =...76 ASIN -/2 /2...76 ASINH ( ) ( ) ASINH(SINH( )) =...76 ATAN -/2 /2...76 ATAN2 x-y x 0 x y -( -...77 ATANH ( ) -1 1 ( ) ATANH(TANH( )) =...77 CEILING 0 442 1 =CEILING(442,10) 10...77 COMBIN COMBIN...78 COS...78 COSH ( )...79 COUNTIF...79 DEGREES...79 EVEN...79 EXP e e e = 2.71828182845904...80 FACT 1...80 FACTDOUBLE...80 4
FLOOR 0...81 GCD 1 2......81 INT...81 LCM 1 2... LCM...82 LN e (2.71828182845904)..82 LOG...82 LOG10 10 ( )...83 MDETERM...83 MINVERSE...83 MMULT 2 1 2...84 MOD...85 MROUND...85 MULTINOMIAL...85 ODD...86 PI 3.14159265358979 15...86 POWER...86 PRODUCT...87 QUOTIENT ( ) 87 RADIANS...87 RANDBETWEEN 0 1 SUBTOTAL...87 ROMAN...88 ROUND...88 ROUNDDOWN...89 ROUNDUP...89 SERIESSUM...89 SIGN 10 0-1...90 SIN...90 SINH ( )...91 SQRT...91 SQRTPI ( * )...91 SUBTOTAL [ ] ([ ] )...92 SUM...92 SUMIF...93 SUMPRODUCT.93 SUMSQ 2 ( )...93 SUMX2MY2 2...94 SUMX2PY2 2...94 SUMXMY2 2 2...94 TAN...95 TANH ( )...95 TRUNC...95...96 5
AVEDEV AVEDEV...97 AVERAGE ( )...98 AVERAGEA ( ) TRUE FALSE...98 BETADIST 1...99 BETAINV = BETADIST(x,...) BETAINV(,...) = x...99 BINOMDIST... 100 CHIDIST 2 (2) 2 2 2... 101 CHIINV 2 (2) = CHIDIST(x,...) CHIINV(,...) = x 2... 101 CHITEST 2 (2) CHITEST 2 2... 102 CONFIDENCE... 103 CORREL 2 2... 103 COUNT... 104 COUNTA... 104 COVAR 2 2... 105 CRITBINOM... 105 DEVSQ... 106 EXPONDIST EXPONDIST 1... 106 FDIST F 2... 107 FINV F = FDIST(x,...) FINV(,...) = x... 107 FISHER x FISHER... 108 6
FISHERINV y = FISHER(x) FISHERINV(y) = x... 108 FORECAST x y x y x (y)... 108 FREQUENCY... 109 FTEST F F 1 2 FTEST 2... 110 GAMMADIST... 110 GAMMAINV = GAMMADIST(x,...) GAMMAINV(,...) = x x... 111 GAMMALN G(x)... 111 GEOMEAN GEOMEAN... 112 GROWTH y x x y GROWTH y x... 112 HARMEAN 1 ( )... 113 HYPGEOMDIST HYPGEOMDIST HYPGEOMDIST 2... 114 INTERCEPT x y x y y 0( ) INTERCEPT 0C... 115 KURT.. 115 LARGE 1 LARGE LARGE 2 3... 116 LINEST y LINEST... 116 LOGEST LOGEST... 120 LOGINV x ln(x) p = LOGNORMDIST(x,...) LOGINV(p,...) = x... 122 LOGNORMDIST... 122 MAX... 123 MAXA TRUEFALSE... 123 MEDIAN ( ) 7
... 124 MIN... 124 MINA TRUEFALSE... 125 MODE ( ) MODE MEDIAN 125 NEGBINOMDIST NEGBINOMDIST BINOMDIST... 125... 126 NORMDIST... 126 NORMINV... 127 PEARSON r r -1.0 1.0 2... 127 POISSON 1... 128 PROB x x... 128 QUARTILE QUARTILE 25%... 129 RANK... 129 RSQ y x r2 PEARSON r2 x y... 130 SKEW... 130 SLOPE y x 2... 131 SMALL 1 SMALL... 131 STANDARDIZE... 131 STDEV... 132 TDIST t t t... 132 TINV t... 133 TREND y x ( ) x y... 133 TRIMMEAN TRIMMEAN... 134 TTEST t TTEST 2... 135 VAR... 135 VARA TRUEFALSE... 136 VARP... 136 8
VARPA TRUEFALSE... 137 WEIBULL... 137 ZTEST z P z x... 138... 138 ASC... 139 CHAR ASCII JIS CHAR... 139 CLEAN CLEAN CLEAN... 140 CODE ASCII JIS... 140 CONCATENATE 1... 140 DOLLAR... 140 EXACT 2 TRUE FALSE EXACT EXACT... 141 FIND/FINDB ( ) ( )... 141 FIXED (.) (,)... 142 JIS... 143 LEFT/LEFTB ( )... 143 LEN/LENB... 144 LOWER... 144 MID/MIDB... 144 PROPER 2... 145 REPLACE/REPLACEB... 145 REPT... 146 RIGHT/RIGHTB ( )... 146 SEARCH/SEARCHB SEARCH/SEARCHB MID REPLACE... 147 SUBSTITUTE REPLACE... 148 T... 148 TEXT... 149 TRIM 1 TRIM... 149 UPPER... 149 VALUE... 150 YEN... 150 9
Excel Excel Excel Shift Excel ##### [ ] [ ] [ ] "####" [ ] [ ] [ ] [ ] [ ] #VALUE! #VALUE! TRUE FALSE A5 A6 "Not available" =A5+A6 #VALUE! 2 SUM =SUM(A5:A6) Enter F2 Ctrl Shift Enter 1 1 #VALUE! 10
#DIV/0! #DIV/0! 0 ( ) #N/A #DIV/0! #N/A =5/0 (0) #DIV/0! #NAME? #NAME? Excel [ ] [ ] [ ] [ ] [ ] [ ] [ ] [ ] [OK] (") (") "The total amount is " B50 ="The total amount is "&B50 (:) SUM(A1:C10) (:) #N/A #N/A #N/A #N/A 11
HLOOKUPLOOKUPMATCH VLOOKUP " " " " VLOOKUP HLOOKUP VLOOKUP HLOOKUP " " " " FALSE 15 (C1:C15) 10 (A1:A10) C11:C15 "#N/A" (C1:C10 ) (A1:A15 ) #N/A #REF! #REF! ( ) #REF! 1 1 #REF! "system" DDE (Dynamic Data Exchange) DDE #NUM! 12
#NUM! IRRRATE Excel #NULL! #NULL! 2 2 (,) 2 2 (SUM(A1:A10,C1:C10)) A1:A10 C1:C10 4 + ( ) 3+3 - ( ) 3-1 -1 * ( ) 3*3 / ( ) 3/3 % ( ) 20% ^ ( ) 3^2 (3*3 ) 2 TRUE FALSE = ( ) A1=B1 > ( ) A1>B1 < ( ) A1<B1 >= ( ) A1>=B1 <= ( ) A1<=B1 13
<> ( ) A1<>B1 "&" 1 & ( ) 2 1 "North" & "wind" "Northwind" : ( ) 2 1 B5:B15, ( ) 1 SUM(B5:B15,D5:D15) ( 1 ) 2 1 SUM(B5:B15 A7:D7) B7 2 SUM PMT TRUE FALSE #N/A (,) (=) 14
#VALUE! AVERAGE 50 IF TRUE FALSE 7 A B B 2 B C C 3 B A A A ( ) ( ) / 1,000 2,500 "D" D 15
16 3 DAVERAGE DCOUNT DCOUNTA DGET 1 DMAX DMIN DPRODUCT DSTDEV DSTDEVP DSUM DVAR DVARP GETPIVOTDATA
17
18
19
GETPIVOTDATA GETPIVOTDATA 20
/ TODAY DATE NOW 21
22 DATEDIF DATEVALUE
23 DAY 1 31 DAYS360
24 EDATE EOMONTH EOMONTH HOUR 0 ( 0 ) 23 ( 11 )
MINUTE 0 () 59 () MONTH 1 () 12 () NETWORKDAYS 25
26 NOW SECOND 0 () 59 () SECOND
TIME 0 0.99999999 0:00:00 ( 0 ) 23:59:59 ( 11 59 59 ) TIMEVALUE 0 0.99999999 0:00:00 ( 0 ) 23:59:59 ( 11 59 59 ) TODAY Excel NOW WEEKDAY 27
28 WEEKNUM
WORKDAY [ ] [ ] [ ] [ ] [ ] [ ] 29
30 YEAR 1900 () 9999 () YEARFRAC 2 ( ) 1
31
32 BESSELI In(x) Jn BESSELJ Jn(x)
33 BESSELK Kn(x) Jn Yn BIN2DEC 2 10
34 BIN2HEX BIN2OCT 2 8
COMPLEX "x+yi" "x+yj" CONVERT 35
36
DEC2BIN 10 2 37
38 DEC2HEX DEC2OCT 10 8
39 DELTA 2 1 = 2 1 0 DELTA ERF
40 ERFC x GESTEP 1 0 ( ) GESTEP
41 HEX2BIN 16 2 HEX2DEC 16 10
42 HEX2OCT IMABS "x+yi" "x+yj"
43 IMAGINARY "x+yi" "x+yj" IMARGUMENT ( ) IMCONJUGATE "x+yi" "x+yj"
44 IMCOS "x+yi" "x+yj" IMDIV "x+yi" "x+yj" 2 IMEXP "x+yi" "x+yj"
IMLN "x+yi" "x+yj" IMLOG10 "x+yi" "x+yj" 10 ( ) IMLOG2 "x+yi" "x+yj" 2 45
IMPOWER "x+yi" "x+yj" IMPRODUCT "x+yi" "x+yj" 2 29 IMREAL "x+yi" "x+yj" 46
47 IMSIN "x+yi" "x+yj" IMSQRT "x+yi" "x+yj"
48 IMSUB "x+yi" "x+yj" 2 OCT2BIN 8 2
49 OCT2DEC 8 10 OCT2HEX 8 16
50 CELL CELL(, )
51
COUNTBLANK ERROR.TYPE Excel ERROR.TYPE ERROR.TYPE 52
53 INFO IS Excel 9
N 54
NA #N/A #N/A " " NA #N/A ( #N/A #N/A ) PHONETIC 55
56 TYPE TYPE AND TRUE TRUE 1 FALSE FALSE
57 FALSE FALSE IF TRUE () FALSE ()
NOT TRUE FALSE FALSE TRUE OR TRUE TRUE FALSE FALSE 58
TRUE TRUE / 59
60 ADDRESS ( ) ADDRESS AREAS 1
CHOOSE 1 CHOOSE 29 1 1 7 1 1 7 COLUMN 61
62 COLUMNS HLOOKUP HLOOKUP VLOOKUP
63 HYPERLINK
INDEX 64
65
66
67 INDIRECT
68 LOOKUP 1 1 LOOKUP 2
69
70 MATCH / MATCH
71 OFFSET ( )
72 ROW ROWS TRANSPOSE TRANSPOSE TRANSPOSE LINEST
73 y TRANSPOSE LINEST y VLOOKUP VLOOKUP HLOOKUP
/ 74
ABS (+-) ACOS 0( ) ( ) 75
ACOSH ( ) 1 ( ) ACOSH(COSH( )) = ASIN -/2 /2 ASINH ( ) ( ) ASINH(SINH( )) = ATAN -/2 /2 76
ATAN2 x-y x 0 x y -( - ) ATANH ( ) -1 1 ( ) ATANH(TANH( )) = CEILING 0 442 1 =CEILING(442,10) 10 77
78 COMBIN COMBIN COS
79 COSH ( ) COUNTIF DEGREES EVEN
80 EXP e e e = 2.71828182845904 FACT 1 FACTDOUBLE
81 FLOOR 0 GCD 1 2... INT
82 LCM 1 2... LCM LN e (2.71828182845904) LOG
83 LOG10 10 ( ) MDETERM MINVERSE
84 MMULT 2 1 2
85 MOD MROUND MULTINOMIAL
86 ODD PI 3.14159265358979 15 POWER
87 PRODUCT QUOTIENT ( ) RADIANS RANDBETWEEN 0 1
88 ROMAN ROUND
89 ROUNDDOWN ROUNDUP SERIESSUM
90 SIGN 10 0-1 SIN
91 SINH ( ) SQRT SQRTPI ( * )
92 SUBTOTAL [ ] ([ ] ) SUBTOTAL SUM
93 SUMIF SUMPRODUCT SUMSQ 2 ( )
94 SUMX2MY2 2 SUMX2PY2 2 SUMXMY2 2 2
95 TAN TANH ( ) TRUNC
96
AVEDEV AVEDEV 97
98 AVERAGE ( ) AVERAGEA ( ) TRUEFALSE
99 BETADIST 1 BETAINV = BETADIST(x,...) BETAINV(,...) = x
100 BINOMDIST
CHIDIST 2 (2) 2 2 2 CHIINV 2 (2) = CHIDIST(x,...) CHIINV(,...) = x 2 101
102 CHITEST 2 (2) CHITEST 2 2
103 CONFIDENCE CORREL 2 2
COUNT COUNTA 104
105 COVAR 2 2 CRITBINOM
106 DEVSQ EXPONDIST EXPONDIST 1
107 FDIST F 2 FINV F = FDIST(x,...) FINV(,...) = x
FISHER x FISHER FISHERINV y = FISHER(x) FISHERINV(y) = x FORECAST x y x y x (y) 108
109 FREQUENCY
110 FTEST F F 1 2 FTEST 2 GAMMADIST
111 GAMMAINV = GAMMADIST(x,...) GAMMAINV(,...) = x x GAMMALN G(x)
112 GEOMEAN GEOMEAN GROWTH y x x y GROWTH y x
113 HARMEAN 1 ( )
HYPGEOMDIST HYPGEOMDIST HYPGEOMDIST 2 114
INTERCEPT x y x y y 0( ) INTERCEPT 0C KURT 115
116 LARGE 1 LARGE LARGE 2 3 LINEST y LINEST
117
118
119
120 LOGEST LOGEST
121
LOGINV x ln(x) p = LOGNORMDIST(x,...) LOGINV(p,...) = x LOGNORMDIST 122
123 MAX MAXA TRUEFALSE
124 MEDIAN ( ) MIN
125 MINA TRUEFALSE MODE ( ) MODE MEDIAN NEGBINOMDIST NEGBINOMDIST BINOMDIST
126 NORMDIST NORMDIST(x,,, )
127 NORMINV PEARSON r r -1.0 1.0 2
128 POISSON 1 POISSON(,, ) PROB x x
129 QUARTILE QUARTILE 25% RANK
130 RSQ y x r2 PEARSON r2 x y SKEW
131 SLOPE y x 2 SMALL 1 SMALL STANDARDIZE
132 STDEV TDIST t t t
133 TINV t TREND y x ( ) x y
TRIMMEAN TRIMMEAN 134
135 TTEST t TTEST 2 VAR
136 VARA TRUEFALSE VARP
137 VARPA TRUEFALSE WEIBULL
ZTEST z P z x 138
139 ASC CHAR ASCII JIS CHAR
CLEAN CLEAN CLEAN CLEAN( ) CHAR(7) CLEAN(CHAR(7)&" "&CHAR(7)) = " " CODE ASCII JIS CODE( ) Macintosh Windows ANSI Macintosh CODE("A") = 65 CODE(" ") = 13377 CONCATENATE 1 CONCATENATE ( 1, 2,...) 1, 2,... 1 1 30 CONCATENATE "&" (") CONCATENATE(""," ") = " " = ""&" " C2 " C5 " " C8 "15%" CONCATENATE(" ",C5," ",C2," ",C8," ") = " 15% " DOLLAR [ ] [ ] 140
DOLLAR(, ) 2 DOLLAR [ ] ([ ] - [ ]) DOLLAR (") DOLLAR(1234.567, 2) = "$1,234.57" DOLLAR(1234.567, -2) = "$1,200" DOLLAR(-1234.567, -2) = "($1,200)" DOLLAR(-0.123, 4) = "($0.1230)" DOLLAR(99.888) = "$99.89" EXACT 2 TRUE FALSE EXACT EXACT EXACT( 1, 2) 1 2 EXACT("word","word") = TRUE EXACT("Word","word") = FALSE EXACT(" "," ") = FALSE Windows Excel 97 Enter Ctrl + Shift + Enter Macintosh Excel 97 return + return TestValue CompareRange {=OR(EXACT(TestValue, CompareRange))} FIND/FINDB ( ) ( ) FIND 1 1 FINDB ( ) SEARCH/SEARCHB FIND/FINDB FIND(,, ) 141
("") 1 1 3 3 1 #VALUE! 0 #VALUE! #VALUE! (") FIND("M","Miriam McGovern") = FINDB("M","Miriam McGovern") = 1 FIND(""," ") = 6 FINDB(""," ") = 11 FIND (#) MID A1 B2 A2:A4 " #124-TD45-87"" #12-671-6772"" #116010" MID(A2,1,FIND(" #",A2,1)-1) = " " MID(A3,1,FIND(" #",A3,1)-1) = " " MID(A4,1,FIND(" #",A4,1)-1) = " " FIXED (.) (,) FIXED(,, ) (,) TRUE FALSE Excel 15 127 2 FIXED [ ] ([ ] - [ ]) 142
FIXED (") FIXED(1234.567, 1) = "1234.6" FIXED(1234.567, -1) = "1230" FIXED(-1234.567, -1) = "-1230" FIXED(44.332) = "44.33" JIS JIS( ) (") JIS("EXCEL") = " " JIS("R1C1") = " " LEFT/LEFTB ( ) LEFT 1 1 LEFTB ( ) LEFT(, ) LEFTB(, ) ( ) ( ) 1 0 1 (") LEFT(" ",4) = " " LEFTB(" ",4) = " A1 "Japan" LEFT(A1) = LEFTB(A1) = "J" 143
- ( ) Excel ( ) -1 LEN 1 A2 "156-" "-156" LEFT(A2,LEN(A2)-1)*-1 LEN/LENB LEN 1 1 LENB ( ) LEN( ) LENB( ) LEN(" ") = 7 LENB(" ") = 13 LEN("") = LENB("") = 0 ("" ) LOWER LOWER( ) (") LOWER("Microsoft Excel") = "microsoft excel" LOWER(" ") = " " LOWER PROPER UPPER PROPER MID/MIDB MID 1 1 MIDB ( MID(,, ) MIDB(,, ) ( ) 1 ("") 144
( ) ( ) 1 #VALUE! #VALUE! (") MID(" ",1,4) = " " MIDB(" ",1,4) = " " MID("1234",5,5) = "" ( ) CODE FIND PROPER 2 PROPER( ) (") (") PROPER("this is a TITLE") = "This Is A Title" PROPER("2-cent's worth") = "2-Cent'S Worth" PROPER("EXCEL ") = "Excel " REPLACE/REPLACEB REPLACE 1 1 REPLACEB ( ) REPLACE(,,, ) REPLACEB(,,, ) ( ) 1 (") 9 4 145
REPLACE(" ",9,4," ") = " " 16 8 REPLACEB(" ",16,8," ") = " " 1992 2 93 REPLACE("1992",3,2,"93") = REPLACEB("1992",3,2,"93") = "1993" A2 " " REPLACE(A2,1,2,"*") = "* " REPLACEB(A2,1,2,"*") = "* " RIGHT "ABCDEF" REPLACE(RIGHT(A3,6),1,6,"*") = "*" REPT REPT(, ) 0( ) ("") REPT 32,000 (") REPT("*-",3) = "*-*-*-" A3 " " REPT($A$3,3.9) = " " RIGHT/RIGHTB ( ) RIGHT(, ) RIGHTB(, ) ( ) ( ) 0 1 146
(") RIGHT(" ",4) = " " RIGHTB(" ",4) = " RIGHT("Stock Number") = "r" RIGHT/RIGHTB LEFT/LEFTB LEFT SEARCH/SEARCHB SEARCH/SEARCHB MID REPLACE SEARCH 1 1 SEARCHB 1 ( 1 2 ) 1 SEARCH(,, ) SEARCHB(,, ) (?) (*) 1 " ~*" (~) 1 3 3 1 #VALUE! 0( ) #VALUE! #VALUE! "AYF0093. (Y )" "Y" 8 SEARCH 8 ( ) 5 "Y" 13 SEARCH ( ) SEARCH/SEARCHB SEARCH/SEARCHB FIND/FINDB SEARCH/SEARCHB SEARCH("e","Statements",6) = SEARCHB("e","Statements",6) = 7 SEARCH(""," ",2) = 3 147
SEARCHB(" "," ",2) = 5 B17 "margin" A14 "Profit Margin" SEARCH($B$17,$A$14) = 8 SEARCH REPLACE REPLACE 2 REPLACE($A$14,SEARCH($B$17,$A$14),6,"Amount") REPLACE "Profit Amount" SUBSTITUTE REPLACE SUBSTITUTE(,,, ) (") SUBSTITUTE("Sales Data","Sales","Cost") = "Cost Data" SUBSTITUTE("Quarter 1, 1991","1","2",1) = "Quarter 2, 1991" SUBSTITUTE("Quarter 1, 1991","1","2",3) = "Quarter 1, 1992" CellCont2 Separator SUBSTITUTE(CellCont2,Separator,"] [") T T() ("") Excel T T (") 148
B1 " " T(B1) = " " B2 19 T(B2) = "" T("True") = "True" T(TRUE) = "" TEXT TEXT(, ) [ ] ([ ] - [ ] [ ] ) (") (*) "G/ " [ ] ([ ] - [ ]) TEXT (") TEXT(2715," #,##0") = " 2,715" TEXT("4/15/93","mmmm dd, yyyy") = "April 15, 1993" TRIM 1 TRIM TRIM( ) (") TRIM(" First Quarter Earnings ") = "First Quarter Earnings" UPPER UPPER( ) (") UPPER("total") = "TOTAL" E5 "yield" UPPER(E5) = "YIELD" 149
VALUE VALUE( ) (") Excel #VALUE! Excel VALUE VALUE VALUE(" 1,000") = 1000 VALUE("16:48:00")-VALUE("12:00:00") = "16:48:00"-"12:00:00" = 0.2 (4 48 ) YEN Windows Excel [ ] [ ] Macintosh Excel [ ] [ ] YEN(, ) 0 YEN [ ] ([ ] - [ ]) YEN (") YEN(1234.567,2) = " 1,234.57" YEN(1234.567,-2) = " 1,200" YEN(-1234.567, -2) = " -1,200" YEN(-0.123,4) = " -0.1230" YEN(99.888) = " 100" 150