Excel 4 Ur Business

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?

(more…)

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?

(more…)

• 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)

(more…)

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

(more…)

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?

(more…)

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.

(more…)