Formula de excel

More Excel Formulas and Functions

Overview

Microsoft Excel has been through many revisions. This tutorial was created using Excel 2000. Other Excel versions contain similar functions, although they may differ in operational detail.

This tutorial assumes a basic understanding of spreadsheet fundamentals. An introductory-level can be found at the Portical site athttp://www.portical.org/ExcelQuickStart.doc.

A Few Notes

Although a anyone can do amazing things with Excel, including creating graphs, charts and performing complex calculations, the basic rule of computing applies: Garbage In, Garbage Out. Performing a complex average of several classes’ Percentile Ranks will produce a result; however, the result won’t mean much as it is mathematically inappropriate toperform such an average. To perform this calculation correctly, you first have to convert your PR scores to Normal Curve Equivalent scores, and then do the complex average.

It is always easier to make sure that your calculation is correct prior to the Big Meeting, rather find out you have a problem in the Big Meeting. Take it from someone who knows…

The Function Wizard

The easiest methodof accessing formulas in Excel is through the Function Wizard. For most formulas, this is a works very well. The Function Wizard can be accessed though the Standard Menu by clicking on the button or by going to Insert and then Function on the file menu. If you know the function you want to utilize, you can also enter the function directly into the Formula Bar.

Sample One – OverAchieverElementary Budget

The first example we will explore is a simple budget spreadsheet that lists amounts that have been allocated to several areas over a three-year period. In this section, we will be using the Average and variations of the Count function. The budgeted values are shown below.

The first average we will do is the average of one column, FY 2003. I want this average to be in cell B8.Click on cell B8 to make it the active cell (When you do this, you will see that the Cell Name Box will change to B8).

To start the Function Wizard, click on the fx button. The Paste Function window will appear. At this point, you can either select from a function area in the left pane, click on the All area to see all available functions, or select one of the Most Recently Used functionsfrom the right side pane.

When you enter a function, Excel will try to guess the range of cells that you want to include. In this case, Excel guessed well. Had there been a blank in the list, the result would not have included any cells above the blank cell.

If you need to change the range of the average, you can either enter the new range manually or click on the small spreadsheet iconnext to the cell range. This will allow you to highlight the range you want in the average directly in the spreadsheet.

Interestingly, Excel will also create functions that span several columns of data. If you wanted to see what the average that was budgeted for all of the areas over the three years, you could enter into the Formula Bar:
=AVERAGE(B3:D7)

Nested Functions

Although theFunction Wizard works well for most situations, there are times when the calculation requires something more complex than the Function Wizard will handle. Many times, this requires nesting functions. One of my personal favorites is the IF Statement.

The IF Statement contains three sections, the Logical Test (Must be True or False), the Value if the Logical Test is True, and the Value if theLogical Test is False. The IF Statement follows the format:

=IF(Logical_Test,Value_If_True,Value_If_False)

The interesting part of this is that either (or both) of the True or False outcomes can be a formula instead of a value.

Sample Two – Counting Scores

Lets say that we have two year’s worth of NCE scores for Total Reading and we want to know how many students scored above the...