Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

The following sections provide more details about the software and other materials available on the CD.

Example files for Excel 2007 Formulas

Most of the chapters in this book refer to workbook files that are available on the CD-ROM. Each chapter has its own subdirectory on the CD. For example, you can find the files for Chapter 5 in the following directory:

examples\chapter 05

The files are all Excel 2007 workbook files that have either of the following extensions:

• XLSX: An Excel workbook file

• XLSM: An Excel workbook file that contains VBA macros

When you open an XLSM file, Excel may display a Security Warning, and tells you that macros have been disabled. To enable macros, click the Options button in the Security Warning panel, and then select the option labeled Enable This Content.

Because the files on this CD are from a trusted source, you may want to copy the files to your hard drive, and then designate the folder as a trusted location. To do so, follow these steps:

1. Open an Explorer window, and select the CD-ROM drive that contains the companion CD-ROM.

2. Right-click the folder that corresponds to the root folder for the samples files, and select Copy from the shortcut menu.

3. Activate the folder on your hard drive where you'd like to copy the files. Right-click the directory, and choose Paste from the shortcut menu.

The CD-ROM files will be copied to a subfolder in the folder you specified in Step 3. To designate this new folder as a trusted location:

1. Start Excel and choose Office Excel Options to display the Excel Options dialog box.

2. In the Excel Options dialog box, click the Trust Center tab.

3. Click the Trust Center Settings button.

4. In the Trust Center dialog box, click the Trusted Locations tab.

5. Click the Add New Location button to display the Microsoft Office Trusted Location dialog box.

6. In the Microsoft Office Trusted Location dialog box, click the Browse button, and locate the folder the contains the files copied from the CD-ROM.

7. Make sure you select the option labeled Subfolders Of This Location Are Also Trusted.

After performing these steps, when you open XLSM files from this location, the macros are enabled and you don't see the security warning.

Following is a list of the chapter example workbooks with a brief description of each. Note that not all chapters have example files.

CHAPTER 1

•  worksheet controls.xlsx-Demonstrates that use of controls placed directly on a worksheet.

CHAPTER 5

•  character set.xlsm-Displays the characters in any font installed on your system.

•  text formula examples.xlsx-Contains examples of formulas that work with text.

•  text histogram.xlsx-Demonstrates how to create a simple histogram directly in a range.

CHAPTER 6

•  day of the week count.xlsx-Demonstrates how to count the occurrences of a day of the week.

•  gmt conversion.xlsx-Demonstrates how to convert times between time zones.

•  holidays.xlsx-Contains formulas to calculate the dates of various U.S. holidays.

•  jogging log.xlsx-Demonstrates how to work with time values that do not represent a time of day.

•  ordinal dates.xlsx-Demonstrates a formula to express a date as an ordinal number.

•  time sheet.xlsm-Calculates a weekly time sheet.

•  work days.xlsx-Demonstrates the NETWORKDAYS function.

CHAPTER 7

•  adjustable bins.xlsx-Demonstrates formula that create adjustable bins for a frequency distribution.

•  basic counting.xlsx-Demonstrates some basic counting formulas.

•  conditional summing.xlsx-Demonstrates various ways to calculate conditional sums.

•  count unique.xlsx-Demonstrates how to count unique (nonduplicated) entries in a range.

•  counting text in a range.xlsx-Contains various formulas that count occurrences of specific text.

•  cumulative sum.xlsx-Demonstrates how to display a cumulative sum a values.

•  frequency distribution.xlsx-Demonstrates three ways to create a frequency distribution.

•  multiple criteria counting.xlsx-Demonstrates formulas that perform multiple criteria counting.

CHAPTER 8

•  basic lookup examples.xlsx-Contains examples of lookup formulas.

•  specialized lookup examples.xlsx-Contains examples of specialized lookup formulas.

CHAPTER 9

•  database formulas.xlsx-Demonstrates database functions.

•  nested subtotals.xlsx-Demonstrates how to create nested subtotals.

•  real estate database.xlsx-Contains a table of real estate listings, used to demonstrate advanced filtering.

•  real estate table.xlsx-Contains a table of real estate listings, used to demonstrate sorting and filtering.

•  table formulas.xlsx-Demonstrate how to use structured references to data within a table.

CHAPTER 10

•  simultaneous equations.xlsx-Demonstrates how to solve simultaneous equations using matrix functions.

•  solve right triangle.xlsm-Demonstrates how to solve right triangles.

•  unit conversion tables.xlsx-Contains conversion factors for a variety of measurement units.

CHAPTER 11

•  basic financial formulas.xlsx-Demonstrates various financial functions: PV, FV, PMT, RATE, and NPER.

•  bond calculations.xlsx-Demonstrates the PRICE and YIELD functions.

•  extending basic functions.xlsx-Demonstrates how to combine various financial functions.

•  payment components.xlsx-Demonstrates the IPMT and PPMT functions.

•  rate conversion.xlsx-Demonstrates the EFFECT and NOMINAL functions.

CHAPTER 12

•  depreciation.xlsx-Demonstrates the depreciation functions.

•  fvschedule.xlsx-Demonstrates the FVSCHEDULE function.

•  internal rate of return.xlsx-Demonstrates the IRR function.

•  irregular cash flows.xlsx-Demonstrates the XMPV and XIRR functions.

•  multiple irr.xlsx-Demonstrates the MIRR function.

•  net present value.xlsx-Demonstrates the NPV function.

CHAPTER 13

•  amortization.xlsx-Contains a simple loan amortization schedule.

•  financial statements.xlsx-Contains several types of financial statements.

•  indices.xlsx-Demonstrates how to create indices.

•  loan data tables.xlsx-Demonstrates a one-way and a two-way data table.

CHAPTER 15

•  array formula calendar.xlsx-Demonstrates how to display a calendar using a single multi-cell array formula.

•  logical functions.xlsx-Demonstrates some inconsistencies with logical functions.

•  multi-cell array formulas.xlsx-Demonstrates various multi-cell array formulas.

•  single-cell array formulas.xlsx-Demonstrates various single-cell array formulas.

CHAPTER 16

•  iterative chart animation.xlsx-Contains an example of an animated chart based on an intentional circular reference.

•  net profit (circular).xlsm-Demonstrates an intentional circular reference.

•  net profit (not circular).xlsx-Demonstrates an alternative to using a curricular reference formula.

•  recursive equations.xlsx-Demonstrates how to solve recursive equations by using an intentional circular reference.

•  simultaneous equations.xlsx-Demonstrates how to solve simultaneous equations by using an intentional circular reference.

•  unique random integers.xlsx-Demonstrates how to generate a list of unique random integers by using an intentional circular reference.

CHAPTER 17

•  box plot.xlsx-Demonstrates how to create a box plot.

•  chart from combo box.xlsx-Demonstrates how to display a chart series by selecting the data from a combo box.

•  clock chart.xlsm-Displays a fully functional analog clock, created with an XY chart.

•  comparative histogram.xlsx-Demonstrates how to create a comparative histogram.

•  conditional colors.xlsx-Demonstrates how to create a column chart with colors that depend on the value of each data point.

•  function plot 2D.xlsx-Plots functions with one variable.

•  function plot 3D.xlsm-Plots functions with two variables.

•  gantt chart.xlsx-Demonstrates how to create a Gantt chart.

•  gauge chart.xlsx-Demonstrates how to create a gauge chart.

•  hypocycloid - animated.xlsm-Plots an animated hypocycloid curve.

•  hypocycloid chart.xlsx-Plots a hypocycloid curve.

•  linear trendline.xlsx-Demonstrates linear trendlines.

•  nonlinear trendlines.xlsx-Demonstrates nonlinear trendlines.

•  plot circles.xlsx-Demonstrates how to plot a circle using an XY chart.

•  plot every nth data point.xlsx-Demonstrates how to plot every nth value in a chart.

•  plot last n data points.xlsx-Demonstrates how to plot the most recent n values in a chart.

•  thermometer chart.xlsx-Demonstrates how to create a thermometer chart.

CHAPTER 18

•  bank accounts.xlsx-The bank account pivot table examples.

•  calculated fields and items.xlsx-The pivot table calculated fields and items example.

•  county data.xlsx-A pivot table example.

•  employee list.xlsx-The pivot table grouping example.

•  hourly readings.xlsx-The pivot table grouping by time example.

•  income and expense.xlsx-The pivot table referencing example.

•  music list.xlsx-The pivot table report example.

•  sales by date.xlsx-The pivot table grouping by date example.

•  test scores.xlsx-The pivot table frequency distribution example.

CHAPTER 19

•  conditional formatting examples.xlsx-Demonstrates formatting examples.

•  conditional formatting formulas.xlsx-Demonstrates conditional formatting formulas.

•  conditional formatting with VBA functions.xlsm-Demonstrates conditional formatting using VBA functions.

•  daily staffing level.xlsx-Demonstrates a conditional formatting color scale.

•  data bars examples.xlsx-Demonstrates conditional formatting data bars.

•  data validation examples.xlsx-Contains data validation examples.

•  extreme color scale.xlsx-Demonstrates a conditional formatting color scale.

•  icon set examples.xlsx-Demonstrates conditional formatting icon sets.

CHAPTER 20

•  credit card validation.xlsx-Contains a megaformula to determine if a numbers is a valid credit card number.

•  name generator.xlsx-Contains a megaformula to generate random names.

•  no middle name.xlsx-Contains a megaformula to remove middle names from full names.

•  position of last space.xlsx-Contains a megaformula to determine the position of the last space character in a text string.

•  time test intermediate.xlsx-Uses intermediate formulas to remove middle names from full names.

•  time test megaformula.xlsx-Uses megaformulas to remove middle names from full names.

•  total interest.xlsx-Demonstrates a simple megaformula.

CHAPTER 24

•  function examples.xlsm-Contains VBA function examples.

CHAPTER 25

•  celltype function.xlsm-Contains a VBA function that describes the contents of a cell.

•  commission function.xlsm-Contains a VBA function to calculate sales commission.

•  counting functions.xlsm-Contains VBA functions that perform counting.

•  date functions.xlsm-Contains VBA functions that work with dates.

• extended data functions help.docx-A Word document that describes the functions in  extended date functions.xlsm.

•  extended date functions.xlsm-Contains a VBA functions that work with pre- 1900 dates.

•  last nonempty cell.xlsm-Contains VBA functions that return the last non- empty cell in a row or column.

•  monthnames.xlsm-Contains a VBA function that returns an array.

•  multisheet functions.xlsm-Contains a VBA function designed to work across multiple worksheets.

•  mysum function.xlsm-Contains a VBA function that emulates Excel's SUM function.

•  random integers function.xlsm-Contains a VBA function that returns an array of nonduplicated random integers.

•  range randomize function.xlsm-Contains a VBA function that returns an array randomized cells.

•  simple functions.xlsm-Contains simple VBA function examples.

•  spelldollars function.xlsm-Contains a VBA function that spells out a numerical value.

•  statfunction function.xlsm-Contains a VBA function that returns a variety of statistical calculations.

•  text manipulation functions.xlsm-Contains VBA functions that manipulate text.

eBook version of Excel 2007 Formulas

The complete text of the book you hold in your hands is provided on the CD in Adobe's Portable Document Format (PDF). You can read and quickly search the content of this PDF file by using Adobe's Acrobat Reader, also included on the CD.