Objective
Excel offers a number of conditional worksheet functions (see Excel Conditional Functions) such as COUNTIF, SUMIF, and AVERAGEIF which work on data on Excel spreadsheets. Real Statistics provides some extensions to these functions. In particular the Real Statistics versions work not only on cell ranges but any arrays.
COUNT_IF
Worksheet Function
The Real Statistics Resource Pack provides the following worksheet functions where R1 is a column array or range.
COUNT_IF(R1, s) = # of times s appears in R1
When R1 is a range, =COUNT_IF(R1, s) is equivalent to the standard Excel formula =COUNTIF(R1, s). Excel’s COUNTIF function requires that R1 be a range, whereas COUNT_IF does not.
Examples
Figure 1 – COUNT_IF Examples
Note that the last formula in Figure 1 doesn’t reference a range. The equivalent standard Excel formula =COUNTIF(A2:A8 & “s”, “pears”) is therefore invalid.
SUM_IF, AVG_IF
Worksheet Functions
The Real Statistics Resource Pack provides the following worksheet functions where R1 is an m × 1 column array or range, and R2 is an m × d array or range containing numerical data. For SUM_IF, d is restricted to 1.
SUM_IF(R1, s, R2) = the sum of all values in R2 whose corresponding row in R1 contains s.
AVG_IF(R1, s, R2): returns a 1 × d row array (or numerical element if d = 1), whose jth element is equal to the average of all values in the jth column of R2 whose corresponding row in R1 contains s.
Examples
Here are some examples where d = 1.
Figure 2 – SUM_IF and AVG_IF Examples
In Figure 3, the means for both columns are displayed: 6.75 (cell Q2) for column 1 and 2 (cell R2) for column 2.
Figure 3 – Multi-column AVG_IF Example
COV_IF
Worksheet Function
The Real Statistics Resource Pack provides the following worksheet functions where R1 is an m × 1 column array or range, and R2 is an m × d array or range containing numerical data.
COV_IF(R1, s, R2): returns a d × d row array with the sample covariance matrix for the data in the matrix consisting of the rows in R2 where the corresponding element in R1 is s.
Note that if d = 1, then the output from =COV_IF(R1, s, R2) is the variance of the data in the matrix consisting of the elements in R2 whose corresponding element in R1 is s.
Examples
An example where d = 2 is shown in rang F2:G3 of Figure 4. The equivalent result using the COV function is shown in range L2:M3. An example with d = 1 is shown in cell F7, with an equivalent result using VAR.S is shown in cell L7.
Figure 4 – COV_IF Examples
COUNTS_IF, SUMS_IF, AVGS_IF
Worksheet Functions
The Real Statistics Resource Pack provides the following worksheet functions where R1 is an m × d+1 column array or range, and R2 is an m × d+1 numerical array or range. For COUNTS_IF, d is restricted to 1.
COUNTS_IF(R1): returns an k × 2 array where the first column consists of all the unique values in R1 in sorted order and the second column consists of the values =COUNT_IF(R1, s) where s is the corresponding value in the first column
SUMS_IF(R1, R2): returns an k × d+1 array where the first column consists of all the unique values in R1 in sorted order and the next d columns consist of the values =SUM_IF(R1, s, R2j) where s is the corresponding value in the first column and R2j is the jth column in R2.
AVGS_IF(R1, R2): returns an k × d+1 array where the first column consists of all the unique values in R1 in sorted order and the next d columns consist of the values =AVG_IF(R1, s, R2) where s is the corresponding value in the first column.
Restriction: In the current release (Rel 9.3), R1 can only contain numeric data. This restriction will be relaxed in future releases.
Examples
Figure 5 – Examples
Examples Workbook
Click here to download the Excel workbook with the examples described on this webpage.
Reference
Microsoft (2021) Excel functions
https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188#bm19