Excel calculations- 3-D

Excel calculations- 3-D

YOUR OBJECTIVE

Your Excel workbook comprises several worksheets that contain first-quarter sales for ice cream sold in several of your company’s business locations. You recorded the figures for each month on a separate worksheet. That is, there is a worksheet for January, another for February, and a third for March. The position and layout of the tables on all three worksheets are identical.

Now, on a fourth worksheet, you want to create a table that will summarize the sales figures for all three months of the first quarter.

Because the data you want to summarize are located in exactly the same position on each of the the three worksheets, you can use excel 3-D calculations to add them together.

In this learning module, you will be shown how to use excel 3-D calculations to summarize data on multiple worksheets.

 

EXCEL CALCULATIONS: HOW DOES IT WORK?

The AutoSum function enables you to add together data from multiple worksheets, provided the cells to be added are in identical positions on each of the worksheets.

To calculate a sum over several worksheets, first open the worksheet that is to contain the summary table . In our example, you want to summarize data from the January, February, and March worksheets on the Quarter 1 worksheet. You will begin by summarizing the Chocolate flavor sales in Detroit. In the summary table, click cell B5. Then, on the Home tab, in the Editing group, click the AutoSum button.

The addition function =SUM() will be inserted into the cell.

Now you have to enter the 3-D cell references. A 3-D cell reference simultaneously links to the same cell address on multiple worksheets. To do this, just click the January worksheet’s tab, and then, while holding the [Shift] key depressed, click the the March worksheet’s tab. If you release the [Shift] key now, the January worksheet will display again. In the formula bar toward the top of the Excel window, you can see the partially constructed 3-D cell reference: =SUM(January:March!). The part of the formula “January:March!” expresses the range of worksheets from January to March. To include the address of the cells that are to be added together, click cell B4 in the January worksheet. Conclude the formula by clicking [Enter]. The summarized value for chocolate ice cream sales in Detroit during the first three months of the year now appears in cell B5 of the Quarter 1 worksheet.

 

Excel calculations: HINT

Instead of continuing to manually enter the SUM function with its cell references for the other ice cream flavors, you can use the AutoFill function instead. To do this, position the mouse pointer on the bottom-right corner of cell B5. It will change to a black cross. Now, while holding the mouse button depressed, drag the cross down until cell B8 is covered. When you release the mouse button, the SUM function will appear with appropriately adjusted cell references in all the cells you covered. As a result, the sales numbers for the other ice cream flavors and the total are displayed. You can use AutoFill to fill in the data for the remaining columns, as well. Just select the entire Detroit data column (B5 to B8), and then use your mouse to drag it by the lower-richt corner until the London column is covered, too.

 

CAUTION

The AutoFill function can only be used if the worksheets in question are identically structured.

 

CAUTION

You may notice that when you use the AutoFill function, you will overwrite any existing cell formatting. To retain the original formatting of the destination cells, move your mouse pointer just below and to the right of the range of cells you just filled in with AutoFill. Then click the SmartTag displayed below and to the right of the range you just filled. In the selection list that appears, activate the  option.

Because you used excel 3-D calculations  cell references to display the summary data in your new table, it will always contain the current consolidated sales figures for the first quarter, even if corresponding data on any of the other three tables changes.

Excel calculate: Calculating with dates

Excel calculate: Calculating with dates

YOUR OBJECTIVE

Given the date on which an order was received and the number of days required to fulfill the order, you want to use Excel to calculate a delivery date. In this learning object, you will find out about how dates are managed in Excel and how to make calculations with them.

HOW TO USE EXCEL TO CALCULATE ?

Values that represent dates are in reality stored as integer numbers. The earlierst date that Excel can process is January 1, 1900, which is represented internally by the number 1. All subsequent dates are represented by numbers that express the number of days that have elapsed since that initial day.

HINT

You can test this by entering the number 1 into a cell and then formating it as a date. You can also try this the other way around: enter today’s date, and then format it as a number.

Because dates are internally treated as numbers, you can easily make calculations with them. For example, you can use excel to calculate the difference between two dates, or add a certain number of days to any given date. In the following example, in cell B6, the date in cell B3 was added to the number in cell B4:

You can also subtract one date from another in order to determine how many days lie between them:

 

Excel average: The MAX, MIN and AVERAGE functions

average_1

YOUR OBJECTIVE

To analyze the performance of several of your company’s customers, you decided it would be useful to calculate the maximum, minimum and average revenue they provided in several categories. Excel average  function can do this quickly and easily using the MAX, MIN and AVERAGE functions. [Read more...]