Microsoft Excel 2000 Functions in Practice

by Blattner, Patrick; Stewart, Laura

• ISBN13: 9780789720450
• ISBN10: 0789720450
• Format: Paperback w/CD
• Copyright: 1999-01-01
• Publisher: Que

Summary

The Microsoft Excel 2000 Functions in Practice provides in-depth information on the functions that will maximize your use of the analytical tools within Excel 2000. Packed with examples and detailed insight into both the functions and their uses, this reference includes more than just discussions of the functions<197>it offers practical, real-world usage of them with chapters on crucial topics such as using Excel's functions in your job, database and list-management functions, date and time functions, engineering functions, financial functions, information and logical functions, lookup and reference functions, math and trigonometry functions, and statistical and text functions.

Table of Contents

Functions Overview	5	(18)
Function Fundamentals	5	(3)
Functions Versus Formulas	5	(1)
Arguments	5	(1)
Operators	6	(1)
Operator Order	7	(1)
Custom Functions	7	(1)
Database- and List-Management Functions	8	(1)
Date and Time Functions	8	(1)
DDE and External Functions	9	(1)
Engineering Functions	9	(2)
Financial Functions	11	(3)
Information Functions	14	(1)
Logical Functions	15	(1)
Lookup and Reference Functions	15	(1)
Math and Trigonometry Functions	16	(2)
Statistical Functions	18	(3)
Text Functions	21	(2)
Managing Your Business with Functions	23	(30)
An Overview	23	(1)
Using Functions for Lookup and Reference, Math, and Trigonometry	24	(2)
VLOOKUP	24	(1)
MATCH	24	(1)
OFFSET	24	(1)
SUMIF	24	(1)
SUMIF with a Conditional Month	24	(1)
SUMIF with a Cell Reference Year Conditional and Month	25	(1)
SUBTOTAL	25	(1)
INDEX (Reference Form)	25	(1)
INDEX with a MATCH Condition	25	(1)
Working with Date and Time	26	(1)
TODAY	26	(1)
NOW	26	(1)
EDATE	26	(1)
NETWORKDAYS	27	(1)
Computing Statistics	27	(1)
AVERAGE	27	(1)
COUNTA	27	(1)
COUNTIF	27	(1)
MAX	28	(1)
MIN	28	(1)
STDEVA	28	(1)
FORECAST	28	(1)
Generating Financial Results	28	(2)
CUMIPMT	28	(1)
CUMPRINC	29	(1)
IPMT	29	(1)
FV	29	(1)
Producing Logical and Informational Data	30	(1)
IF	30	(1)
ISNUMBER	30	(1)
ISBLANK	30	(1)
Working with Text	31	(1)
CONCATENATE	31	(1)
PROPER	31	(1)
UPPER	31	(1)
RIGHT	31	(1)
MID	31	(1)
Important Tools for Any Business	32	(21)
Sell In Versus Sell Through	32	(1)
Channel Velocity	32	(1)
Cascading Schedules	33	(2)
Reverse Schedules	35	(1)
Summing the Total Velocity	36	(1)
Automating Projected Cash Flows	36	(1)
Transposing Tables with Formulas (without Absolute Referencing)	37	(1)
Averaging Positive Numbers Only in a Range	38	(1)
International Rate Converters	39	(1)
Rate Exchange Tables	40	(1)
Using the EUROCONVERT Formula	40	(1)
Applying a Named Range to Formulas	41	(1)
P&L---Direct Contribution	42	(1)
Financial Ratios	43	(3)
Resource Pools	46	(2)
Building Custom Functions	48	(1)
Producing a Line Item Milestone Management Chart	49	(1)
Ramping Up Production on a Single-Line Item	50	(1)
Counting Unique Items in a List	51	(2)
Database- and List-Management Functions	53	(12)
Database Functions Overview	53	(1)
General DFUNCTION Syntax	54	(1)
DAVERAGE	54	(1)
DCOUNT	55	(1)
DCOUNTA	56	(1)
DGET	56	(1)
DMAX	57	(1)
DMIN	58	(1)
DPRODUCT	58	(1)
DSTDEV	59	(1)
DSTDEVP	59	(1)
DSUM	60	(1)
DVAR	61	(1)
DVARP	62	(1)
GETPIVOTDATA	63	(2)
Date and Time Functions	65	(14)
Date and Time Functions Overview	65	(1)
DATE	66	(1)
DATEVALUE	66	(1)
DAY	67	(1)
DAYS360	68	(1)
EDATE	68	(1)
EOMONTH	69	(1)
HOUR	70	(1)
MINUTE	70	(1)
MONTH	71	(1)
NETWORKDAYS	72	(1)
NOW	73	(1)
SECOND	73	(1)
TIME	74	(1)
TIMEVALUE	74	(1)
TODAY	74	(1)
WEEKDAY	75	(1)
WEEKNUM	76	(1)
WORKDAY	77	(1)
YEAR	77	(1)
YEARFRAC	78	(1)
Engineering Functions	79	(34)
Engineering Functions Overview	79	(1)
BESSELI	80	(1)
BESSELJ	80	(1)
BESSELK	81	(1)
BESSELY	82	(1)
BIN2DEC	83	(1)
BIN2HEX	84	(1)
BIN2OCT	84	(1)
COMPLEX	85	(1)
CONVERT	86	(3)
DEC2BIN	89	(1)
DEC2HEX	90	(1)
DELTA	90	(1)
ERF	91	(1)
ERFC	92	(1)
GESTEP	92	(1)
HEX2BIN	93	(1)
HEX2DEC	94	(1)
HEX2OCT	94	(1)
IMABS	95	(1)
IMAGINARY	96	(1)
IMARGUMENT	97	(1)
IMCONJUGATE	97	(1)
IMCOS	98	(1)
IMDIV	99	(1)
IMEXP	100	(1)
IMLN	101	(1)
IMLOG10	101	(1)
IMLOG2	102	(1)
IMPOWER	103	(1)
IMPRODUCT	104	(1)
IMREAL	105	(1)
IMSIN	106	(1)
IMSQRT	106	(1)
IMSUB	107	(1)
IMSUM	108	(1)
OCT2BIN	109	(1)
OCT2DEC	110	(1)
OCT2HEX	110	(3)
Financial Functions	113	(44)
Financial Functions Overview	113	(1)
ACCRINT	114	(1)
ACCRINTM	115	(1)
AMORDEGRC	115	(1)
AMORLINC	116	(1)
COUPDAYBS	117	(1)
COUPDAYS	118	(1)
COUPDAYSNC	119	(1)
COUPNCD	119	(1)
COUPNUM	120	(1)
COUPPCD	121	(1)
CUMIPMT	122	(1)
CUMPRINC	122	(1)
DB	123	(1)
DDB	124	(1)
DISC	125	(1)
DOLLARDE	126	(1)
DOLLARFR	126	(1)
DURATION	127	(1)
EFFECT	127	(1)
FV	128	(1)
FVSCHEDULE	129	(1)
INTRATE	129	(1)
IPMT	130	(1)
IRR	131	(1)
ISPMT	132	(1)
MDURATION	132	(1)
MIRR	133	(1)
NOMINAL	134	(1)
NPER	134	(1)
NPV	135	(1)
ODDFPRICE	136	(2)
ODDFYIELD	138	(1)
ODDLPRICE	138	(1)
ODDLYIELD	139	(1)
PMT	140	(1)
PPMT	140	(1)
PRICE	141	(1)
PRICEDISC	142	(1)
PRICEMAT	143	(1)
PV	144	(1)
RATE	145	(1)
RECEIVED	146	(1)
SLN	147	(1)
SYD	148	(1)
TBILLEQ	148	(1)
TBILLPRICE	149	(1)
TBILLYIELD	150	(1)
VDB	151	(1)
XIRR	152	(1)
XNPV	152	(1)
YIELD	153	(1)
YIELDDISC	154	(1)
YIELDMAT	155	(2)
Information Functions	157	(10)
Information Functions Overview	157	(1)
CELL	158	(2)
COUNTBLANK	160	(1)
ERROR.TYPE	161	(1)
INFO	162	(1)
IS Functions	163	(1)
Using the ISBLANK Function	164	(1)
ISNUMBER	164	(1)
N	165	(1)
NA	166	(1)
TYPE	166	(1)
Logical Functions	167	(6)
Logical Functions Overview	167	(1)
AND	168	(1)
FALSE	169	(1)
IF	170	(1)
NOT	171	(1)
OR	172	(1)
TRUE	172	(1)
Lookup and Reference Functions	173	(18)
Lookup and Reference Functions Overview	173	(1)
ADDRESS	174	(1)
AREAS	174	(1)
CHOOSE	175	(1)
COLUMN	176	(1)
COLUMNS	177	(1)
HLOOKUP	177	(1)
HYPERLINK	178	(1)
INDEX (Array Form)	179	(1)
INDEX (Reference Form)	180	(2)
INDIRECT	182	(1)
LOOKUP (Array Form)	183	(1)
LOOKUP (Vector Form)	184	(1)
MATCH	184	(1)
OFFSET	185	(3)
ROW	188	(1)
ROWS	188	(1)
TRANSPOSE	188	(1)
VLOOKUP	189	(2)
Math and Trigonometry Functions	191	(34)
Math and Trigonometry Functions Overview	191	(1)
ABS	192	(1)
ACOS	192	(1)
ACOSH	193	(1)
ASIN	193	(1)
ASINH	194	(1)
ATAN	195	(1)
ATAN2	195	(1)
ATANH	196	(1)
CEILING	197	(1)
COMBIN	197	(1)
COS	198	(1)
COSH	199	(1)
COUNTIF	199	(1)
DEGREES	200	(1)
EVEN	201	(1)
EXP	201	(1)
FACT	202	(1)
FACTDOUBLE	203	(1)
FLOOR	203	(1)
GCD	204	(1)
INT	204	(1)
LCM	205	(1)
LN	205	(1)
LOG	206	(1)
LOG10	206	(1)
MDETERM	206	(1)
MINVERSE	207	(1)
MMULT	207	(1)
MOD	208	(1)
MROUND	208	(1)
MULTINOMIAL	208	(1)
ODD	209	(1)
PERMUT	209	(1)
PI	210	(1)
POWER	210	(1)
PRODUCT	210	(1)
QUOTIENT	211	(1)
RADIANS	211	(1)
RAND	212	(1)
RANDBETWEEN	212	(1)
ROMAN	213	(1)
ROUND	213	(1)
ROUNDDOWN	214	(1)
ROUNDUP	214	(1)
SERIESSUM	214	(1)
SIGN	215	(1)
SIN	215	(1)
SINH	216	(1)
SQRT	216	(1)
SQRTPI	216	(1)
SUBTOTAL	217	(1)
SUM	218	(1)
SUMIF	219	(1)
SUMPRODUCT	220	(1)
SUMSQ	221	(1)
SUMX2MY2	222	(1)
SUMX2PY2	222	(1)
SUMXMY2	222	(1)
TAN	223	(1)
TANH	223	(1)
TRUNC	223	(2)
Statistical Functions	225	(54)
Statistical Functions Overview	225	(1)
AVEDEV	226	(1)
AVERAGE	226	(1)
AVERAGEA	227	(1)
BETADIST	227	(1)
BETAINV	228	(1)
BINOMDIST	228	(2)
CHIDIST	230	(1)
CHIINV	230	(1)
CHITEST	231	(1)
CONFIDENCE	231	(1)
CORREL	232	(1)
COUNT	233	(1)
COUNTA	234	(1)
COUNTBLANK	234	(1)
COUNTIF	235	(1)
COVAR	236	(1)
CRITBINOM	236	(1)
DEVSQ	237	(1)
EXPONDIST	238	(1)
FDIST	239	(1)
FINV	240	(1)
FISHER	240	(1)
FISHERINV	240	(1)
FORECAST	241	(1)
FREQUENCY	241	(1)
FTEST	242	(1)
GAMMADIST	243	(1)
GAMMAINV	244	(1)
GAMMALN	244	(1)
GEOMEAN	244	(1)
GROWTH	245	(1)
HARMEAN	246	(1)
HYPGEOMDIST	246	(1)
INTERCEPT	247	(1)
KURT	248	(1)
LARGE	248	(1)
LINEST	249	(1)
LOGEST	250	(1)
LOGINV	251	(1)
LOGNORMDIST	252	(1)
MAX	252	(1)
MAXA	253	(1)
MEDIAN	253	(1)
MIN	254	(1)
MINA	254	(1)
MODE	254	(1)
NEGBINOMDIST	255	(1)
NORMDIST	256	(1)
NORMINV	257	(1)
NORMSDIST	258	(1)
NORMSINV	258	(1)
PEARSON	258	(1)
PERCENTILE	259	(1)
PERCENTRANK	260	(1)
PERMUT	261	(1)
POISSON	261	(1)
PROB	262	(1)
QUARTILE	263	(1)
RANK	264	(1)
RSQ	264	(1)
SKEW	265	(1)
SLOPE	266	(1)
SMALL	267	(1)
STANDARDIZE	267	(1)
STDEV	268	(1)
STDEVA	269	(1)
STDEVP	269	(1)
STDEVPA	270	(1)
STEYX	270	(1)
TDIST	271	(1)
TINV	272	(1)
TREND	272	(1)
TRIMMEAN	273	(1)
TTEST	274	(1)
VAR	274	(1)
VARA	275	(1)
VARP	275	(1)
VARPA	276	(1)
WEIBULL	276	(1)
ZTEST	277	(2)
Text Functions	279	(18)
Text Functions Overview	279	(1)
ASC	280	(1)
CHAR	280	(1)
CLEAN	281	(1)
CODE	281	(1)
CONCATENATE	282	(1)
DOLLAR	282	(1)
EXACT	283	(1)
FIND	284	(1)
FIXED	285	(1)
JIS	286	(1)
LEFT	286	(1)
LEN	287	(1)
LOWER	288	(1)
MID	288	(1)
PHONETIC	289	(1)
PROPER	290	(1)
REPLACE	290	(1)
REPT	291	(1)
RIGHT	291	(1)
SEARCH	292	(1)
SUBSTITUTE	292	(1)
T	293	(1)
TEXT	293	(1)
TRIM	294	(1)
UPPER	295	(1)
VALUE	296	(1)
YEN	296	(1)
Index	297