MeadInKent
| Links | Feedback | Weighted Averages | Statistics |

Excel Functions: Sum, Average, Count

8 Hover with mouse to view formulas in this example
- A B C
1 Patients seen by Doctors    
2      
3   This Year Last Year
4 Dr Smith 120 110
5 Dr Lam 80 70
6 Mrs Dapper 50  
7 Prof. Plum 102 105
8      
9 Total patients 352 285
10 Average number seen 88 95
11 Number of values 4 3

A function is a special key word which can be entered into a cell in order to perform a process to some data which is appended within brackets. = FunctionName(Data) The data (or argument in proper terminology) often includes a range of cells. Excel automatically recognises the names of these functions provided that they are preceded by an equals sign and finish with brackets.

There is a function button [Image] on the 'Formulas' ribbon which offers assistance and useful prompts when entering a function into a spreadsheet cell. Alternatively you can type the function directly into the formula bar.

SUM(), AVERAGE() and COUNT() are common functions and relatively easy to understand. They each apply to a range of cells containing numbers (or blank but not text) and return either the arithmetic total of the numbers, the average mean value or the quantity of values in the range.

In the diagram above the three functions used are as follows:

[B9] = SUM(B4:B7) 352
[B10] = AVERAGE(B4:B7) 88
[B11] = COUNT(B4:B7) 4

Cell C6 is currently empty. Both AVERAGE() and COUNT() would be affected by placing a zero value in the cell. Doing so would change last years values to 71 and 4 respectively. SUM() is unaffected.

The range need not be limited to a single column. For example the function = COUNT(B4:C7) would return 7.

These three functions only take numeric values into account. They ignore text and blank spaces which may be contained within the range. It is possible to use a variant of the COUNT function called COUNTA() which returns the number of cells in a range containing any numeric or text value. For example the function =COUNTA(A4:B9) would return 10.

excel Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: xlsumavgct.htm © meadinkent.co.uk 2017 Page last updated Nov17 CMIDX S1 P1 Y