VARIANCE - How to calculate variance in excel?

How to calculate variance in excel? How to calculate the variance,  using Excel formulas?

The variance is the "average square deviation of the individual results to their average." Interpretation of variance is difficult due to the fact that the (denominator) unit is  a squared, in which the given characteristic is measured - it can be stated that the higher the variance, the greater the diversity of the population with the trait.

Accordingly - much more reliable measure is the standard deviation -the square root of the variance. In this situation, the interpretation is simple, because the standard deviation does not square the units . It indicates how many average values deviate + / - from the arithmetic mean.

Returning to the variance function (formula), variance used in Excel uses the following formula:

Formula Excel:

VAR ( value1, value2,. ..)

Value1, value2, .. . is from 1 to 255 argument values that correspond to the sample population.

How to add a new WorkSheet in Excel Workbook?

How to insert a new Sheet  in an Excel Workbook?

In Excel, instead of putting everything in one Sheet, you can sort out the information and distribute it in several sheets. Therefore, you may need to add a few or even several new sheets.

There are three ways to add a new worksheet to a workbook:

• Click INSERT SHEET controls to the right of the last tab sheet,

Insert Worksheet - Excel 2010 Screen

• Press buttons SHIFT + F11,
• Right-click the sheet tabs, and then select INSERT from the shortcut menu. INSERT window open. Activate the GENERAL card. Then click the Wroksheet icon and click OK.

Insert Worksheet - Excel 2010 Screen

How to Rename the Worksheet in Excel?

How to Change the Name  of an Excel Worksheet?

Default names of Excel worksheets:

Sheet1, Sheet2, etc. 

To rename a sheet you have to double click its tab. Excel highlights the worksheet name, so that it can be modified or replaced with a new one.

Rename Sheet - Excel 2010 Screen

Sheet name can contain up to 31 characters, including spaces.
You can not use characters: / \ [] <>. ? '*

Numbered list in Excel - How to make?

How to create a numbered list in Excel? How can you generate a list of numbers in Excel ?

• Mark in the Excel spreadsheet cell A1, and type 1, then cell A2 and type 2,
• Select area A1: A2,
• Move your mouse to the lower right corner of the selected field - square (fill handle)

Excel 2010 Screen

• Click in the box left mouse button and drag vertically down to cell A10,
• Excel automatically enters the numbers in the selected area when the mouse button is released - will increase the value in the next cell on the difference between the numbers in cells A2-A1, etc.

Excel 2010 Screen

Video tutorial:

LOAN CALCULATOR in Excel

How to calculate loan rates in Excel?   How to calculate a monthly payment in Excel?

To calculate the payments of a loan in Excel, you can use a simple formula PMT which calculates the amount of the loan or the loan based on constant payments and a constant periodic rate of interest.

 PMT(rate, nper, pv, fv, type)

Rate -  is the interest rate for the loan
Nper -  is the total number of payments for the loan
Pv -  is the present value, or the total amount that a series of future payments is worth now; also known as the principal
Fv - is the future value, or a cash balance you want to attain after the last payment is made.

If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0. Type - is the number 0 (zero) or 1 and indicates when payments are due.

You can used to PPMT function - Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

PPMT(rate, per, nper, pv, [fv], [type])

Rate - Required. The interest rate per period.
Per - Required. Specifies the period and must be in the range 1 to nper.
Nper - Required. The total number of payment periods in an annuity.
Pv - Required. The present value — the total amount that a series of future payments is worth now.
Fv - Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type - Optional. The number 0 or 1 and indicates when payments are due.


Download example XLS file:

How to connect multiple cells in Excel text ("&" character or CONCATENATE)?

How to connect multiple cells in Excel text? To link text cells in Excel you can use the concatenation operator - character "&" or function CONCATENATE (arguments)?

If you want to connect two (or more) of the cells, for example, cell B2 contains Gliwice and cell B3 contains Silesia, we apply the function:

= B2 & B3   or   = CONCATENATE (B5, B6)

but in this case there is no space. To get a space, use the following function:

= B2 & "" & B3   or   = CONCATENATE (B5, "", B6)

Screen - Excel 2010 - CONCENTATE function

Download example XLS file:

How to Calculate a Standard Deviation in Excel?

How to calculate  a Standard Deviation in Excel?

---

In statistics and probability theory, standard deviation (represented by the symbol sigma) shows how much variation or dispersion exists from the average (mean), or expected value (read more on wikipedia).

http://en.wikipedia.org/wiki/Standard_deviation

---

The standard deviation is calculated as the square root of the variance. Thus, the designation of the variance can be regarded as an intermediate step to calculate the standard deviation.

In excel there is a function that directly from the data (sample) calculates standard deviation.

Excel function:
STDEV ( number1, number2, ... ) 

Number1, number2, ... are 1 to 255 arguments corresponding to a sample of the population.

Instead of arguments separated by semicolons, you can use a single array or a reference to an array. The following figure shows the calculation in excel in two ways.

Screen Excel 2010 -  Standard Deviation in Excel

Download example XLS file:

How to create a Pie Chart in Excel?

How to create a chart showing the results of the survey questions? In Excel, you can create charts of various types, but for the show of surveys answers are often used pie chart.

To create a pie chart:

- Locate the INSERT menu and select the CHARTS category and click the pie chart,
- Select the DATA SOURCE you want to appear on the graph,
- In the next step you can customize chart - you can use Chart Styles to eg. change colors.

Chart can also be created first by checking the data to be displayed and then clicking on the type of chart.

Pie chart - Excel 2010 Screen

Download example XLS file:

MORTGAGE CALCULATOR in Excel

How to calculate mortgage rates in Excel? How to make in Excel simple Mortgage Calculator?

---

Mortgage calculators are used to help a current or potential real estate owner determine how much they can afford to borrow on a piece of real estate. Mortgage calculators can also be used to compare the costs, interest rates, payment schedules, or help determine the change in the length of the mortgage loan by making added principal payments (read more on wikipedia).

http://en.wikipedia.org/wiki/Mortgage_calculator

---

To calculate the payments of a mortgage in Excel, you can use a simple formula PMT which calculates the mortgage rate  based on constant payments and a constant periodic interest rate.

PMT(rate, nper, pv, fv, type)

Rate -  is the interest rate for the loan.
Nper -  is the total number of payments for the loan.
Pv -  is the present value, or the total amount that a series of future payments is worth now; also known as the principal.
Fv - is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type - is the number 0 (zero) or 1 and indicates when payments are due.

You can used to PPMT function - Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

PPMT(rate, per, nper, pv, [fv], [type])

Rate - Required. The interest rate per period.
Per - Required. Specifies the period and must be in the range 1 to nper.
Nper - Required. The total number of payment periods in an annuity.
Pv - Required. The present value — the total amount that a series of future payments is worth now.
Fv - Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type - Optional. The number 0 or 1 and indicates when payments are due.


Download example XLS file: