How to calculate variance in microsoft excel 2003?

Excel provides useful statistical functions for finding the variance of a data set. In general, these variance functions retrieve a set of values stored in a worksheet range and then make the expected calculation.

A variance, just to remind readers, is a common measure of describing the spread of observations in a distribution. A variance is related to another statistical measure, the standard deviation. A variance is equal to the square of the standard deviation.

Variance of a Sample with the VAR Function

If the data set you're working with is a sample and you do not want to include logical values or text from the set in the calculation, you use the VAR function. For example, if you're using a new production process that is supposed to increase productivity and have a series of data for the numbers of parts produced each day, you can find the sample variance. The VAR function uses the following syntax:

=VAR (data set range)

Variance of a Sample with the VARA Function

If the data set is a sample but you want to include logical values or text in the calculation, you use the VARA function. Excel counts cells containing the logical value TRUE as 1 and cells containing text or FALSE 0. The VARA function uses the following syntax:

=VARA (data set range)

Variance of a Population Sample with the VARP Function

If the data set you're working with is a population and you do not want to include logical values or text from the set in the calculation, you use the VARP function. The VARP function uses the following syntax:

=VARP (data set range)

Variance of a Population with the VARPA Function

If the data set is a population but you want to include logical values or text in the calculation, you use the VARPA function. The VARPA function uses the following syntax:

=VARPA (data set range)

A Final Note About the Data Set Range Argument

One other note: You can include multiple worksheet ranges as your data set range argument when using the variance functions. For example, if you were calculating the variance of a sample using the VAR function and had your data stored in several different worksheet ranges, your VAR function might look like this:

=VAR(B1:B:10,B101:B110,B200)

How to create charts in microsoft excel 2003?

Excel comes with a built-in chart creator. This wonderful tool allows you to display your data graphically. There are two ways to create a chart in Excel 2003 by using an "instant chart" or by using the Chart Wizard.

To create a chart instantly, highlight the data table, or click and drag the data you want to display in chart form, and press F11. This shortcut will create a columnar chart.

If you would like to create a different kind of chart, you can select the data table or click and drag to highlight the data for the chart, and select the Chart Type button on the Chart toolbar. You can choose your chart type and Excel will create an embedded chart using your selected data.

If you would prefer to use the Chart Wizard to create a chart, select the data you plan to use. Omit grand totals since they will affect the scale of the chart and make it difficult to read.

Click the Chart Wizard button. You will be shown a dialogue box with the first of four steps you'll need to complete to create your chart. Select the chart type you want to use. Click Next.

In Step 2, identify whether your data are in rows or columns, and identify the data range in the field provided, if it is not already there or you want to alter the data range. The Wizard will display a sample chart to show you what your data will look like on the final product. Click Next.

In Step 3, you can add axis labels, a chart title, data labels, legends and a number of other options by selecting the proper tabs in this step. Once you have set these options, click Next. (You can edit this information later via the Chart menu, if desired.)

In Step 4, choose the location of your chart. It can be embedded in the current worksheet or as a new, separate sheet. Click Finish and your chart will appear in the location you've specified

What is the meaning of charting terms in Microsoft excel 2003 ?

Excel's Chart Wizard and documentation use several charting terms: data markers, data-marker descriptions, legend, chart text, plot area, and chart area.

You'll find it useful to understand just what these words and phrases mean, so the list that follows provides definitions.

Data markers

Data markers are the graphical elements used to represent individual data point values in a chart. In the case of a line chart, for example, Excel uses uses symbols, or points, on a line to show data point values. These symbols or points are the data markers.

Other types of charts in Excel use other data markers. A chart that uses columns or bars, for example, has column or bar data markers. A pie chart has pie-slice data markers, and so on.

Data marker descriptions

Excel typically describes and qualifies data markers using the data-marker descriptions such as axis scales and data labels.

Different types of charts use different data-marker descriptions. Bar, column, and line charts use axis scales. Pie and doughnut charts use data labels.

Legend

A legend names and identifies the data series you've plotted. In the case of a pie chart, for example, the legend typically names the data series and then also shows which colors are used for which pie slices.

In charts that show multiple data series, the legend lists all of the data series and visually shows chart viewers how to identify data series.

Chart text

Chart text predictably describes a chart or some part of a chart. A chart might include a title that shares the chart message such as "Industry Continues to Grow" or a subtitle that clarifies some bit of information about the chart such as "(five-year forecast of domestic revenues)".

Plot area

The plot area of a chart is the area that includes the data markers and data-marker descriptions.

In many charts, the plot area is a rectangle that shows the lines and scales representing the plot area.

In same cases--such as the case of a pie chart or doughnut chart----the circle that shows the slices of pie and the data labels that identify the slices of pie comprise the plot area.

Chart area

The chart area includes plot area, any chart text, and a legend. In other words, the chart area represents the whole enchilada.

How to deactivate basic shortcute in microsoft excel 2003?

Over the last few days I have been running basic and advanced Excel courses and one of the common questions that people are asking me is "What is a simple way to Disable a Short cut in Excel?" I have spent lots of time thinking about a non-programmatic way to do this and there is a very simply solution.

Write a macro …

Let us say for example you are writing a Microsoft Excel template that you need various users to enter data into, but you want to limit their ability to use the features of Cut, Copy and Paste. Okay, so let me start off by testing your memory… What is the shortcut keystrokes for Cut, Copy and Paste … I will put you out of your misery if you do not know, they are -

Cut - [Ctrl] + [X]

Copy - [Ctrl] + [C]

Paste - [Ctrl] + [V]

Now to override these basic shortcuts we simply use a macro to do so, keeping in mind that the macro will only disable the short cuts for that document, as long as you choose the right options. Let us go through the process to disable the short cut, Cut.

The first step is to open a new spreadsheet in Microsoft Excel, then go to the Tools menu and then choose Macro from the drop down menu, then select Record New Macro …

The Record Macro dialog box will be visible. In the Macro Name text box, type the text EliminateCut. Now not that I did not have a space between the word Eliminate and Cut. If you have a space, the Excel application will come up with an error because in Microsoft Excel you are NOT allowed to have spaces in macro names.

Second step click in the box that say shortcut key and type in the letter X. In the Store in Macro drop down box the option "This Workbook" should be selected. If it is not, make sure you select it from the drop down box or you will disable Cut for all future workbooks, which will not be a positive outcome. Finally press the OK button.

Word of warning … Do Not Click or do anything on the Worksheet. The Stop Recording toolbar should appear, simply click on the stop button which looks like a stop button on a CD Player and the macro recorder will stop.

Now if you put some text into a cell and deactivate the cell by pressing the [Enter] key, then go back and click on it once and try out your shortcut. What you will notice is that the cell is selected but if you click on another cell and try and paste the value by using [Ctrl] + [V] you will notice that the cell does not move and as such the short cut [Ctrl] + [X] is disabled.

I should note that there are some limitations to this technique. If you actually activate the cell and select the text in the cell itself and then try [Ctrl] + [X] you will find that the text will be cut, but if you use [Ctrl]+[X] on a normally selected cell you will notice [Ctrl] + [X] is disabled.

How to create a formula to calculate mortgage in microsoft excel 2003?

One of the really cool parts aspects of Microsoft Excel is the functions Microsoft has created for you to use. This means that rather than have to develop a function from scratch you can use pre-built ones to do a plethora of tasks like Building your own Mortgage Calculator. The Mortgage Calculator or PMT function is just one of many Financial Functions available.

Okay, so how to build a mortgage calculator…

The first thing we have to do is to start by setting up a few basic headings. So lets begin by starting a new workbook and clicking in the first cell A1. Enter into cell address A1 the heading - Monthly Loan Repayments. Next off, enter into cell address A2 - Amount of Loan, cell address A3 - Interest Rate, cell address A4 - Length of Loan and then in A6 - Monthly Repayment.

In example mortgage calculator, we will take the Loan Amount, Interest Rate and Length of Loan and calculate your Monthly Repayment. Okay so in the corresponding field B1 enter the value of $200,000 and make sure you format the field as a currency. In cell B2 enter a value of 9.25% and format the field as a percentage and then finally enter in a value for the Length of the Loan as 25. The value you enter into the Length of the Loan field is in years.

Now its time to create the formula that will do your calculation for the Monthly Repayment. The function we will use for this calculation is called the PMT function. The PMT function always returns a negative number so one of the things we will need to do is to convert it into a positive number, but a little on that later.

There are three arguments we will use for this formula and they are -

= PMT(Monthly Interest Rate, Number of Payments, Amount Borrowed)

So to work out the Monthly Interest Rate we simply take the value in B3 and divide it by 12 - B3/12. The PMT function works on the basic of the number of payments you are going to make, so if we are going to make monthly payments on our mortgage we simply take the number of years in cell B4 and multiply it by 12 - B4 *12.

This means that to calculate the Monthly Repayment for our mortgage we need to enter the following formula -

= PMT(B3/12, B4*12, B2)

Now as I said before, the PMT function always returns a negative value, so to turn this into a positive value we simply type the PMT function with the Absolute Function encapsulating it as shown below -

= ABS(PMT(B3/12,B4*12,B2))

Simply type the formula above into the cell B6 and press the enter key. You must now format the cell address B6 as a currency and you can do that by simply pressing the Dollar Symbol on the Formatting Toolbar. As soon as you enter the formula and press enter you should get a result of $1712.76. If you do not get this answer, simply go back and make sure that you have entered the formula correctly.

The cool part about this Mortgage Calculator is that you can go back and change any one of the values in B2, B3 and B4 which are the Loan Amount, Interest Rate and Length of Loan to work out what your monthly mortgage repayments will be.

The cool part about this simple tool is that it tells you really quickly whether borrowing massive amounts from the bank is worth it and whether you can really afford that mortgage. Why not check out what your repayments will be if your interest rate went up by 2 or 3%, it can be really interesting to see the impact on your budget.

Simple tools like this can save you thousands of dollars and can also help you see what changes interest rates will have on your own budget. It is certainly worthwhile building yourself a Budgeting Spreadsheet and the mortgage calculator to work out just what you really can afford especially in these uncertain times.

How to sum cells by Using labels in Microsoft Excel 2003?

Microsoft Excel is a fantastic tool but one of its downfalls is the use of cell addresses especially when you are trying to sum a series of numbers; however Microsoft has a really cool tool that allows you to add up a series of cells simply using the labels around your data. In this article we will investigate the steps you need to follow to be able to use Labels to Sum cells in Microsoft Excel.

Let us get started …

To show you how using labels works, the first step we need to undertake is to simply create a new set of data, so open a new Microsoft Excel workbook and click on Sheet 1. First off we are going to build the worksheet so in cell A1 I want you to type - Years, in cell B1 type the word Values and in cell C1 type the word Values1 and in cell D1 type the word Total. These four values we have typed into cell A1, B1, C1, D1 are labels. We are now going to put into the worksheet three more labels. In cell A2 type the value 1999, in cell A3 type the value 2000 and in cell A4 type the value 2001. The last three values entered will in fact become labels but we will convert them to labels a little latter on.

In the remaining cells simply put the following values:

B2 29 C2 32
B3 54 C3 99
B4 62 C4 72

Now that we have built a very simple spreadsheet and we have a few values to work with we have to tell Microsoft Excel to actually accept labels in our formulas in our spreadsheet. We do this by first going to the Tools menu and then choosing the Options command from the drop down menu. The Options dialog box will now be open in front of you. Simply choose the Calculation tab and in the bottom right hand corner you will see a check box that says Accept Labels in Formulas and you need to click on the check box so that it has a tick in it. Then to complete the process simply press the OK button.

Now we can use the labels we put in B1 and C1 to add up the values.

Lets try it out…

In cell B5, which is the Values column I want you to type the formula -

= Sum(Values)

The cell should return the total of 145. What you will notice though is that the formula looked up the column to where the label was and said everything in this column will be added together. We could have simply typed the following formula instead of using labels -

= Sum(B2:B3)

However, as I am sure you will agree, using labels makes your formulas a lot easier to read and much clearer to understand. Now it is your turn. In the cell C5 write the equivalent label formula for that cell. I will give you a hint if you are not sure -

= Sum(Values1)

How did that go?

Alright, the next issue we are going to visit is using numbers as formula labels. Now if we simply typed in cell D2 the formula -

= Sum(1999)

All that would be returned is the value 1999 so this will not work at all. In fact what we need to do is to tell Microsoft Excel that we want the cells in A2, A3 and A4 to be treated as labels. The first step we must do is to select cells A2,A3 and A4, then go to the Insert menu, choose Name from the drop down menu and then choose the Label command from the expanded menu. The Label Ranges dialog box will now be visible in front of you. All you simply have to do, to have the cells we chose before defined as Labels is to press the Add button and then press the OK button.

Click once on the cell D2. What we are going to do now is to type in the same formula I mentioned before and that is -

= Sum(1999)

By the way, press the Enter key if you have not done so already. As soon as you do that you should see that it adds up the values in cells B2 and C2 and you will see the value of 61 in your cell.

Try creating the formulas for the other two cells -

D3 = Sum(2000)

D4 = Sum(2001)

Finally, just to finish our spreadsheet off , we can total all of our total values in cells D2, D3 and D4 by typing the following formula in D4 -

= Sum(Total)

Using labels in Microsoft Excel is a very clean way of summing your values as it ensures that you do not miss a cell address and it makes the formulas a lot simpler. One issue that I do teach my students is not to put blank rows in your spreadsheets as I have seen cases where the blank rows actually affected the ability of the application to work out what was a label and what is not. By following the design process I outlined above you will find that you have absolutely no troubles in using Labels to sum cells in your spreadsheets.

how to use formatting toolbar to format cells in microsoft excel 2003?

Microsoft Excel 2003 provides several formatting tools to help you format information stored in the cells of a worksheet. There are several convenient ways to format cell information, but this article will cover the Formatting Toolbar.

Excel provides a formatting toolbar, which can be made to appear in the document window. The formatting toolbar provides quick access to the most common formatting commands.

To enable the Formatting Toolbar, choose View > Toolbars and select the Formatting Toolbar. The Formatting Toolbar allows you to adjust the font and font size, text attributes, and text alignment. The Formatting Toolbar also contains a one-click button that will allow you to merge several selected cells together and center text inside them.

The Formatting Toolbar also contains a Currency Style button that allows you to quickly format currency figures. To configure the Currency Style properties, right click on the currency style button and set the properties according to your preference. Each time you use the Currency Style button, these properties will be applied. Other quick formatting buttons include the Percent Style and Comma Style buttons, which control how percentages and commas are used on cell data.

The Formatting Toolbar also contains buttons that allow you to increase or decrease the precision of a real number, by shifting the decimal point to the left or right. You can also increase or decrease text indentations, control the application of grid lines, and change the color of the cell background and the text color within a cell.

Excel 2007 has created a "ghosted" quick access palette that has now made this unnecessary so this will not be relevant to Office 2007 users, well done Microsoft!