MS Excel Learning Classes – 7

Most Important Formulas in MS-Excel

SUM

Number1, number2, …    are 1 to 30 arguments for which you want the total value or sum.Adds all the numbers in a range of cells.     Syntax               SUM(number1,number2, …)

Example

A
Data
-5
15
30
‘5
TRUE
 

Formula

Description (Result)
=SUM(3, 2) Adds 3 and 2 (5)
=SUM(“5”, 15, TRUE) Adds 5, 15 and 1, because the text values are translated into numbers, and the logical value TRUE is translated into the number 1 (21)
=SUM(A2:A4) Adds the first three numbers in the column above (40)
=SUM(A2:A4, 15) Adds the first three numbers in the column above, and 15 (55)
=SUM(A5,A6, 2) Adds the values in the last two rows above, and 2. Because nonnumeric values in references are not translated, the values in the column above are ignored (2)

 

AVERAGE

Returns the average (arithmetic mean) of the arguments.

Syntax  AVERAGE(number1,number2,…)

Number1, number2, …    are 1 to 30 numeric arguments for which you want the average.

Example

1
2
3
4
5
6
 
 
 
A
Data
10
7
9
27
2

 

Formula Description (Result)
=AVERAGE(A2:A6) Average of the numbers above (11)
=AVERAGE(A2:A6, 5) Average of the numbers above and 5 (10)

MEDIAN

Syntax      MEDIAN(number1,number2,…)Returns the median of the given numbers. The median is the number in the middle of a set of numbers; that is, half the numbers have values that are greater than the median, and half have values that are less.

Number1, number2, …    are 1 to 30 numbers for which you want the median.

Example

1
2
3
4
5
6
7
 
 
 
A
Data
1
2
3
4
5
6
Formula Description (Result)
=MEDIAN(A2:A6) Median of the first 5 numbers in the list above (3)
=MEDIAN(A2:A7) Median of all the numbers above, or the average of 3 and 4 (3.5)

MODE

Syntax                       MODE(number1,number2,…)Returns the most frequently occurring, or repetitive, value in an array or range of data. Like MEDIAN, MODE is a location measure.

Number1, number2, …    are 1 to 30 arguments for which you want to calculate the mode. You can also use a single array or a reference to an array instead of arguments separated by commas.

Example

1
2
3
4
5
6
7
 
 
A
Data
5.6
4
4
3
2
4
Formula Description (Result)
=MODE(A2:A7)                      Mode, or most frequently occurring number above (4)


COUNT


Syntax                       COUNT
(value1,value2,…)Counts the number of cells that contain numbers and also numbers within the list of arguments. Use COUNT to get the number of entries in a number field that’s in a range or array of numbers.

Value1, value2, …    are 1 to 30 arguments that can contain or refer to a variety of different types of data, but only numbers are counted.

Example

1
2
3
4
5
6
7
8
 
 
A
Data
Sales
12/8/2008
19
22.24
TRUE
#DIV/0!

 

 

Formula Description (Result)
=COUNT(A2:A8) Counts the number of cells that contain numbers in the list above (3)
=COUNT(A5:A8) Counts the number of cells that contain numbers in the last 4 rows of the list (2)
=COUNT(A2:A8,2) Counts the number of cells that contain numbers in the list, and the value 2 (4)

 

Spread the love
About admin 2013 Articles
Please feel free to contact us or send your comments. [email protected]

Be the first to comment

Leave a Reply

Your email address will not be published.