Excel 4 Ur Business

Overview

• What is net present value (NPV)?
• How do I use NPV to compare the merits of investments for which cash flows are received at several points in time?
• How do I use the Excel NPV function?
• How can I compute NPV when cash flows are received at the beginning of a year or in the middle of the year?
• How can I compute NPV when cash flows are received at irregular intervals?

Consider the following two investments, whose cash flows are listed in the file NPVExample.xls and shown in the following figure.

Read the rest of this post »

Overview

• When I enter dates into Excel, I often see a number such as 37625 rather than a date such as 1/4/2003. What does this number mean, and how do I change 37625 to a normal date?
• Can I enter a formula that automatically displays today’s date?
• How do I determine a date that is 50 workdays after another date? What if I want to exclude holidays?
• How do I determine the number of workdays between two dates?
• I have 500 different dates entered in Excel. What formulas can I use to extract the month, year, day of the month, and day of the week from each date?

Read the rest of this post »

Overview

• I have a spreadsheet in which each cell contains a product description, a product ID, and a product price. How can I put all the product descriptions in column A, all the product IDs in column B, and all the prices in column C?
• Every day I receive data about total U.S. sales, which is computed in a cell as the sum of East, North, and South region sales. How can I extract East, North, and South sales to separate cells?
• I download quarterly gross national product (GNP) data from the Web. The cell containing first quarter data for 1980 contains the entry 1980.1 5028.8. How can I place the date and GNP value in different cells?

• In the spreadsheet I use for a mailing list, column A contains people’s names, column B contains their street address, and column C contains their city and zip code. How can I create each person’s full address in column E?

Read the rest of this post »

Overview

• Given monthly sales for several products, how do I write a formula that will calculate the sales of a product during a given month? For example, how much of product 2 did I sell during June?
• Given a list of baseball player salaries, how do I write a formula that yields the player with the highest salary? How about the player with the fifth highest salary?
• Given the annual cash flows from an investment project, how do I write a formula that returns the number of years required to pay back the project’s initial investment cost?

Read the rest of this post »

• I have a list of distances between U.S. cities. How do I write a function that returns the distance between, say, Seattle and Miami?
• Is there any way I can write a formula that references the entire column containing the distances of each city to Seattle?

Syntax of the INDEX Function

The INDEX function allows you to return the entry in any row and column within a rectangular array of numbers. The most commonly used syntax for the INDEX function is:

INDEX(Array, Row Number, Column Number)

Read the rest of this post »

Overview

• How do I write a formula to compute tax rates based on income?
• Given a product ID, how can I look up the product’s price?
• Suppose that a product’s price changes over time. I know the date the product was sold. How can I write a formula to compute the product’s price?

Syntax of the LOOKUP Functions

Read the rest of this post »

Overview

• How do I create a name for a range of cells based on a spreadsheet label and use that name in formulas?

• How do I use natural language range names to incorporate a relationship such as Month(t) Ending Inventory = Month(t) Beginning Inventory + Month(t) Production -Month(t) Demand in a spreadsheet formula?

Microsoft Excel 2002 and Microsoft Office Excel 2003 give you the ability to create ‘natural language’ range names based on labels already present in your spreadsheet. I’ll illustrate the use of natural language range names with two examples.

How do I create a name for a range of cells based on a spreadsheet label and use that name in formulas?

Read the rest of this post »

There are three ways to create range names:

• Entering a range name in the Name box
• Choosing the Name, Create command from the Insert menu
• Choosing the Name, Define command from the Insert menu

Using the Name Box to Create a Range Name

The Name box is located directly above the label for column A, as you can see in the followning figure. (To see the Name box, you need to display the Formula bar.) To create a range name using the Name box, simply select with the mouse the cell or range of cells that you want to name, click in the Name box, and then type the range name you want to use. Press Enter, and you’ve created the range name. Clicking on the drop-down arrow for the Name box displays the range names defined in the current workbook. You can also display all the range names in a workbook by pressing the F3 button, which displays the Paste Name dialog box. When you select a range name from the Name box, Excel selects the cells corresponding to that range name. This enables you to check that you’ve chosen the cell or range that you intended to.

Read the rest of this post »