Free Essay

Prueba 1 Excel

In: Business and Management

Submitted By marcodelgado
Words 6379
Pages 26
Excel Tutorial to Improve Your Efficiency (2007 Version)
My purpose with this Excel tutorial is to illustrate some Excel tips that will dramatically improve your efficiency. I make no attempt to be as encyclopedic as some of the 800-page Excel manuals available. I concentrate on common tasks, not every last thing that can be done in Excel. Also, I presume that you have some Excel knowledge. For example, I assume you know about rows and columns, values, labels, and formulas, relative and absolute addresses, and other basic Excel elements. If you know virtually nothing about Excel, you probably ought to work through an “Excel for Dummies” book and then work through this tutorial.
The style of this tutorial should be easy to follow. Main topics appear in bold black type. Specific direction headings are in yellow, and these are followed by detailed directions in red. Additional comments about the directions appear in blue. Then there are “Try it!” exercises in green. These “Try it!” exercises are a key feature of this tutorial. I have embedded numerous sample Excel spreadsheets so that you can try out the directions right away—without switching into Excel. When you double-click on one of these spreadsheets, you launch Excel, and the spreadsheet “comes alive.” The menus and toolbars even change to those for Excel. By clicking outside one of these spreadsheets, you’re back in Word.
The easiest way to maneuver around this tutorial is to switch to outline view. To do so, select Outline from Word’s View ribbon, which gives you an extra Outlining ribbon. From this ribbon, click on the Show Level dropdown. This lets you choose the level of the outline. You’ll get good results by choosing Level 2. This lets you see all first-level and second-level headings. Put your cursor on a heading of interest and then switch back to normal view by selecting Print Layout, again from the View ribbon. (Try it right now. It’s easy!)
Finally, I suggest that you save this file–RIGHT NOW–as My Excel Tutorial.docx (or some such name) and work with the copy. That way, if you mess anything up as you try the exercises, you can always go back and retrieve the original file (Excel Tutorial.docx).
Have fun!
Changes in Excel 2007
If Excel 2007 is your first exposure to Excel, you’ll love it. If you’re used to Excel 2003 or an earlier version, however, you’ll have some unlearning to do. There are two big changes in Excel 2007 and many smaller changes. The first big change is that worksheets are much bigger. You used to have about 65,000 rows and 256 columns. Now you have over a million rows and over 16,000 columns. You’ll hardly ever use this much space, but it is available.
The second big change is the one you will notice right away: You no longer have the usual menus and toolbars. Microsoft has completely reorganized the user interface to provide tabs and ribbons. Each tab (Home, Insert, Page Layout, etc.) has an associated ribbon that is similar to the old toolbars. For example, if you click on the Formula tab, you get a ribbon with buttons that are useful for working with formulas. Each ribbon has several groups of buttons. For example, the Formulas ribbon has one group called Defined Names with buttons for manipulating range names. There is only one way to learn these ribbons: practice and experiment. If you are used to the old Excel, you will undoubtedly curse at the new ones a few times when you can’t find something, but you can be assured that they are more logically organized than the old versions.
One particular menu item you will miss is ToolsOptions, for changing various options in Excel. It’s not gone; it’s just in a different place. To get to it, click on the new Office button (in the upper left corner of the screen) and then on Excel Options. From there, you can experiment with the various options. While you’re at it, note the options along the left when you click on the Office button. For example, this is where you find Save As and Print, two obvious favorites.
You can’t change the new ribbons; they are built in and fixed. However, you can customize the user interface to some extent. Excel gives you a quick access toolbar (usually referred to as QAT) at the top left of the screen. You can put your favorite buttons on this toolbar so that they are always visible and available. The QAT comes with a few favorite buttons, but you can add more. Just click on the dropdown arrow to the right of the QAT and check any of your favorites. (You can also click on More Commands to get many more buttons.)
With the old menus gone, what about the keyboard shortcuts many of you depend on? As far as I can tell, they still work, or at least most of them still work. For example, you can still press Ctrl-s to save a file or Ctrl-p to print a file. However, in this document, where I’ve embedded spreadsheets, the keyboard shortcuts for Excel won’t work. This is evidently because the spreadsheets are embedded in a Word document.
One other important change is file extensions. Unless you wrote your own add-ins in earlier versions of Excel, the only file extension you probably ever saw was .xls. You can still save files with this extension, but they will lose any features new to Excel 2007. To save them and keep new features, you should save them as .xlsx files. Note that if you do so, people with Excel 2003 will not be able to open them unless they download and install a free file format converter from Microsoft. (Search the Web for Microsoft Office Compatibility Pack to find this free download.) One other new file extension is .xlsm. If your Excel file has associated VBA macros, then you have to save it as an .xlsm file.
Basic Spreadsheet Tasks
This section illustrates a number of ways to make you more efficient: how to select ranges, how to copy and paste, how to deal with absolute and relative addresses, how to insert and delete rows or columns, and a few others. Most Excel users know how to do these things, but they often do them inefficiently. So even though this material might seem elementary, read on—you might just pick up a few tricks you weren’t aware of.
Moving to the Top of the Sheet
Often you want to reorient yourself by going back to the “home” position on the worksheet.
To go to the top left of the sheet (cell A1):
Press Ctrl-Home (both keys at once).
Try it! Starting in the middle of the spreadsheet, go to the home cell, A1.

Using End-Arrow Key Combinations
To go to the end of a range (top, bottom, left, or right):
Press the End key, then the appropriate arrow key. For example, press End and then right arrow to go to the right edge of a range.
Try it! Starting at a corner (a bordered cell), move around to the other corners.

The action of an End-arrow combination depends on where you start. It takes you to the last nonblank cell if you start in a nonblank cell. (If there aren’t any nonblank cells in that direction, it takes you to the far edge of the sheet.) If you start in a blank cell, it takes you to the first nonblank cell.
Splitting the Screen
It is often useful to split the screen so that you can see more information.
To split the screen vertically, horizontally, or both:
Click on the narrow “screen splitter” bar just to the right of the bottom scroll bar (for vertical splitting) or just above the right-hand scroll bar (for horizontal splitting) and drag this to the left or down. (This is a bit tricky. You will know your cursor is in the right position when it changes to a double-arrow cursor.)
Splitting gives you two “panes” (or four if you split in both directions). Once you have these panes, practice scrolling around in any of them, and see how the others react.
Try it! Split the screen either way and then remove the split.

Selecting a Range
Usually in Excel, you select a range and then do something to it (such as enter a formula in it, format it, delete its contents, and so on). Therefore, it is extremely important to be able to select a range efficiently. This is easy if the whole range appears on the screen, but it is trickier if you can’t see the whole range. In the latter case the effect of dragging (the method most users try) can be frustrating–things scroll by too quickly. Try one of the methods below instead.
To select a range that fits on a screen:
Click on one corner of the range and drag to the opposite corner.
Click on one corner, hold down the Shift key, and click on the opposite corner.
Try it! Select the range B2:D7.

To select a range doesn’t fit on a screen:
Click on the upper left corner of the range. Then, holding the Shift key down, use the End-arrow combinations (End and right arrow, then, if necessary, End and down arrow) to get to the opposite corner.
Split the screen so that the upper left corner shows in one pane and the bottom right corner shows in another pane. Click on the upper left corner, hold the Shift key down, and click on the bottom right corner.
Try it! Select the range B2:C100 or the range E2:N5. Try both of the methods suggested above.

Selecting Multiple Ranges
Suppose you want to format more than one range in a certain way (as currency, for example). The quickest way is to select all ranges at once and then format them all at once.
To select more than one range:
Select the first range, press the Ctrl key, select the second range, press the Ctrl key, select the third range, and so on.
For example, to select the ranges B2:D5 and F2:H5, click on B2, hold down the Shift key and click on D5 (so now the first range is selected), hold down the Ctrl key and click on F2, and finally hold down the Shift key and click on H5.
Try it! Select all three numerical ranges shown.

Note that in Excel 2007, the highlighting is sometimes hard to see. The shading isn’t as dark as in previous versions of Excel, so you might be fooled into thinking you haven’t really highlighted multiple ranges. But the above method definitely works, exactly as it always has.
Copying and Pasting
Copying and pasting (often formulas) is one of the most frequently performed tasks in Excel, and it can be a real time-waster if done inefficiently. The following two methods illustrate a keyboard shortcut method and a button method. We certainly prefer the keyboard shortcut, but you can use either.
To copy and paste using keyboard shortcuts:
Select the copy range (using one of the efficient selection methods described above), press Ctrl-c (for copy), select the paste range (again, efficiently), and press Ctrl-v (for paste). (If you practice pressing Ctrl-c or Ctrl-v with the little finger and index finger of your left hand, it will become automatic in no time.)
Note that the copy range will still have a dotted line around it, meaning that it is still on the clipboard and is available to be pasted again. Press the Esc key to get rid of it.
Try it! Copy the formula in cell C2 down through cell C8 using Ctrl-c and Ctrl-v.

To copy and paste using buttons:
Proceed as above, but use the copy and paste buttons (in the left section of the Home ribbon) instead of the Ctrl-c and Ctrl-v key combinations. (They are in the Clipboard group.)
Try it! Copy the formula in C2 down through cell C8 using the Copy and Paste buttons.

A frequent task is to enter a formula in one cell and copy it down a row or across a column. There are several very efficient ways to do this. The first of these is my personal favorite. I’m always surprised how few people know about it.
To avoid copying and pasting altogether, use Ctrl-Enter:
Starting with the top or left-hand cell, select the range where the results will go. (Use the selection methods described earlier, especially if this range is a large one.) Type the formula and then press Ctrl-Enter instead of Enter.
Try it! Fill up the range C2:C8 with Ctrl-Enter.

Pressing Ctrl-Enter enters what you typed in all of the selected cells (adjusted for relative addresses), so in general, it can be a real time saver. It can be used to enter a formula, as above, and it can also be used to enter a fixed number such as 10 in a whole range of cells. Just select the range, type 10, and press Ctrl-Enter.
Try it! Fill up the range B2:D8 with the value 10 by using Ctrl-Enter.

When you select a cell and place the cursor on the bottom right of the cell, the cursor changes to a plus sign. This is called a drag handle. It can be used in a couple ways to copy.
To copy with the drag handle:
Enter the formula in the top or left-hand cell of the intended range. Place the cursor on the drag handle at the lower right of this cell, and drag this handle down or across to copy.
Try it! Copy the formula in C2 down through C8 with the “drag handle”.

To copy by double-clicking on the drag handle:
Enter the formula in the top or left-hand cell of the intended range. Double-click on the drag handle.
This method uses Excel’s built-in intelligence, but it works only in certain situations. Let’s say you have numbers in the range A3:B100. You want to enter a formula in cell C3 and copy it down to cell C100. Because this is a common thing to do, Excel does it for you if you double-click on the drag handle. It senses the “filled-up” range in column B and guesses that you want another filled-up range right next to it in column C. If there were no adjacent filled-up range, double-clicking on the drag handle wouldn’t work.
Try it! Copy the formula in C2 down through C8 by double-clicking the “drag handle.”

Copying and Pasting with the Special/Values Option
Often you have a range of cells that contains formulas, and you would like to replace the formulas with the values they produce. Usually, you paste these values onto the copy range, that is, you overwrite the formulas with values. However, you could also select another range for the paste range.
To copy formulas and paste values:
Select the range with formulas, press Ctrl-c to copy, and select the range where you want to paste the values (which could be the same as the copy range). Then (because there is no keyboard equivalent) select the Paste dropdown on the Home ribbon, and select the Paste Values option.
Try it! Copy the range D2:D8 to itself, but paste values.

You might want to experiment with the other options in the Paste dropdown. For example, if you have a set of labels entered as a row and you want this same set of labels entered somewhere else as a column, try copying and pasting with the Transpose option. Or if you want to paste only the formats from some other range, this is also possible. The various options on the Paste dropdown are well worth learning.
Moving (Cutting and Pasting)
Often you would like to move information from one place in the sheet to another.
To move (cut and paste):
Select the range to be cut, press Ctrl-x (for cutting), select the upper left corner of the paste range, and press Ctrl-v. (The little finger-index finger combination in your left hand is also good for pressing Ctrl-x.)
As with copying and pasting, ribbon buttons can be used instead of key combinations. Also, note that you need only select the upper left cell of the paste range. Excel knows that the shape of the paste range must be the same as the shape of the cut range.
Try it! Move the range A2:C8 to the range D2:F8. (Watch how relative addresses affect the eventual formulas in column F.)

Absolute/Relative References
Absolute and references are indicated in formulas by dollar signs or the lack of them, and they indicate what happens when you copy or move a formula to a range. You often want some parts of the formula to stay fixed (absolute) and others to change relative to the cell position. This is a crucial concept for efficiency in spreadsheet operations, so you should take some time to understand it thoroughly. Here are two important things to remember: (1) The dollar signs are relevant only for the purpose of copying or moving; they have no inherent effect on the formula. For example, the formulas =5*B3 and =5*$B$3 produce exactly the same result. Their difference is relevant only if you want to copy the result to some range. (2) There is never any need to type the dollar signs. This can be done with the F4 key.
To make a cell reference absolute or mixed absolute/relative using the F4 key:
Enter a cell reference such as B3 in a formula. Then press the F4 key.
In fact, pressing the F4 key repeatedly cycles through the possibilities: B3 (neither row nor column fixed), then $B$3 (both column B and row 3 fixed), then B$3 (only row 3 fixed), then $B3 (only column B fixed), and back again to B3.
Try it! Enter the appropriate formula in cell B7 and copy across to E7. (Scroll to the right to see the correct answer.)

Try it again! Enter one formula with appropriate absolute/relative addressing in cell C5 that can be copied to C5:F9. (Scroll to the right to see the correct answer.)

Showing Formulas in R1C1 Notation
Speaking of relative and absolute addresses in formulas, it is pretty amazing how intelligent spreadsheets are. When you copy a formula in cell C1 such as =A1+B1 down, it automatically changes appropriately: =A2+B2, then =A3+B3, and so on. In a sense, these are all the same formula. Each says to add the two values to the left of the current cell. Excel allows you to see this equivalence even more clearly by viewing the formulas in a different format, called R1C1 notation. In this format, each of the formulas in column C is becomes =RC[-2]+RC[-1]. R stands for row, and C stands for column. The fact that there is nothing next to R means we stay in the same row. The numbers in brackets next to C mean to go 2 columns to the left and 1 column to the left. (For columns, negative numbers mean to go to the left, positive to the right. For rows, negative numbers mean to go up, positive down.)
If there is a number next to R or C that is not in brackets, it indicates an absolute reference. For example, =R2C[2] placed in cell D5 is equivalent to =F$2 because the row reference is absolute and the column reference is relative to column D.
The usual way of expressing formulas, such as =C5+D5, is called A1 format. The new way discussed here is called R1C1 format. You can easily toggle between them.
To toggle between A1 and R1C1 formats:
Select the Office button, select Excel options, select Formulas, and check or uncheck the R1C1 reference style option.
Try it! Toggle between A1 and R1C1 reference style in the following spreadsheet, and for either, examine the formulas inside the border and in the Total row. (Note: As far as I can tell, you’ll have to actually open Excel to try this one. When you double-click on the spreadsheet below, the Excel menu appears, but not the Office button. So you evidently can’t change the Excel options from within Word. I suppose that makes sense.)

I have read one Excel book that advocates the use of R1C1 notation everywhere, reasoning that this notation makes more logical sense. Maybe the author has a point, but the A1 notation is so ingrained in most of us that the transition to R1C1 notation would probably start a revolt among millions of Excel users. It isn’t going to happen!
Inserting and Deleting Rows or Columns
Often you want to insert or delete rows or columns. Note that deleting a row or column is not the same as clearing the contents of a row or column—making all of its cells blank. Deleting a row or column means wiping it out completely.
To insert one or more blank rows:
Click on a row number and drag down as many rows as you want to insert. Then click on the Insert dropdown and select Insert Sheet Rows. (Interestingly, the Insert dropdown is in the Cells group of the Home ribbon, not on the Insert ribbon.)
Note that the rows you insert are inserted above the first row you selected. For example, if you select rows 8 through 11 and then insert, four blank rows will be inserted between the old rows 7 and 8.
Try it! Insert blank rows for the data for Feb, Apr, and May.

Columns are inserted in the same way.
Try it! Insert blank columns for sales reps Baker, Miller, and Smith (so that the sales reps are in alphabetical order from left to right).

To delete one or more rows:
Click on a row number and drag down as many rows as you want to delete. Then select the Delete dropdown (again from the Home ribbon) and select Delete Sheet Rows. Columns are deleted in exactly the same way.
Try it! The company no longer carries products K322 and R543, so get rid of their rows.

Because I insert and delete rows and columns so often, I always use keyboard shortcuts left over from previous versions of Excel—that still work.
To insert one or more rows or columns:
Highlight the rows and press Ctrl-i and then r. For columns, the shortcut is Ctrl-i and then c. (These are easy to remember: i for insert, r for row, c for column.)
To delete one or more rows or columns:
Highlight the rows or columns you want to delete and press Ctrl-e and then d. (This is also pretty easy to remember: e for edit, d for delete.)
Try it! Repeat the above inserting and deleting exercises with the keyboard shortcuts.
Filling a Series
Say you want to fill column A, starting in cell A2, with the values 1, 2, and so on up to 1000. There is an easy way.
To fill a column range with a series:
Enter the first value in the first cell (1 in cell A2). With the cursor in the starting cell (A2), select the Fill dropdown and then select the Series option to obtain a dialog box. (The Fill dropdown is in the Editing group of the Home ribbon.) Change the Rows setting to Columns, make sure the Type setting is Linear, make sure 1 is in the Step Value box, enter the final value (1000) in the Stop Value box, and click on OK.
As you can guess from this dialog box, many other options are possible. Don’t be afraid to experiment with them.
Try it! The series of days in column A should go from 1 to 25, in column D it should go from 26 to 50.

Summation Button
The SUM function is used so often to sum across rows or columns that a button (the button) is available to automate the procedure. To illustrate its use, suppose you have a table of numbers in the range B3:E7. You want the row sums to appear in the range F3:F7, and you want the column sums to appear in the range B8:E8. This is easy.
To produce row and column sums with the summation button:
Select the range(s) where you want the sums (F3:F7 and B8:E8–remember how to select multiple ranges?), and click on the summation button.
Note that if you select multiple cells, you get the sums automatically. If you select a single cell (such as when you have a single column of numbers to sum), you are shown the sum formula “for your approval” and you have to press Enter to actually enter it. Why does Excel do it this way–your guess is as good as mine!
Try it! Use the summation button to fill in the row and column sums.

The summation button is in the Editing group of the Home ribbon. If you want a sum, click directly on the button. Alternately, you can click on the AutoSum dropdown for other options, including Average, Max, Min, and others.
Transposing a Range
Often you set up a spreadsheet and then decide that you would rather have a portion of it transposed. That is, you would like to “turn it on its side,” so that rows become columns and vice versa. This is simple with one of Excel’s Paste options.
To transpose a range:
Select a range that you want to transpose and press Ctrl-c to copy it. Then select the upper left cell of the range where you want the transposed version to go, select the Paste dropdown, and select the Transpose option.
Make sure there is enough room for the transposed version. For example, if the original range has 3 rows and 5 columns, the transposed version will have 5 rows and 3 columns. If you select cell D5, say, as the upper left cell for the transposed version, everything in the range D5:F9 will be overwritten by the transposed version.
Try it! Transpose the range A3:D6 to a range with upper left cell F3.

Range Names
Range names are extremely useful for making your formulas more understandable. After all, which formula makes more sense: =B20-B21 or =Revenue-Cost? Efficient use of range names takes some experience, but here are a few useful tips.
To create a range name:
Select a range that you want to name. Then type the desired range name in the upper left “name box” on the screen. (This box is just above the column A heading. It usually shows the cell address, such as E13, where the cursor is.)
Try it! Name the rectangular range containing the numbers Data.

When you type the range name in the name box, make sure you then press Enter to make the name “stick.” It’s easy to type the name and then click on some other cell without pressing Enter. (I have done it many times.) If you do so, you will find that the range name was not created.
You can also select the Formulas tab and use the Define Name option in the Defined Names group to name a range, but typing the range name in the name box is quicker and more intuitive. By the way, range names are not case sensitive, so that Revenue, revenue, and REVENUE can be used interchangeably.
To delete a range name:
Select the Name Manager in the Defined Names group of the Formulas ribbon. This shows a list of all range names in your workbook. Click on the one you want to delete, and then click on the Delete button.
Suppose a range has name Costs and you want to rename it UnitCosts. If you highlight the range, the name box will show Costs. If you then overwrite this with UnitCosts in the name box, the range will have two names, Costs and UnitCosts. There is nothing inherently wrong with this, but if you want only a single name, UnitCosts, you will have to delete the Costs name, as described here.
Try it! The numerical range is currently named Data. Delete this range name and then rename the range Database.

Suppose you have the labels Revenue, Cost, and Profit in cells A20, A21, and A22, and you would like the cells B20, B21, and B22 (which will contain the values of revenue, cost, and profit) to have these range names. Here’s how to do it quickly.
To create range names from adjacent labels:
Select the range consisting of the labels and the cells to be named, in this case A20:B22. Then click on the Create from Selection button in the Defined Names group of the Formulas ribbon. In the resulting dialog box, make sure the appropriate option (in this case, Left Column) is checked, and click on OK.
Excel tries (usually successfully) to guess where the labels are that you want to use as range names. If it guesses incorrectly, you can always override its guess.
Try it! Name the ranges A3:A8, B3:B8, and so on according to the labels in row 2.

Sometimes you enter a formula using cell addresses, such as =B20-B21. Later, you name B20 as Revenue and B21 as Cost. The formula does not change to =Revenue-Cost automatically. However, it would be nice to make it change (and hence become more readable). Excel calls this “applying” the names, and it is easy to do.
To apply range names to a formula:
Select the cells that contain the formulas. Then select the Apply Names item from the Define Name dropdown on the Formula ribbon. This gives you a list of all range names, and you can highlight the names you want to apply.
Try it! Apply the Revenue and Cost range names (that already exist) to the formula for profit.

It is often useful to go to a particular named range. You can do this easily from the name box.
To go to a particular named range:
Click on the down arrow at the right of the name box, and select any of the range names you see. That range will then be selected automatically.
Try it! There are five named ranges below. Locate them.

Sometimes it is straightforward to use range names in formulas. For example, if B20 is named Revenue and B21 is named Cost, then entering the formula =Revenue-Cost in, say, cell B22 is a natural thing to do. But consider this situation. The range B3:B14 contains revenues for each of 12 months, and its range name is Revenue. Similarly, C3:C14 contains costs, and its range name is Cost. For each month you want that month’s revenue minus cost in the appropriate cell in column D. You will get it correct if you select the range D3:D14, type the formula =Revenue-Cost, and press Ctrl-Enter. If you click on any cell in this range, you will see the formula =Revenue-Cost.
This is pretty amazing. How does Excel know that the formula in D3, for example, is really =B3-C3? Let’s just say that it’s smart enough to figure this out. If it confuses you, however, you can always enter =B3-C3 and copy it down. Then you are safe, but you have lost the advantage of range names!
Try it! Enter the formula for all of D3:D14 using range names. (If you like, calculate profits again in column E in the usual way, without range names.)

To document your spreadsheet, it is often useful to create a list of all of your range names and their corresponding cell addresses. This is easy with Paste Names option.
To paste a list of all range names on a sheet:
To paste a list of all range names and corresponding addresses, select a cell with plenty of blank space below it, select the Use in Formula dropdown in the Defined Names group, and click on the Paste Names option.
Try it! Paste a list of all range names, starting in cell D2.

Basic Excel Functions
There are many useful functions in Excel. You should become familiar with the ones most useful to you. For example, financial analysts should learn the financial functions. But there are a few that everyone should know. (By the way, I capitalize the names of these functions, just for emphasis. However, they are not case sensitive. You can enter SUM, Sum, or sum, for example, with exactly the same result.)
SUM Function
The SUM function is probably the most used Excel function of all. It sums all values in one or more ranges.
To use the SUM function:
Enter the formula =SUM(range), where range is any range. This sums the numerical values in the range.
Actually, it is possible to include more than one range in a SUM formula, as long as they are separated by commas. (This can also be done with the COUNT, COUNTA, AVERAGE, MAX, and MIN functions discussed below.) For example, =SUM(B5,C10:D12,Revenues) is allowable (where Revenues is the name for some range). The result is the sum of the numerical values in all of these ranges combined. Note that if any cell in any of these ranges contains a label rather than a number, it is ignored in the sum.
Try it! Use the SUM function in cell B10 to calculate the total of all costs.

The COUNT function counts all of the cells in a range with numeric values. The COUNTA functions counts all nonblank cells in a range. For example, if cells A1, A2, and A3 contain Month, 1, and 2, respectively, then =COUNT(A1:A3) returns 2, whereas =COUNTA(A1:A3) returns 3.
To use the COUNT function:
Enter the formula =COUNT(range), where range is any range. This returns the number of numeric values in the range.
To use the COUNTA function:
Enter the formula =COUNTA(range), where range is any range. This returns the number of nonblank cells in the range.
Try it! Use the COUNT and COUNTA functions to fill in cells E1 and E2. Note that there are students below the visible portion of the spreadsheet.

AVERAGE Function
The AVERAGE function averages all of the numeric cells in a range.
To use the AVERAGE function:
Enter the formula =AVERAGE(range) where range is any range. This produces the average of the numeric values in the range.
Try it! Use the AVERAGE function to calculate the averages in cells B1 and B2. (For B2, you’ll have to replicate the exam scores in column C and make some changes.)

Note that the AVERAGE function ignores labels and blank cells in the average. So, for example, if the range C3:C50 includes scores for students on a test, but cells C6 and C32 are blank because these students haven’t yet taken the test, then =AVERAGE(C3:C50) averages only the scores for the students who took the test. (It does not automatically average in zeros for the two who didn’t take the test.)
Also, you might remember from your statistics course that the average is often called the mean. There is no MEAN function in Excel, so if you want the mean, you should use the AVERAGE function.
MAX, MIN Functions
The MAX function returns the largest numeric value in a range. Similarly, the MIN function returns the smallest numeric value in a range.
To use MAX and MIN functions:
Enter the formula =MAX(range) or =MIN(range) where range is any range. These produce the obvious results: the maximum (or minimum) value in the range.
Try it! Use the MAX and MIN functions to fill in the range B8:C9. For example, you want the values $2300 and $3600 in cells B8 and C9.

There are many times when you need to sum products of values in two (or possibly more than two) same-sized ranges. Fortunately, there is an Excel function that sums products quickly.
To use the SUMPRODUCT function
Enter the formula =SUMPRODUCT(range1,range2), where range1 and range2 are exactly the same size. For example, they might be two column ranges with 10 cells each, or they might be two ranges with 4 rows and 10 columns each. The formula sums the products of the corresponding values from the two ranges.
There can actually be more than two ranges in the SUMPRODUCT formula, separated by commas, as long as all of them have exactly the same size. This is not as common as having only two ranges, but it is sometimes useful.
Try it! Sum the products of the two ranges in the following spreadsheet to find the total shipping cost. Enter the result in cell G1. (Scroll to the right for the answer.) By the way, if you are tempted to write the formula without the SUMPRODUCT function as the sum of 9 products, as some of my students continue to do, imagine how long your formula would be if there were 10 plants and 50 cities!

IF Function
Formulas involving the IF function are very useful, and they vary from simple to complex. I’ll provide a few examples.
To enter a basic IF function:
Enter the formula =IF(condition,expression1,expression2), where condition is any condition that is either true or false, expression1 is the value of the formula if the condition is true, and expression2 is the value of the formula if the condition is false.
A simple example is =IF(A1

Similar Documents

Free Essay

Prueba de Chi Cuadrado

...PRUEBAS NO PARAMÉTRICAS Distribución chi-cuadrada ( 2) Las aplicaciones más comunes de la distribución chi-cuadrada son (1) pruebas de bondad de ajuste y (2) pruebas de independencia. 1.- Pruebas de bondad de ajuste Medidas sobre qué tan cerca se ajustan los datos muestrales observados a una forma de distribución particular planteada como hipótesis. Si el ajuste es razonablemente cercano, puede concluirse que si existe la forma de distribución planteada como hipótesis. Prueba chi-cuadrada donde k: k = m-1: grados de libertad donde m es el número de parámetros o productos a estimar. EJEMPLO. Juan Pérez, director de Mercadeo de Pasta Dental, tiene la responsabilidad de controlar el nivel de existencias para cuatro tipos de Productos distribuidos por su fábrica. En el pasado, ha ordenado nuevos productos bajo la premisa de que los cuatro tipos son igualmente populares y la demanda de cada tipo es la misma. Sin embargo, recientemente las existencias se han vuelto más difíciles de controlar, y Juan considera que debería probar su hipótesis respecto a una demanda uniforme. Sus hipótesis son: H0: La demanda es uniforme para los cuatro tipos de productos. H1: La demanda no es uniforme para los cuatro tipos de productos. La Tabla 1.1 muestra la expectativa uniforme para una muestra de 48 pastas vendidas en los últimos seis meses todos los valores en miles...

Words: 606 - Pages: 3

Free Essay

Administrative Assistante/Human Resources II. Descripción Estudio de los métodos cuantitativos para la toma de decisiones, en particular la aplicación de modelos matemáticos y estadísticos en el análisis de problemas relacionados con las ciencias económicas y administrativas. Los temas principales incluyen: probabilidad y análisis para la toma de decisiones, teoría de juegos, análisis bajo condiciones de incertidumbre y análisis de redes. Se incluyen simulaciones. III. Objetivos Se espera que al finalizar el curso, el estudiante pueda: 1. Integrar las técnicas cuantitativas aprendidas para tomar decisiones dentro de la organización. 2. Explicar las decisiones basadas en elementos cuantitativos. IV. Contenido temático A. El acercamiento al análisis cuantitativo 1. Definición del problema 2. Desarrollo del modelo 3. Obtención de datos 4. Desarrollo de la solución 5. Prueba de la solución 6. Análisis de los resultados 7. Análisis de sensitividad 8. Implementación B. Desarrollo de un modelo de análisis cuantitativo 9. Pasos en el desarrollo de modelos cuantitativos 10. Ventajas y desventajas de los...

Words: 1009 - Pages: 5

Free Essay

This Is a Project

...1 Capítulo I 2 ¿De que se trata esto?  En pocas palabras, la econometría consiste en la aplicación estadística matemática a la información económica para dar soporte empírico a los modelos construidos por la economía y de ese modo obtener resultados significativos 3 ¿Cuál es su utilidad? 1. Nos permite probar teorías económicas, vale decir, se busca examinar las relaciones que existen entre distintas variables explicativas y variables explicadas Los econometristas al identificar relaciones entre variables en el tiempo, pueden predecir y/o pronosticar valores probables de estas, por ejemplo, el valor promedio de las ventas en años posteriores. 2. 3. Una vez formulados los modelos econométricos, es posible diseñar políticas 4 Aspectos importantes  La econometría y la economía no son unívocos, esto es, no se debe vincular la economía exclusivamente con la econometría en lo relativo a teorías económicas. Es importante desligarse del paradigma de la ocupación de la econometría.  Existe la economía del crimen, de la salud, del deporte, que llevan el nombre por la supuesta racionalidad de las personas y la disyuntiva costo-beneficio. 5  Es útil para formular políticas públicas y para evaluar su impacto en la ciudadanía, también proyecciones y estimaciones de ventas. para  Por consiguiente, podemos usar la econometría tanto en el sector público como en el sector privado ya que presenta un bagaje de alternativas para...

Words: 8701 - Pages: 35

Free Essay

Tags Honor Oficina Teléfono Oficina Correo Electrónico II. : : : : : : : : Finanza Gerencial FINA 2100 Tres (3 créditos) 2016-33 Antonio J. Fernós Sagebién por acuerdo previo (787) 250-1912 Ext. 2493 Descripción del Curso: Estudio de los principios básicos y contemporáneos de la administración financiera y su utilización en la toma de decisiones. Énfasis en el uso de los modelos matemáticos para determinar el valor presente y futuro de las inversiones. Utilización de técnicas para evaluar el financiamiento de los activos de la empresa, el riesgo y el rendimiento de proyectos. Análisis de la estructura y el costo de capital. Requisito: ACCT 1162. III. Objetivos del Curso: A. Terminales 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. Identificar los objetivos y funciones principales del campo de las finanzas. Reconocer la importancia del rol de la finanza gerencial en un sistema económico moderno de libre empresa. Aplicar los diferentes conceptos y modelos en la solución de problemas financieros. Aplicar las técnicas de evaluación y análisis para la toma de decisiones financieras y de inversión. Aplicar las técnicas de evaluación de riesgo y rendimiento de proyectos. Analizar la estructura de capital y su costo. Enumerar las funciones de un administrador financiero y los principios éticos aplicables. Explicar las ventajas y desventajas de organizaciones empresariales. Describir los componentes básicos...

Words: 3142 - Pages: 13

Free Essay


...PMI. FGPR020- Versión 3.0 CONTROL DE VERSIONES Versión Hecha por Revisada por Aprobada por Fecha Motivo 1.0 2.0 RL RL SP SP SP SP 08.05.09 Versión Original 17.05.09 Versión 2 SCOPE STATEMENT NOMBRE DEL PROYECTO SIGLAS DEL PROYECTO Desarrollo de Nuevo Producto DESCRIPCIÓN DEL ALCANCE DEL PRODUCTO REQUERIMIENTOS: CONDICIONES O CAPACIDADES QUE DENUPRO CARACTERÍSTICAS: PROPIEDADES FÍSICAS, QUÍMICAS, DEBE POSEER O SATISFACER EL PRODUCTO PARA CUMPLIR CON CONTRATOS, NORMAS, ESPECIFICACIONES, U OTROS DOCUMENTOS FORMALMENTE IMPUESTOS. ENERGÉTICAS, O SICOLÓGICAS, QUE SON DISTINTIVAS DEL PRODUCTO, Y/O QUE DESCRIBEN SU SINGULARIDAD. 1. Lograr que los componentes del producto cumplan con las Especificaciones técnicas requeridas para el oportuno proceso de producción. 1. Frasco Flint cristalino (Fire polish, es un proceso de pulir el frasco para retirar todas las imperfecciones las marcas de partición propias del molde) e impresión de serigrafía. 2. Tapa y collar de material Surlyn de color negro. 3. Esta presentación tiene que tener un anillo dorado entre el collar y la tapa para...

Words: 29432 - Pages: 118

Free Essay


...reflejando correctamente la imagen fiel del patrimonio a sus accionistas. Por este motivo, el fin principal de la auditoría es encontrar que la empresa no tenga mayor gasto/pasivo o menor ingreso/activo o que de alguna manera incumpla los principios contables generalmente aceptados. Debido a esto, el mayor riesgo de la auditoría es que la empresa esté declarando un menor gasto/pasivo o un mayor ingreso/activo. PRUEBAS DE AUDITORÍA 900 PLANIFICACIÓN ( ) Cálculo de la materialidad: Ver BOICAC nº 38. El resumen es el siguiente. (*) Existen dos variaciones: Empresas con pérdidas o en punto muerto o con bajo nivel de rentabilidad: entre el 0,5% y el 1% del más representativo entre la cifra de negocios total, los fondos propios o el volumen de activos. Empresas en desarrollo (sin haber alcanzado el volumen normal de operaciones): entre el 3% y el 5% de los fondos propios. Materialidad: Límite a partir del cual se califica la opinión. Si B: 100 y 7.5%, 75000 3000 AREA DE TESORERÍA ( ) * Arqueo de Caja: El arqueo de caja es una prueba teórica, no se realiza arqueo físico como tal sino que se solicita a la cía un detalle de los arqueos de caja de los distintos centros para poder comparar las cifras con el balance. Se comprueba la fecha del arqueo y que este esté firmado por la persona que lo ha realizado y por un supervisor a ser posible. * Conciliaciones bancarias: La conciliación bancaria es una herramienta para, partiendo del saldo en...

Words: 9054 - Pages: 37

Free Essay

Hv Mario

...MARIO ALBERTO BARRANCO RACEDO Cra. 51 No 79-168 Apto 5 B. Barranquilla. Colombia Celular Colombia 3205715900 E-mail: PERFIL PROFESIONAL Administrador de empresas, bilingüe, Master en creación y apertura de negocios, con experiencia administrativa y comercial en las áreas de servicio, ventas, marketing, comercio exterior y logística. Fortalezas en comunicación, negociación, influencia y liderazgo en los grupos; creatividad y facilidad para establecer relaciones y trabajar en equipo. EXPERIENCIA LABORAL CENTRO COMERCIAL BUENAVISTA SANTA MARTA. Gerente, administrativo. Santa Marta. Mayo 2009 – Enero 2013. Responsabilidades: Administración de la copropiedad horizontal, gran superficie de 67.000 mt2 construidos, mantenimiento, seguridad, aseo, marketing, ventas de áreas comunes, relaciones comerciales, representación legal, manejo de los medios de comunicación, planeación y desarrollo de las asambleas y juntas directivas con los copropietarios y miembros de la Junta Directiva. Logros:   Ocupación del 100 % de los locales comerciales Centro Comercial Buenavista posicionado como el mejor centro comercial de la ciudad de Santa Marta y uno los más importantes del Caribe Colombiano. Aumentar los ingresos de áreas comunes en un 88% con referencia al 2009. Aumento en un 56% del trafico del centro comercial , de manejar 2.256 vehículos diarios a la fecha manejamos 3.526 Apertura de 8 puntos nuevos de áreas comunes y de negocios en concesión.       ...

Words: 639 - Pages: 3

Free Essay

Executive Report

...INFORME EJECUTIVO A continuación se detalla los apartados que debe de contener el Informe Ejecutivo a presentar el desarrollo de cada caso. 1. Presentación Resumen del caso problema a resolver. En forma breve se debe de presentar los por menores del caso problema a resolver, así como los escenarios futuros y consecuencias de no resolver en un tiempo prudente y con información confiable. 2. Metodología empleada Descripción del método y técnica utilizada en la resolución del caso (en este caso se refiere al método y técnica estadística), indicar que tipo de análisis estadístico o matemático se aplico a las distintas variables de estudio. 3. Cuerpo del informe El objetivo es crear un informe gerencial. Este tipo de informes se caracterizan por aportar un resumen interpretativo acerca de los resultados obtenidos durante la aplicación de las pruebas estadísticas aplicadas. Se debe explicar a detalle, cada uno de los puntos detectados durante el estudio matemático, valiéndose de gráficas, tablas y datos numéricos que sirvan como respaldo o justificación para cada afirmación hecha. Se trata de demostrar dominio sobre la información y un adecuado manejo de los datos numéricos; como lo hemos comentado, el objetivo del curso es aprender a manipular variables cuantitativas, aplicar fórmulas y procedimientos, pero fundamentalmente, aplicar e interpretar dicha información a una situación que asemeje a la realidad. Por consiguiente, tendrá un peso significativo este...

Words: 722 - Pages: 3

Free Essay

Informcion General

... PROFESOR: Análisis de Datos TOMAS MINAURO LATORRE S E G U N D O E X A M E N P A R C I A L Fecha : Lunes 28 de octubre de 2002 Duración : 2 horas Instrucciones : Antes de responder las pregunta lea bien su examen para planear su solución. CON MATERIAL DE CONSULTA. SI está permitido el uso de su Computadora personal. Trabaje sus respuestas en su computador directamente en este file usando Word, insertando sus respuestas a continuación de cada pregunta. Contestar únicamente TRES preguntas. La prueba se calificará sobre 60 puntos. Deberán grabar en el disquete que se les proporcionará tanto el file Ex-Parcial-II como el de Excel que use para resolver el examen. No coloque su nombre en ningún lugar de estos dos files, para asegurar una calificación anónima. E X I T O S . . . ! ! ! EL PROFESOR Pregunta 1 (20 puntos) La mujer se vuelto cada vez más importante para los vendedores de autos en los EEUU, ellas compran aproximadamente el 45% de los autos que se venden, además de que influye en casi el 80 % de las ventas. Un dato importante es que la mayoría de las compras de autos nuevos se hacen por autos producidos en su país. Si se trata de autos importados, las mujeres prefieren los autos japoneses, mientras que los hombres se inclinan por los autos europeos. La línea nacional preferida por las mujeres es la de Chevrolet. Chevrolet le ha dado importancia al mercado femenino...

Words: 3335 - Pages: 14

Free Essay


...aparatos electrónicos, como multímetros digitales analógicos, generadores de función, osciloscopios, contadores de frecuencia y otras máquinas para pruebas y mediciones. Altavox vende una línea de medidores de prueba que son populares entre los electricistas profesionales. El modelo VC202 se vende, a través de seis distribuidoras están en Atlanta, Boston, Chigago, Dallas y Los Ángeles y fueron escogidas para atender regiones diferentes. El modelo VC202 se ha vendido bien durante años por confiabilidad y sólida contrucción, pero hay alguna variabilidad en la demanda. En la tabla de la página siguiente se muestra la demanda del producto en las últimas 13 semanas. Estos datos se encuentran en una hoja de cálculo de Excel, Altavox Data, contenida en el DVD del libro. La demanda de las regiones varía entre un máximo de 40 unidades en promedio semanal en Atlanta y 48 unidades en Dallas. Los datos de este trimestre están muy cerca de la demanda del trimestre pasado. La gerencia quisiera que usted experimentara con algunos modelos de pronóstico para determinar cuál debe usarse en un nuevo sistema que va a establecerse. El nuevo sistema está programado para usar uno de dos modelos: promedio móvil simple o suavización exponencial. A continuación tenemos los siguientes datos: SEMANA Atlanta Boston Chicago Dallas Los Angeles TOTAL 1 36 26 44 27 32 2 45 35 34 42 43 3 37 41 22 35 54 4 38 40 55 40 40 5 55 46 48 51 46 6 30 48 72 64 74 7 18 55 62 70 40 8 ...

Words: 2026 - Pages: 9

Free Essay

AnáLisis Mercado Yogurt En Colombia

...que sirva para reforzar la posición dominante del líder de la categoría en un segmento nuevo. YOGURT EN COLOMBIA TAMAÑO DEL MERCADO Perspectivas de crecimiento Se estima que en los próximos años la categoría presentará un crecimiento anual de 10% en unidades y volumen de ventas debido al continuo crecimiento de la base de consumidores y al incremento sostenido de sus ingresos YOGURT EN COLOMBIA CARACTERÍSTICAS MERCADO YOGURT 2. Cuchareable Subcategorías presentes en el mercado Colombiano 2.1. Saborizado 1. Líquido 2.2. Frutado 1.1.Yogurt funcional: 2.3 Funcional Pre/probióticos Pre/probióticos Otro yogurt funcional – impacto salud Otro yogurt funcional – impacto salud 1.2. Yogurt bebible regular 2.4 Yogurt cuchareable regular CANALES DE DISTRIBUCIÓN •...

Words: 2397 - Pages: 10

Free Essay

Seguimiento de Trabajos de Construccion

...2012 PRACTICA PROFESIONAL SUPERVISADA PRACTICA PROFESIONAL SUPERVISADA Enero 2013 Presentado por: Emmanuel De Leon UNIVERSIDAD SANTA MARÍA LA ANTIGUA FACULTAD DE CIENCIAS Y TECNOLOGIA ESCUELA DE INGENIERIA CIVIL PROYECTO DE GRADUACIÓN PRÁCTICA PROFESIONAL SUPERVISADA “PLANIFICACIÓN, SEGUIMIENTO Y CONTROL DE LOS TRABAJOS DE CONSTRUCCIÓN DE LA ZONA DE PATIOS Y TALLERES DE LA LÍNEA UNO DEL METRO DE PANAMÁ” A REALIZAR EN LA EMPRESA: CONSORCIO LÍNEA 1 – METRO DE PANAMÁ SUPERVISADO POR: ING. OMAR DARIO DIAZ PRESENTADO POR: EMMANUEL DE LEON 8-807-2428 XX de Enero de 2013 AGRADECIMIENTO Son numerosas las personas a las que debo agradecer por ayudarme en el logro de mi carrera, es demasiado poco el decir gracias, para expresar la gratitud que siento hacia ustedes, sin embargo les agradeceré brevemente a mis seres más allegados quienes han estado a mi lado durante todo este tiempo. Le doy gracias a Dios, por estar conmigo en cada paso que doy, por fortalecer mi corazón e iluminar mi mente y por haber puesto en mi camino a aquellas personas que han sido mi soporte y compañía durante todo mi periodo de estudio y realización de esta práctica profesional. Les agradezco hoy y siempre a todos mis familiares, amigos y profesores por su apoyo, alegría, esperanza y además en todo momento me dieron buenos consejos que me brindaron la fortaleza necesaria para seguir adelante y alcanzar exitosamente mis metas. Por último y como agradecimiento especial, le agradezco...

Words: 6143 - Pages: 25

Free Essay


...Tarea #6 Estructura informática de la Empresa (AC 9202-T005) Profesor Dr. Dexter Mena Jose Mauricio Ureña Jimenez 1 er Trimestre 2013 2 INDICE Página Que significa Gestión del Conocimiento 3 Qué herramientas informáticas asocian este concepto 5 Qué significa (BI Business Intelligence) 7 Bibliografía 11 3 ¿Que significa Gestión del Conocimiento? Gestión del conocimiento es el proceso por el cual una organización, facilita la trasmisión de informaciones y habilidades a sus empleados, de una manera sistemática y eficiente. Es importante aclarar que las informaciones y habilidades no tienen por qué estar exclusivamente dentro de la empresa, sino que pueden estar o generarse generalmente fuera de ella. Este matiz final es muy importante. Generalmente la mayoría de las empresas identifican gestión del conocimiento solamente con la información y habilidades internas de la empresa, lo que se conoce como Business Intelligence o inteligencia empresarial. De esta forma casi todos los esfuerzos se orientan a canalizar la información y habilidades que ya posee una organización centrándose en la eficiencia de los procesos de comunicación interna a través de la implantación de sistemas como CRM, ERP y un CMI Esto ha sido tradicionalmente así por que siempre ha sido mucho más fácil controlar los volúmenes de información interna que la información externa que se encuentra fuera de la organización que es más difícil de encontrar, buscar, seleccionar y organizar...

Words: 2405 - Pages: 10

Free Essay


...República Bolivariana de Venezuela Ministerio de Educación Superior Universidad Católica Andrés Bello Facultad de Ciencias Económicas y Sociales Escuela de Administración y Contaduría Contaduría Pública Informe de Pasantías Servicio Nacional Integrado de Administración Aduanera y Tributaria (SENIAT) Tutor Empresarial: Lic. Neckers Rodríguez Autor: Roseira Ines C.I: 20.413.163 Caracas, Septiembre de 2012 Índice Capitulo I Introducción……………………………………………………………………………4 Capitulo II Antecedentes de la creación del Servicio Nacional Integrado de Administración Aduanera y Tributaria (SENIAT)…………………………………………………….5-6 Misión…………………………………………………………………………………..6 Visión…………………………………………………………………………………...6 Nuestros Valores Son La Declaración De Nuestras Creencias y Principios…..6-7 Actividad a que se dedica…………………………………………………………...7 Políticas………………………………………………………………………………..7 Estrategias…………………………………………………………………………….8 Ubicación Geografica………………………………………………………………...8-9 Organigrama estructural……………………………………………………………..10 Estructura de la Organización……………………………………………………….11 Superintendente Tributario………………………………………………………….11-12 Gerencia de recaudación……………………………………………………………12-13 Gerencia de Aduanas Principales………………………………………………….13-14 Gerencia de Arancel…………………………………………………………………14 Gerencia de valor…………………………………………………………………….14-15 Gerencia de fiscalización……………………………………………………………15-16 Capitulo III Descripción de la Organización……………………………………………………...

Words: 4413 - Pages: 18

Free Essay

Licitacion Turismo En Colombia

...y aceptar el libre derecho de expresar ideas y credos. * Transparencia: Relaciones claras que aseguren mutuo crecimiento. * Trabajo en Equipo:Un grupo humano altamente calificado para cumplir con los objetivos de la organización. SERVICIOS Ofrecemos servicios de consultoría, diseño y planeación de gestión y gerencia de proyectos. Aplicando nuestra metodología. Elaboramos todo el ciclo de vida del proyecto desde el inicio del mismo hasta su finalización, pasando por las fases de planificación, ejecución, control y gestión de cambios. * CONSULTORIA * DISEÑO * PLANEACIÓN DE GESTIÓN. * ELABORACIÓN COMPLETA DEL CICLO DE VIDA * ASESORÍA EN CUALQUIER PARTE DEL PROCESO * GESTIÓN DEL CAMBIO MANAGERS 1. ARLEIDY...

Words: 3213 - Pages: 13