• Home
• Income Tax
• PPF
• Forms
• Widgets
• Tax Planning
• Forex
• Learn PC
• Miscellaneous
• Blog

Excel Tutorial

Excel Average, Count and Round functions

Calculating the average of a group of numbers

Calculate the average of numbers in a contiguous row or column

1. Click the cell where you want to display the average of the numbers.
2. Highlight the range cells containing numbers for which you want to find out the average.
3. Click the arrow next to AutoSum on the Standard toolbar, and then click Average and press ENTER.

Calculating the average of numbers not in a contiguous row or column

To do this task, use the AVERAGE function as shown in the examples below :

1. To find out Averages of all numbers in cells from D1 to D6, the formula will be
   =AVERAGE(D1:D6)
2. To find out Averages the first three and the last number in the row, the formula will be
   =AVERAGE(D1:D3,D6) =AVERAGE(IF(D1:D6<>0, D1:D6,""))
3. To find out Averages the numbers in the list except those that contain zero, such as cell A5 (11.4), the formula will be
   =AVERAGE(IF(D1:D6<>0, D1:D6,""))
   Please note that this formula needs to be entered as an Array formula by pressing Ctrl + Shift + Enter keys.

Example 1

Example 2

Example 3

Calculating weighted average

To do this task, use the SUMPRODUCT and SUM functions as shown in the examplein the image :

Calculating Weighted Average

The function SUMPRODUCT multiplies all the components of the two arrays and then adds the products> The SUMPRODUCT in above example is 5*80 + 40*20 + 30*2 + 5*300 = 2760.

Rounding Numbers

Rounding numbers on a worksheet

1. Select the cells that you want to format.
2. To display more or fewer digits after the decimal point, click Increase Decimal or Decrease Decimal on the Formatting toolbar.

Rounding numbers in a built-in number format

1. On the Format menu, click Cells, and then click the Number tab.
2. In the Category list, click Currency, Accounting, Percentage, or Scientific.
3. In the Decimal places box, enter the number of decimal places that you want to display.

Round a number up

To do this task, use the ROUNDUP, EVEN, or ODD functions as shown in the examples below:

If the number entered in cell A2 is 20.346

Round a number down

To do this task, use the ROUNDDOWN function as shown in the examples below:

If the number entered in cell A2 is 20.346

Count Numbers

Count cells that contain numbers in a contiguous row or column

1. Click the cell where you want to display the Count of the numbers.
2. Highlight the range cells containing numbers for which you want to find out the average.
3. Click the arrow next to the AutoSum on the Standard toolbar , and then click Count and press ENTER.

Count cells that contain numbers that are not in a contiguous row or column

To do this task, use the COUNT function. The formula
=COUNT(A2:A10,A12)
Counts number of cells in range A2:A10 and Cell A12 that contain numbers. Please note that if text is entered in any cell, it will not be counted. However,a date is a number and it will be counted.

Find Smallest or Largest number in a Range

1. Select a cell below or to the right of the numbers for which you want to find the smallest number.
2. Click the arrow next to AutoSum , and then click Min (calculates the smallest), or Max (calculates the largest), and then press ENTER.

If the cells are not in a contiguous row or column

To do this task, use the MIN, MAX, SMALL, or LARGE functions. Please look at the use of formulas in the examples given below: