Excel - Microsoft Excel VARP Function

Description

The Excel VARP function returns the Variance of a given set of values. 

The syntax of the function is : 

VARP( number1, [number2], ... ) 

Where the number arguments provide a minimum of 2 numerical values to the function. You can enter up to 254 number arguments to the Varp function in Excel 2007 or Excel 2010, but you can only enter up to 30 number arguments in Excel 2003. 

Note that the Varp function is used when calculating the variance of an entire population. If your data is just a sample of the population (eg. if your data set records the individual heights of a sample of UK males), you need to use the Excel Var or the Excel Var.S function. 

Note also, that the Varp function ignores text values and logical values if these are supplied as part of an array. However, if they are supplied directly to the function, text representations of numbers and logical values are interpreted as numbers. If you want a population variance function that does not ignore text and logical values that are supplied as a part of an array, consider using the Varpa function. 

A company keeps a record of its monthly sales figures, over the last three years. These are stored in cells B3 - B14, D3 - D14 and F3 - F14 of the example spreadsheet on the right. 

The variance of the three years' sales figures is calculated in cell H3 of the spreadsheet. 

The formula for this, as shown in the formula bar, is : 

=VARP( B3:B14, D3:D14, F3:F14 ) 

As shown in cell H3, the variance for the 3 years of sales figures is 6,170,524.69. 

Other Argument Types

The example above shows the arguments to the Varp function being input in the form of 3 cell ranges. However, you can also input figures directly, as individual numbers or number arrays. 

For example, if, during January and February 2012 the sales figures are 13,000 and 14,500 we can add these directly into the above function as follows: 

Either as individual numbers: 

=VARP( B3:B14, D3:D14, F3:F14, 13000, 14500 ) 

Or, as an array of numbers: 

=VARP( B3:B14, D3:D14, F3:F14, {13000,14500} ) 

This gives the updated variance of 5,930,921.05. 

Varp Function Errors

If you get an error from the Excel Varp Function, this is likely to be the #DIV/0! error: 

Common Errors 

Excel- Microsoft Excel VAR Function

Description

The Excel VAR function returns the variance of a sample set of values from a population. 

The format of the function is : 

VAR( number1, [number2], ... ) 

Where the number arguments provide a minimum of 2 numerical values to the function. You can enter up to 254 number arguments to the Var function in Excel 2007 or Excel 2010, but you can only enter up to 30 number arguments in Excel 2003. 

Note that the Var function is used when calculating the variance for a sample of a population (eg. if your data set records the individual heights of a sample of UK males). If you are calculating the variance for an entire population, you need to use the Excel Varp or the Excel Var.P function. 

Note also, that the Var function ignores text values and logical values if these are supplied as part of an array. However, if they are supplied directly to the function, text representations of numbers and logical values are interpreted as numbers. If you want a sample variance calculation that does not ignore text and logical values that are supplied as a part of an array, consider using the Vara function. 

Var Function Example

Imagine you wanted to find out the variance of the heights of adult males in London. It is not realistic to measure the height of all males, but you could take a sample of the population and measure their heights. 

The example spreadsheet on the right stores the measurements (in cm) of 3,000 adult males. The measured heights are stored in cells B3 - B1002, D3 - D1002 and F3 - F1002. 

The variance of the heights of the sample group is calculated in cell H3 of the spreadsheet. The formula for this, as shown in the formula bar, is : 

=VAR( B3:B1002, D3:D1002, F3:F1002 ) 

As shown in cell H3, the variance of the individual heights of the sample group is 9.261904762 cm. 

Other Argument Types

The example above shows the arguments to the Var function being input in the form of 3 cell ranges. However, you can also input figures directly, as individual numbers or number arrays. 

For example, if you wanted to include two further heights, of 176cm and 177cm into the sample we could add these directly into the above function as follows: 

Either as individual numbers: 

=VAR( B3:B1002, D3:D1002, F3:F1002, 176, 177 ) 

Or, as an array of numbers: 

=VAR( B3:B1002, D3:D1002, F3:F1002, {176,177} ) 

Var Function Error

If you get an error from the Excel Var Function, this is likely to be the #DIV/0! error: 

Common Errors 

Some users may also encounter the following problem with the Var function: 

Common Problem: Values Stored as Text 

If you are getting an unexpected result from the Var function, this may be because you are supplying the function with text representations of numbers, rather than numerical values. 

The Var function will interpret logical values or text representations of numbers if they are supplied directly to the function. However, if the function is supplied with an array of cells containing logical values or text representations of numbers, these cells are ignored in the calculation. 

The different Excel data types are explained in more detail on the Excel Formatting page. 

Solution 

You can fix this problem by converting all text representations of numbers into numerical values. One way to do this is using the Text-To-Columns command: 

Excel - Microsoft Excel TRIMMEAN Function

Description

The Excel TRIMMEAN function calculates the trimmed mean (or truncated mean) of a supplied set of values. 

The syntax of the function is : 

TRIMMEAN( array, percent ) 

where the function arguments are: 

Note that, the specified percentage is the total percentage of values to be excluded. This percentage is divided by two, to get the number of values that are removed from each end of the range. 

It should also be noted that, when Excel is calculating how many values to discard from the supplied array of values, the calculated percentage is rounded down to the nearest multiple of 2. For example, if you want to calculate the trimmed mean of an array of 10 values, then: 

Trimmean Function Example

Cells B1-B3 of the spreadsheet below show 3 examples of the Excel Trimmean Function, all of which are used to calculate the trimmed mean of the values in cells A1-A10, for different percent values. 

The spreadsheet on the left shows the format of the functions and the spreadsheet on the right shows the results. 

Note that, in cell B1 of the spreadsheet above, the percent argument is 15%. As there are 10 values in the supplied array, the number of values to be ignored is 1.5 rounded down to the nearest multiple of 2 which is zero. 

Trimmean Function Errors

If you get an error from the Excel Trimmean function this is likely to be one of the following: 

Common Errors 

Excel - Microsoft Excel TREND Function

Description

The Excel TREND function calculates the trend line through a given set of y-values and (optionally), a given set of x-values. The function then extends the linear trendline to calculate additional y-values for a further supplied set of new x-values. 

The syntax of the function is : 

TREND( known_y's, [known_x's], [new_x's], [const] ) 

Where the arguments are as follows : 

If more than one new y-value is to be calculated by the Excel Trend function, the new values will be returned as an array. Therefore, the function must be entered as an Array Formula. 

Array Formulas: 

To input an array formula, you need to first highlight the range of cells for the function result. Type your function into the first cell of the range, and press CTRL-SHIFT-Enter.

Go to the Excel Array Formulas page for more details. 

Trend Function Example

The spreadsheet below shows a simple example of the Excel Trend Function being used to extend a series of x- and y-values that lie on the straight line y = 2x + 10. These are stored in cells A2 - B5 and are also shown in the graph. 

The Trend function uses the least squares method to find the straight line that has the best fit for the provided known x- and y- values. In this simple example, the line of best fit is the straight line y = 2x + 10. 

Once Excel has calculated the straight line equation, it can use this to calculate the new y-values for the provided new x values. 

In this example, the values of the [new_x's] are stored in cells A8 - A10 and the Excel Trend function has been used, in cells B8 - B10, to find the corresponding new y values. The equation for this, as shown in the formula bar, is : 

=TREND( B2:B5, A2:A5, A8:A10 ) 

It is seen that the Trend function in the formula bar is encased in curly braces { }. This indicates that the function has been input as an Array Formula. 

Note that, although the points in the example fit exactly along the straight line y = 2x + 10, this is not essential. The Excel Trend function will find the line of best fit for any set of values provided to it. 

Trend Function Errors

The most common errors from the Excel Trend function are listed in the table below : 

Common Errors 

Excel - Microsoft Excel STEYX Function

Description

The Excel STEYX function calculates the standard error for the line of best fit, through a supplied set of x- and y- values. 

The format of the function is : 

STEYX( known_y's, known_x's ) 

Where the function arguments are: 

The known_y's and known_x's arrays must contain the same number of data values. 

Note that the Steyx function will interpret text representations of numbers as numeric values. Other text values, that cannot be interpreted as numbers are ignored, along with the corresponding value in the other array of x- or y- values. 

Steyx Function Example

Cells A2 - A10 and B2 - B10 of the spreadsheet below list a number of known x and known y values, and also shows these points, plotted on a chart, along with the line of best fit through the points. 

The Standard Error for the line of best fit can be calculated by the Excel Steyx function. This, function, which is shown in cell C12 of the example spreadsheet, has the form: 

=STEYX( B2:B10, A2:A10 ) 

This returns the value 1.201186347 as the calculated standard error. 

Steyx Function Errors

If you get an error from the Excel Steyx function this is likely to be one of the following: 

Common Errors 

Excel - Microsoft Excel STDEVPA Function

Description

The Excel STDEVPA function calculates the standard deviation of a supplied set of values. 

The format of the function is : 

STDEVPA( number1, [number2], ... ) 

where the arguments, number1, [number2], etc, are one or more numerical values or references to cells containing numbers. 

If you are using Excel 2007 or Excel 2010, you can enter up to 255 number arguments to the function. However, in Excel 2003, the function can only accept up to 30 number arguments. 

Note that the Stdevpa function is used when calculating the standard deviation for an entire population. If you are calculating the standard deviation of an sample population, you need to use the Stdev or the need to use the Stdeva function. 

STDEVPA & STDEVP Functions

The Excel Stdevpa function is very similar to the Excel Stdevp function, in that both functions calculate the standard deviation of a supplied set of values, representing an entire population. 

The difference between these two functions applies when an array of values, containing text or logical values is supplied to the function. In this case, the Stdevp function ignores the text and logical values, whereas the Stdevpa function assigns the value 0 to text and the values 1 or 0 to logical values. 

The rules for this are shown in the table below, compared to the rules for the Stdevp function. 

Stdevpa Function Example

The following spreadsheet shows the Excel Stdevpa function used to calculate the standard deviation of the set of values in cells A1-A4 and in cells A1-A6. 

The format of the functions are shown in the spreadsheet on the left and the resulting values are shown in the spreadsheet on the right. 

Note that, in the spreadsheet above, the function in cell B2 includes the values in cells A5 and A6. In this case, the logical value TRUE in cell A5 is treated as the value 1 and the text in cell A6 is treated as the value 0. This is shown by the example in cell B3, in which cells A1 - A4 and the values 1 and 0 give the same result as the standard deviation calculation for cells A1 - A6. 

Stdevpa Function Error

If you get an error from the Excel Stdevpa function this is most likely to be the #VALUE! error: 

Common Errors 

Excel - Microsoft Excel STDEVP Function

Description

The Excel STDEVP function calculates the standard deviation of a supplied set of values. 

The format of the function is : 

STDEVP( number1, [number2], ... ) 

where the arguments, number1, [number2], etc, are one or more numerical values or references to cells containing numbers. 

If you are using Excel 2007 or Excel 2010, you can enter up to 255 number arguments to the function. However, in Excel 2003, the function can only accept up to 30 number arguments. 

Note that the Stdevp function is used when calculating the standard deviation for an entire population. If you are calculating the standard deviation of an sample population, you need to use the Stdev function. 

Note also, that the Stdevp function ignores text values and logical values if these are supplied as part of an array. If you want a function that includes text and logical values in the calculation, consider using the Stdevpa function. 

Stdevp Function Example

A company keeps a record of its monthly sales figures, over the last three years. These are stored in cells B3 - B14, D3 - D14 and F3 - F14 of the spreadsheet on the right. 

The standard deviation of the three years' sales figures is calculated in cell H3 of the spreadsheet. The formula for this, as shown in the formula bar, is : 

=STDEVP( B3:B14, D3:D14, F3:F14 ) 

As shown in cell H3, the standard deviation for the 3 years of sales figures is 2,484.05

Other Argument Types

In the example above, the arguments to the Stdevp function are input as 3 cell ranges. However, you can also input figures directly, as individual numbers or number arrays. 

For example, if, during January and February 2010 the sales figures are 13,000 and 14,500, you could add these directly into the above function as follows: 

Either as individual numbers: 

=STDEVP( B3:B14, D3:D14, F3:F14, 13000, 14500 ) 

Or, as an array of numbers: 

=STDEVP( B3:B14, D3:D14, F3:F14, {13000, 14500} ) 

This gives the updated standard deviation value of 2,435.35

Stdevp Function Errors

If you get an error from the Excel Stdevp function this is likely to be one of the following: 

Common Errors 

Excel - Microsoft Excel Stdeva Function

Description

The Excel STDEV function calculates the sample standard deviation of a supplied set of values. 

The format of the function is : 

STDEV( number1, [number2], ... ) 

where the arguments, number1, [number2], etc, are one or more numerical values or references to cells containing numbers. 

If you are using Excel 2007 or Excel 2010, you can enter up to 255 number arguments to the function. However, in Excel 2003, the function can only accept up to 30 number arguments. 

Note that the Stdev function is used when calculating the standard deviation for a sample of a population (eg. if your data set records the individual heights of a sample of UK males). If you are calculating the standard deviation of an entire population, you need to use the Stdevp or the Stdevpa function. 

STDEVA & STDEV Functions

The Excel Stdeva function is very similar to the Excel Stdev function, in that both functions calculate the sample standard deviation of a supplied set of values. 

The difference between these two functions applies when an array of values, containing text or logical values is supplied to the function. In this case, the Stdev function ignores the text and logical values, whereas the Stdeva function assigns the value 0 to text and the values 1 or 0 to logical values. 

The rules for this are shown in the table below, compared to the rules for the Stdev function. 

Stdeva Function Example

The following spreadsheet shows the Excel Stdeva function used to calculate the standard deviation of the set of values in cells A1-A4 and in cells A1-A6. 

The format of the functions are shown in the spreadsheet on the left and the resulting values are shown in the spreadsheet on the right. 

Note that, in the spreadsheet above, the function in cell B2 includes the values in cells A5 and A6. In this case, the logical value TRUE in cell A5 is treated as the value 1 and the text in cell A6 is treated as the value 0. This is shown by the example in cell B3, in which cells A1 - A4 and the values 1 and 0 give the same result as the standard deviation calculation for cells A1 - A6. 

Stdevpa Function Error

If you get an error from the Excel Stdeva function this is most likely to be the #VALUE! error: 

Common Errors 

Excel - Microsoft Excel STDEV Function

Description

The Excel STDEV function calculates the sample standard deviation of a supplied set of values. 

The format of the function is : 

STDEV( number1, [number2], ... ) 

where the arguments, number1, [number2], etc, are one or more numerical values or references to cells containing numbers. 

If you are using Excel 2007 or Excel 2010, you can enter up to 255 number arguments to the function. However, in Excel 2003, the function can only accept up to 30 number arguments. 

Note that the Stdev function is used when calculating the standard deviation for a sample of a population (eg. if your data set records the individual heights of a sample of UK males). If you are calculating the standard deviation of an entire population, you need to use the Stdevp function. 

Note also, that the Stdev function ignores text values and logical values if these are supplied as part of an array. If you want a function that includes text and logical values in the calculation, consider using the Stdeva function. 

Imagine you wanted to find out the standard deviation of the heights of adult males in London. It is not realistic to measure the height of all males, but you could take a sample of the population and measure their heights. 

The example spreadsheet on the right stores the measurements (in cm) of 3,000 adult males. The measured heights are stored in cells B3 - B1002, D3 - D1002 and F3 - F1002. 

The standard deviation of the heights of the sample group is calculated in cell H3 of the spreadsheet. The formula for this, as shown in the formula bar, is : 

=STDEV( B3:B1002, D3:D1002, F3:F1002 ) 

As shown in cell H3, the standard deviation in the individual heights of the sample group is 5.4 cm. 

Other Argument Types

In the example above, the arguments to the Stdev function are input as 3 cell ranges. However, you can also input figures directly, as individual numbers or number arrays. 

For example, if you wanted to include two further heights, of 176cm and 177cm into the sample, you could add these directly into the above function as follows: 

Either as individual numbers: 

=STDEV( B3:B1002, D3:D1002, F3:F1002, 176, 177 ) 

Or, as an array of numbers: 

=STDEV( B3:B1002, D3:D1002, F3:F1002, {176,177} ) 

Stdev Function Errors

If you get an error from the Excel Stdev function this is likely to be one of the following: 

Common Errors

Microsoft - Excel SMALL Function

Description

The Excel SMALL function returns the k'th smallest value from an array or a range of cells containing numerical values. 

The format of the function is : 

SMALL( array, k ) 

where the function arguments are: 

The array argument can be supplied to the function either directly, or as a reference to a range of cells containing numeric values. If values in the supplied range of cells are text values, these values are ignored. 

Small Function Examples

The following example shows the Excel Small function, being used to retrieve the 1st, 2nd, 3rd, 4th and 5th smallest values from the set of values in cells A1 - A5. 

The format of the functions are shown in the spreadsheet on the left and the resulting values are shown in the spreadsheet on the right. 

Note that, any calls to the Small function, in which the index, k is set to 1, gives the same result as the Min function. 

In the example above, the functions in cells B1 and B5 could be replaced by the Min function and the Max function, respectively. 

Small Function Error

If you get an error from the Excel Small Function, this is likely to be the #NUM! error: 

Common Error 

Also, the following problem is encountered by some users: 

Common Problem 

The Small function returns the wrong value, or returns the #NUM! error, even though you believe your value of k is between 1 and the number of values in the supplied array. 

Possible Reason 

Text values, including text representations of numbers within the supplied array, are ignored by the Small function. Therefore, this problem may arise if the values in the supplied array are text representations of numbers, instead of actual values (read more about Excel data types on the Excel Formatting page) 

Solution 

Note that the Text to Columns only converts values, it will not change the data type of a cell containing a function

Courses Microsoft Project Advanced Training Course in London

Audience: This Project advanced course is aimed at Project users wishing to further their project skills. Some experience in working with Microsoft Project and creating a basic project plan is required.

Course Duration: 1 day 

Course Types: Group Bookings, Public Scheduled Courses, 1-2-1 Sessions, Bespoke Tailored Courses and Saturday Club.

Course Costs:

Public Courses : £125 +VAT      Next Scheduled Course 28th April 2017
Group Rate: £ 695 + VAT
1-2-1 Rate £ 450 + VAT

MOS Specialist / Expert Exam Costs ( optional )

New Prices from 19th February 2018

All Vouchers now include a Retake Voucher

Option 1 - Voucher & Retake

Option 2 - Voucher, Retake and Gmetrix ( Test Prep )

Option 3 - Exam Pack includes - Voucher, Retake, Gmetrix and Exam study manual

Discount 4 for 3 on MOS Exam Packs 

View Scheduled Public Courses

Course Location: This Microsoft Project advanced course  is delivered at our training centres our London Venues; training can also be delivered at your offices. 

Tailored course content can be customised to meet your specific requirements, with scheduled dates to suit you.

At Course Completion

On completing this course, students will be able to:

• Identify and resolve over-allocations
• Creating a Project Baseline
• Understand the uses of Resource Pools
• Create Multiple Projects
• Track a Project using the Tracking Gantt view
• Create & Modify Tables
• Creating calculated fields
• Applying project filters
• Displaying Reports
• Templates
• Team Planner
• Office online & 365 additional functionality

Detailed Course Outline

Project 2007 Advanced Training Course Outline

Project 2010 Advanced Training Course Outline

Project 2013 Advanced Training Course Outline

Project 2016 Advanced Training Course Outline

Charis Alexandra Training Course Index

Contact Email 

For Further Course Details Visit Our Homepage

Microsoft Accredited IT Training Provider

Microsoft Certified Training Materials

IT Training Courses delivered by Microsoft Accredited Facilitators

Microsoft MOS Test Centre London

Courses Microsoft Project Introduction Training Courses in London

Audience: This Project introduction course is for those who are a new user, or if you have some knowledge of Project and wish to understand of how to get the best out of the package, relative to real projects.

Course Duration: 1 day 

Course Types: Group Bookings, Public Scheduled Courses, 1-2-1 Sessions, Bespoke Tailored Courses and Saturday Club.

Course Costs:

Public Courses : £125 +VAT      Next Scheduled Course 28th November 2017
Group Rate: £ 695 + VAT
1-2-1 Rate £ 450 + VAT

MOS Specialist / Expert Exam Costs ( optional )

New Prices from 19th February 2018

All Vouchers now include a Retake Voucher

Option 1 - Voucher & Retake

Option 2 - Voucher, Retake and Gmetrix ( Test Prep )

Option 3 - Exam Pack includes - Voucher, Retake, Gmetrix and Exam study manual

Discount 4 for 3 on MOS Exam Packs 

View Scheduled Public Courses

Course Location: This Microsoft Project introduction course is delivered at our training centres our London Venues; training can also be delivered at your offices. 

Tailored course content can be customised to meet your specific requirements, with scheduled dates to suit you.

At Course Completion

After completing this course, students will be able to:

• Understanding Project Terminology
• Opening and closing Project plans
• Tell ME
• Modifying the Project Calendar
• Creating Project Plans
• Entering Tasks
• User Controlled Scheduling 
• Task Durations
• Linking Tasks
• Task Inspector
• Understanding resource over-allocations
• Setting the Project Baseline
• The Tracking Toolbar
• Tracking

Detailed Course Outline


Project 2007 Introduction Training Course Outline

Project 2010 Introduction Training Course Outline

Project 2013 Introduction Training Course Outline

Project 2016 Introduction Training Course Outline

Charis Alexandra Training Course Index

Contact Email 

For Further Course Details Visit Our Homepage

Microsoft Accredited IT Training Provider

Microsoft Certified Training Materials

IT Training Courses delivered by Microsoft Accredited Facilitators

Microsoft MOS Test Centre London