|| Links | Feedback | Array functions | SUMPRODUCT() | Filtering data ||
These are more complex versions of the basic functions SUM, AVERAGE and COUNT. They are part of a series known as 'database functions' and they are used to interrogate lists of data held on a worksheet. For a selected table of data these functions will perform their processing only on records (or rows) which meet specified criteria. All other records are ignored.
|1||Ages of Patients seen by Doctors|
|4||Dr Smith||JAW||42||Dr Smith||>40|
|11||The average age of patients seen by Dr Smith||42.67|
|12||The number of Dr Smiths patients aged over 40:||2.00|
In this example the table is contained in the range A3:C9. Column headings must be placed directly above the data and these headings included within the range of the table in the formula.
Two different criteria have been placed in the range E3:F4. Each criteria refers to a column title and underneath it the required match. Note that the second criteria specifies patients whose Age is greater than (>) 40.
The criteria range can be expanded to include more items. If you want to include two alternatives for one variable (e.g. Dr Smith OR Dr Lam) they can be placed one below the other (in E4 and E5). In this situation, make sure that you also specify criteria for other fields against each row. If you leave the 'Age' criteria blank against a second 'Doctor', the result would be for Dr Smith's patients aged over 40 OR Dr Lam's patients for any age.
|[E11] = DAVERAGE(A3:C9, 3, E3:E4)||42.67|
|[E12] = DCOUNT(A3:C9,"Age", E3:F4)||2|
These functions all require three arguments (each separated by commas).
|14||Length of theatre procedures|
|24||Time spent in theatres by Dr Smith:||92.00||mins|
DSUM() was not included within the first example because there is no benefit in adding up patients ages. In a table containing details of time spent in operating theatres, DSUM() can be used (using the formula [E24]=DSUM(A16:C22, 3, E3:E4)) to determine the total operating time for a particular Doctor.
Click to see examples of several functions which can apply conditions to SUM calculations.
|Get this information as a document
accompanied by Excel worksheets
|Click here for details about
file. It has been rewritten for Excel 2010.
|file: xldsumdavg.htm||© meadinkent.co.uk 2015||Last updated Feb14||CMIDX S2 P1 Y|