You’ll find the sample files that are used in this book at:
http://go.microsoft.com/FWLink/?Linkid=229604
The Microsoft Excel 2010 software is not available from this website. You should purchase and install that software before using this book.
Microsoft offers special updates and file converters for opening an Excel 2007 and Excel 2010 workbook in a previous Excel versions (97–2003). If the converters are not installed with the Office update, you might be prompted to install them when opening an Excel 2007 or Excel 2010 workbook.
After you install the updates and the converter, you can open Excel 2007 and Excel 2010 workbooks. You can edit and save the workbooks. However, the new features and formats of Excel 2007 and Excel 2010 are not displayed in previous Excel versions. You will find detailed information at the following Microsoft website: http://office.microsoft.com/en-us/excel/HA100775611031.aspx
You should also read the information that is provided for the sample files in each chapter.
The following table lists the names of the sample files that are used in the book. Because some users who are working with older Excel versions (Excel 2000 through Excel 2003) might not be able to open the new file formats of Excel 2007 and Excel 2010 (.xlsx, .xlsm, and so on), the sample files are provided in both formats: .xls (Excel 97 through Excel 2003) and .xlsx (Excel 2007 and Excel 2010).
When you open a sample file in Excel and then close Excel, you can save the file in the same format or in a different format. By default, Excel offers the standard format again. However, if you choose the standard format (*.xlsx), Excel will display a message box that contains the information that you are going to save your work as a macro-free workbook. You should click No and save your work in a macro-enabled file type (*.xlsm).
Chapter Folder | Workbook/Worksheet Name | Location and/or Description |
|---|---|---|
Chapter01 | Excel_Example.xls or Excel_Example.xlsx | Chapter01 folder |
Excel_Pivot_Data.xls or Excel_Pivot_Data.xlsx | Chapter01 folder | |
Chapter02 | InformationFunctions.xls or InformationFunctions.xlsx | Chapter02 folder |
Date_Time.xls or Date_Time.xlsx | Chapter02 folder | |
Text_Data.xls or Text_Data.xlsx | Chapter02 folder | |
DifferentialCalculation.xls or DifferentialCalculation.xlsx | Chapter02 folder | |
Lookup.xls or Lookup.xlsx | Chapter02 folder | |
Practice_Statistics.xls or Practice_Statistics.xlsx | Chapter02 folder | |
DBFunction empty.xls or DBFunction empty.xlsx | Chapter02 folder | |
Practice_Database.xls or Practice_Database.xlsx | Chapter02 folder | |
Financial mathematics.xls or Financial mathematics.xlsx | Chapter02 folder | |
Math.xls or Math.xlsx | Chapter02 folder | |
Chapter03 | Arithmetic Operators | Chapter03.xls or Chapter03.xlsx |
Priority | Chapter03.xls or Chapter03.xlsx | |
Comparison Operators | Chapter03.xls or Chapter03.xlsx | |
Text Operator | Chapter03.xls or Chapter03.xlsx | |
Relative Reference | Chapter03.xls or Chapter03.xlsx | |
Absolute Reference | Chapter03.xls or Chapter03.xlsx | |
Mixed Reference | Chapter03.xls or Chapter03.xlsx | |
Array Formula | Chapter03.xls or Chapter03.xlsx | |
Chapter05 | Fct_Names | Chapter05_Names.xls or Chapter05_Names.xlsx |
Comparison | Chapter05_CF.xls or Chapter05_CF.xlsx | |
Training | Chapter05_Names.xls or Chapter05_Names.xlsx | |
Weekend | Chapter05_CF.xls or Chapter05_CF.xlsx | |
Holidays | Chapter05_CF.xls or Chapter05_CF.xlsx | |
Accident1, Accident2 | Chapter05_CF.xls or Chapter05_CF.xlsx | |
Print Layout | Chapter05_CF.xls or Chapter05_CF.xlsx | |
Top3 | Chapter05_CF.xls or Chapter05_CF.xlsx | |
Empty | Chapter05_CF.xls or Chapter05_CF.xlsx | |
Credit | Chapter05_CF.xls or Chapter05_CF.xlsx | |
Groups | Chapter05_CF.xls or Chapter05_CF.xlsx | |
Subtotals | Chapter05_CF.xls or Chapter05_CF.xlsx | |
Depending list, Paygrouplist | Chapter05_DV.xls or Chapter05_DV.xlsx | |
Duplicates | Chapter05_DV.xls or Chapter05_DV.xlsx | |
Completed | Chapter05_DV.xls or Chapter05_DV.xlsx | |
Chapter06 | Circle | Geometry.xls or Geometry.xlsm |
Fundamentals | Geometry.xls or Geometry.xlsm | |
Quadrilateral | Geometry.xls or Geometry.xlsm | |
Sector of a circle | Geometry.xls or Geometry.xlsm | |
Chapter07 | WORKDAY | Date_Time.xls or Date_Time.xlsx |
WORKDAY.INTL | Date_Time.xlsx | |
Practice | Date_Time.xls or Date_Time.xlsx | |
YEARFRAC | Date_Time.xls or Date_Time.xlsx | |
DATEDIF | Date_Time.xls or Date_Time.xlsx | |
DATE | Date_Time.xls or Date_Time.xlsx | |
DATEVALUE | Date_Time.xls or Date_Time.xlsx | |
EDATE | Date_Time.xls or Date_Time.xlsx | |
TODAY | Date_Time.xls or Date_Time.xlsx | |
YEAR | Date_Time.xls or Date_Time.xlsx | |
NOW | Date_Time.xls or Date_Time.xlsx | |
WEEKNUM | Date_Time.xls or Date_Time.xlsx | |
MINUTE | Date_Time.xls or Date_Time.xlsx | |
MONTH | Date_Time.xls or Date_Time.xlsx | |
EOMONTH | Date_Time.xls or Date_Time.xlsx | |
NETWORKDAYS | Date_Time.xls or Date_Time.xlsx | |
NETWORKDAYS.INTL | Date_Time.xlsx | |
SECOND | Date_Time.xls or Date_Time.xlsx | |
HOUR | Date_Time.xls or Date_Time.xlsx | |
DAY | Date_Time.xls or Date_Time.xlsx | |
DAYS360 | Date_Time.xls or Date_Time.xlsx | |
WEEKDAY | Date_Time.xls or Date_Time.xlsx | |
TIME | Date_Time.xls or Date_Time.xlsx | |
TIMEVALUE | Date_Time.xls or Date_Time.xlsx | |
Chapter08 | ASC | Text_Data.xls or Text_Data.xlsx |
BAHTTEXT | Text_Data.xls or Text_Data.xlsx | |
CODE | Text_Data.xls or Text_Data.xlsx | |
DOLLAR | Text_Data.xls or Text_Data.xlsx | |
REPLACE | Text_Data.xls or Text_Data.xlsx | |
FIXED | Text_Data.xls or Text_Data.xlsx | |
FIND | Text_Data.xls or Text_Data.xlsx | |
TRIM | Text_Data.xls or Text_Data.xlsx | |
UPPER | Text_Data.xls or Text_Data.xlsx | |
PROPER | Text_Data.xls or Text_Data.xlsx | |
EXACT | Text_Data.xls or Text_Data.xlsx | |
LOWER | Text_Data.xls or Text_Data.xlsx | |
LEN | Text_Data.xls or Text_Data.xlsx | |
LEFT | Text_Data.xls or Text_Data.xlsx | |
RIGHT | Text_Data.xls or Text_Data.xlsx | |
CLEAN | Text_Data.xls or Text_Data.xlsx | |
SEARCH | Text_Data.xls or Text_Data.xlsx | |
T | Text_Data.xls or Text_Data.xlsx | |
MID | Text_Data.xls or Text_Data.xlsx | |
TEXT | Text_Data.xls or Text_Data.xlsx | |
CONCATENATE | Text_Data.xls or Text_Data.xlsx | |
SUBSTITUTE | Text_Data.xls or Text_Data.xlsx | |
VALUE | Text_Data.xls or Text_Data.xlsx | |
REPT | Text_Data.xls or Text_Data.xlsx | |
CHAR | Text_Data.xls or Text_Data.xlsx | |
Chapter09 | Samples | Logical_values.xls or Logical_values.xlsx |
Properties and Interpretation | Logical_values.xls or Logical_values.xlsx | |
IF | Logical_values.xls or Logical_values.xlsx | |
Chapter10 | Address.xls or Address.xlsx | Chapter10 folder |
Offset.xls or Offset.xlsx | Chapter10 folder | |
References.xls or References.xlsx | Chapter10 folder | |
Misc | References.xls or References.xlsx | |
Index | References.xls or References.xlsx | |
MTRANS | References.xls or References.xlsx | |
exampleRTD.xls, exampleRTD.xlsx, or exampleRTD.xlsm, exampleRTD.dll | Chapter10 folder | |
Column-Row | References.xls or References.xlsx | |
LOOKUP | Lookups.xls or Lookups.xlsx | |
test.doc or test.docx | Chapter10 folder | |
VLOOKUP | Lookups.xls or Lookups.xlsx | |
MATCH | Lookups.xls or Lookups.xlsx | |
HLOOKUP | Lookups.xls or Lookups.xlsx | |
Chapter11 | Misc | Informations.xls or Informations.xlsm |
Error | Informations.xls or Informations.xlsm | |
Info | Informations.xls or Informations.xlsm | |
IS-functions | Informations.xls or Informations.xlsm | |
ISEVEN | Informations.xls or Informations.xlsm | |
ISODD | Informations.xls or Informations.xlsm | |
NA | Informations.xls or Informations.xlsm | |
Cell | Informations.xls or Informations.xlsm | |
Chapter12 | INTERCEPT | Regression.xls or Regression.xlsx |
RSQ | Regression.xls or Regression.xlsx | |
BETAINV or BETA.INV | Probability.xls or Probability.xlsx | |
BETADIST or BETA.DIST | Probability.xls or Probability.xlsx | |
BINOM.INV | Probability.xlsx | |
BINOMDIST or BINOM.DIST | Probability.xls or Probability.xlsx | |
CHIINV or CHI.INV | Probability.xls or Probability.xlsx | |
CHISQ.INV or CHISQ.INV.RT | Probability.xlsx | |
CHITEST or CHI.TEST or CHISQ.TEST | Probability.xls or Probability.xlsx | |
CHIDIST or CHISQ.DIST or CHISQ.DIST.RT | Probability.xls or Probability.xlsx | |
CRITBINOM | Probability.xls or Probability.xlsx | |
EXPONDIST or EXPON.DIST | Probability.xls or Probability.xlsx | |
FINV or F.INV or F.INV.RT | Probability.xls or Probability.xlsx | |
FISHER | Probability.xls or Probability.xlsx | |
FISHERINV or FISHER.INV | Probability.xls or Probability.xlsx | |
FTEST or F.TEST | Probability.xls or Probability.xlsx | |
FDIST or F.DIST or F.DIST.RT | Probability.xls or Probability.xlsx | |
GAMMAINV or GAMMA.INV | Probability.xls or Probability.xlsx | |
GAMMALN | Probability.xls or Probability.xlsx | |
GAMMALN.PRECISE | Probability.xls or Probability.xlsx | |
GAMMADIST or GAMMA.DIST | Probability.xls or Probability.xlsx | |
GEOMEAN | Average.xls or Average.xlsx | |
TRIMMEAN | Average.xls or Average.xlsx | |
ZTEST or Z.TEST | Probability.xls or Probability.xlsx | |
HARMEAN | Average.xls or Average.xlsx | |
Web access | WEBACCESS.xls or WEBACCESS.xlsx | |
COUNT | Count.xls or Count.xlsx | |
COUNTA | Count.xls or Count.xlsx | |
COUNTBLANK | Count.xls or Count.xlsx | |
FREQUENCY | Count.xls or Count.xlsx | |
HYPGEOMDIST or HYPGEOM.DIST | Probability.xls or Probability.xlsx | |
LARGE | Count.xls or Count.xlsx | |
SMALL | Count.xls or Count.xlsx | |
CONFIDENCE or CONFIDENCE.NORM | Probability.xls or Probability.xlsx | |
CONFIDENCE.T | Probability.xls or Probability.xlsx | |
CORREL | Probability.xls or Probability.xlsx | |
COVAR | Regression.xls or Regression.xlsx | |
COVARIANCE.P | Regression.xls or Regression.xlsx | |
COVARIANCE.S | Regression.xls or Regression.xlsx | |
KURT | Symmetry.xls or Symmetry.xlsx | |
LOGINV or LOGNORM.INV | Probability.xls or Probability.xlsx | |
LOGNORMDIST or LOGNORM.DIST | Probability.xls or Probability.xlsx | |
MAX&MIN | Count.xls or Count.xlsx | |
MAXA&MINA | Count.xls or Count.xlsx | |
MEDIAN | Average.xls or Average.xlsx | |
AVEDEV | Variance.xls or Variance.xlsx | |
AVERAGE | Average.xls or Average.xlsx | |
AVERAGEA | Average.xls or Average.xlsx | |
AVERAGEIF | Average.xls or Average.xlsx | |
AVERAGEIFS | Average.xlsx | |
MODE or MODE.SNGL | Average.xls or Average.xlsx | |
MODE.MULT | Average.xlsx | |
NEGBINOMDIST or NEGBINOM.DIST | Probability.xls or Probability.xlsx | |
NORMINV or NORM.INV | Probability.xls or Probability.xlsx | |
NORMSINV or NORM.S.INV | Probability.xls or Probability.xlsx | |
NORMDIST, NORMSDIST or NORM.DIST or NORM.S.DIST | Probability.xls or Probability.xlsx | |
PEARSON | Regression.xls or Regression.xlsx | |
POISSON or POISSON.DIST | Probability.xls or Probability.xlsx | |
PERCENTILE | Average.xls or Average.xlsx | |
PERCENTILE.INC or PERCENTILE.EXC | Average.xlsx | |
PERCENTRANK | Average.xls or Average.xlsx | |
PERCENTRANK.INC or PERCENTRANK.EXC | Average.xlsx | |
QUARTILE | Average.xls or Average.xlsx | |
QUARTILE.INC or QUARTILE.EXC | Average.xlsx | |
RANK | Count.xls or Count.xlsx | |
RANK.EQ | Count.xlsx | |
RANK.AVG | Count.xlsx | |
LINEST | Regression.xls or Regression.xlsx | |
LOGEST | Regression.xls or Regression.xlsx | |
FORECAST | Regression.xls or Regression.xlsx | |
SKEW | Symmetry.xls or Symmetry.xlsx | |
STDEV or STDEV.S | Variance.xls or Variance.xlsx | |
STDEVA | Variance.xlsx | |
STDEVP or STDEV.P | Variance.xls or Variance.xlsx | |
STDEVPA | Variance.xlsx | |
STANDARDIZE | Probability.xls or Probability.xlsx | |
SLOPE | Regression.xls or Regression.xlsx | |
STEYX | Probability.xls or Probability.xlsx | |
DEVSQ | Regression.xls or Regression.xlsx | |
TINV or T.INV or T.INV.2T | Probability.xls or Probability.xlsx | |
TREND | Regression.xls or Regression.xlsx | |
TTEST or T.TEST | Probability.xls or Probability.xlsx | |
TDIST or T.DIST or T.DIST.2T | Probability.xls or Probability.xlsx | |
T.DIST.RT | Probability.xlsx | |
VAR or VAR.S | Variance.xls or Variance.xlsx | |
VARA | Variance.xls or Variance.xlsx | |
VARP or VAR.P | Variance.xls or Variance.xlsx | |
VARPA | Variance.xls or Variance.xlsx | |
GROWTH | Regression.xls or Regression.xlsx | |
PERMUT | Probability.xls or Probability.xlsx | |
PROB | Probability.xls or Probability.xlsx | |
WEIBULL or WEIBULL.DIST | Probability.xls or Probability.xlsx | |
COUNTIF | Count.xls or Count.xlsx | |
COUNTIFS | Count.xlsx | |
Chapter13 | Raw data | DBFUNCTION_empty.xls or DBFUNCTION_empty.xlsx |
DCOUNT | DBFUNCTION2.xls or DBFUNCTION2.xlsx | |
DCOUNTA | DBFUNCTION2.xls or DBFUNCTION2.xlsx | |
DGET | DBFUNCTION2.xls or DBFUNCTION2.xlsx | |
DMAX or DMIN | DBFUNCTION2.xls or DBFUNCTION2.xlsx | |
DAVERAGE | DBFUNCTION2.xls or DBFUNCTION2.xlsx | |
DPPRODUCT | DBFUNCTION2.xls or DBFUNCTION2.xlsx | |
DSTDEV | DBFUNCTION2.xls or DBFUNCTION2.xlsx | |
DSTDEVP | DBFUNCTION2.xls or DBFUNCTION2.xlsx | |
DSUM | DBFUNCTION2.xls or DBFUNCTION2.xlsx | |
DVAR | DBFUNCTION2.xls or DBFUNCTION2.xlsx | |
DVARP | DBFUNCTION2.xls or DBFUNCTION2.xlsx | |
GETPIVOTDATA | DBFUNCTION2.xls or DBFUNCTION2.xlsx | |
Chapter14 | Cube Test.cub | Use the offline cube file to test the cube functions without Microsoft Analysis Services |
offline cubeTest.xlsx | This workbook contains the example applications for the cube functions | |
offLine.odc | Use the office data connection file to access data through workbook connections | |
offLine.oqy | Use the office data connection file to access data through Microsoft Query | |
Chapter15 | AMORDEGRC | Depreciation Calculation.xls or Depreciation Calculation.xlsx |
AMORLINC | Depreciation Calculation.xls or Depreciation Calculation.xlsx | |
ACCRINT | Simple Interest Calculation.xls or Simple Interest Calculation.xlsx | |
ACCRINTM | Simple Interest Calculation.xls or Simple Interest Calculation.xlsx | |
Bill of Exchange | Simple Interest Calculation.xls or Simple Interest Calculation.xlsx | |
PV | Compound Interest Calculation.xls or Compound Interest Calculation.xlsx; Repayment Calculation.xls or Repayment Calculation.xlsx; Annuity Calculation.xls or Annuity Calculation.xlsx | |
SYD | Depreciation Calculation.xls or Depreciation Calculation.xlsx | |
Treasury Bonds | Simple Interest Calculation.xls or Simple Interest Calculation.xlsx | |
DURATION | Price Calculation.xls or Price Calculation.xlsx | |
EFFECT | Compound Interest Calculation.xls or Compound Interest Calculation.xlsx | |
DDB | Depreciation Calculation.xls or Depreciation Calculation.xlsx | |
DB | Depreciation Calculation.xls or Depreciation Calculation.xlsx | |
IRR | Investment Calculation.xls or Investment Calculation.xlsx | |
ISPMT | Simple Interest Calculation.xls or Simple Interest Calculation.xlsx | |
PRICE | Price Calculation.xls or Price Calculation.xlsx | |
SLN | Depreciation Calculation.xls or Depreciation Calculation.xlsx | |
MDURATION | Price Calculation.xls or Price Calculation.xlsx | |
NPV | Investment Calculation.xls or Investment Calculation.xlsx | |
DOLLARFR | Other.xls or Other.xlsx | |
DOLLARDE | Other.xls or Other.xlsx | |
MIRR | Investment Calculation.xls or Investment Calculation.xlsx | |
YIELD | Price Calculation.xls or Price Calculation.xlsx | |
YIELDMAT | Simple Interest Calculation.xls or Simple Interest Calculation.xlsx | |
PMT | Annuity Calculation.xls or Annuity Calculation.xlsx | |
TBILL | Simple Interest Calculation.xls or Simple Interest Calculation.xlsx | |
ODDFPRICE | Price Calculation.xls or Price Calculation.xlsx | |
ODDFYIELD | Price Calculation.xls or Price Calculation.xlsx | |
ODDLPRICE | Simple Interest Calculation.xls or Simple Interest Calculation.xlsx | |
ODDLYIELD | Simple Interest Calculation.xls or Simple Interest Calculation.xlsx | |
VDB | Found in Depreciation Calculation.xls or Depreciation Calculation.xlsx | |
XIRR | Investment Calculation.xls or Investment Calculation.xlsx | |
XNPV | Investment Calculation.xls or Investment Calculation.xlsx | |
RATE | Compound Interest Calculation.xls or Compound Interest Calculation.xlsx; Annuity Calculation.xls or Annuity Calculation.xlsx | |
PRICE and YIELD | Price Calculation.xls or Price Calculation.xlsx | |
FV | Compound Interest Calculation.xls or Compound Interest Calculation.xlsx; Annuity Calculation.xls or Annuity Calculation.xlsx; Repayment Calculation.xls or Repayment Calculation.xlsx | |
FVSCHEDULE | Compound Interest Calculation.xls or Compound Interest Calculation.xlsx | |
NPER | Compound Interest Calculation.xls or Compound Interest Calculation.xlsx; Annuity Calculation.xls or Annuity Calculation.xlsx; or Repayment Calculation.xls or Repayment Calculation.xlsx | |
Chapter16 | AGGREGATE.xlsx | Chapter16 folder |
ARRAY_FUNCTION | Chapter16.xls or Chapter16.xlsx | |
ARRAY_FUNCTION2 | Chapter16.xls or Chapter16.xlsx | |
ATAN2 | Chapter16.xls or Chapter16.xlsx | |
COMBIN | Chapter16.xls or Chapter16.xlsx | |
Data&Chart and Example | ACosH.xls or ACosH.xlsx | |
Data&Chart | ASinH.xls or ASinH.xlsx | |
Data&Chart | ATanH.xls or ATanH.xlsx | |
Data&Chart and Example | CosH.xls or CosH.xlsx | |
Data&Chart and Distribution | SinH.xls or SinH.xlsx | |
Data&Chart and Water waves | TanH.xls or TanH.xlsx | |
EXP | Chapter16.xls or Chapter16.xlsx | |
EXP_LOG_LN | Chapter16.xls or Chapter16.xlsx | |
FACT | Chapter16.xls or Chapter16.xlsx | |
FLOOR | Chapter16.xls or Chapter16.xlsx | |
GCD_LCM | Chapter16.xls or Chapter16.xlsx | |
LOG_LN | Chapter16.xls or Chapter16.xlsx | |
MOD | Chapter16.xls or Chapter16.xlsx | |
POWER | Chapter16.xls or Chapter16.xlsx | |
PRODUCT | Chapter16.xls or Chapter16.xlsx | |
PRODUCT_SUM | Chapter16.xls or Chapter16.xlsx | |
RAND | Chapter16.xls or Chapter16.xlsx | |
ROMAN | Chapter16.xls or Chapter16.xlsx | |
ROUND | Chapter16.xls or Chapter16.xlsx | |
ROUNDUP | Chapter16.xls or Chapter16.xlsx | |
Seriessum | Seriessum.xls or Seriessum.xlsx | |
SIGN | Chapter16.xls or Chapter16.xlsx | |
SIN_COS | Chapter16.xls or Chapter16.xlsx | |
SQRT | Chapter16.xls or Chapter16.xlsx | |
SUBTOTAL | Chapter16.xls or Chapter16.xlsx | |
SUBTOTAL2 | Chapter16.xls or Chapter16.xlsx | |
SUMIF | Chapter16.xls or Chapter16.xlsx | |
SUMIFS | Chapter16.xls or Chapter16.xlsx | |
SUMIFS2 | Chapter16.xls or Chapter16.xlsx | |
SUMPRODUCT | Chapter16.xls or Chapter16.xlsx | |
SUMXY | Chapter16.xls or Chapter16.xlsx | |
trigon | Chapter16.xls or Chapter16.xlsx | |
Binary | Numbers.xls or Numbers.xlsx | |
Decimal | Numbers.xls or Numbers.xlsx | |
Hexadecimal | Numbers.xls or Numbers.xlsx | |
Octal | Numbers.xls or Numbers.xlsx | |
Sheet1 | Convert.xls or Convert.xlsx | |
Complex | Complex.xls or Complex.xlsm | |
BESSEL I and Chart | Bessel_I.xls or Bessel_I.xlsx | |
BESSEL J and Chart | Bessel_J.xls or Bessel_J.xlsx | |
Bessel K and Chart | Bessel_K.xls or Bessel_K.xlsx | |
Bessel Y and Chart | Bessel_Y.xls or Bessel_Y.xlsx | |
Data and Chart | Gauss.xls or Gauss.xlsx | |
Sheet1 | Delta.xls or Delta.xlsx | |
Longjump | GESTEP.xls or GESTEP.xlsx |