Excel OR Function

Basic Description

The Excel OR function tests a number of supplied conditions and returns either:

-	TRUE if ANY of the conditions evaluate to TRUE
or
-	FALSE otherwise (i.e. if ALL of the conditions evaluate to FALSE)

The syntax of the function is :

OR( logical_test1, [logical_test2], ... )

where the logical_test arguments are one or more conditions that evaluate to either TRUE or FALSE.
You can enter up to 255 conditions to the Excel Or function in Excel 2007 or 2010. However, in Excel 2003, the function can only handle up to 30 arguments.


Note that, if the logical_tests return numbers, instead of logical values, zero is treated as the logical value FALSE all non-zero numbers are treated as the logical value TRUE.

Excel OR Function Examples

The following spreadsheet shows three examples of the Excel Or function.
The spreadsheet on the left shows the formulas and the spreadsheet on the right shows the results.

Formulas: A B C 1 5 10 =OR( A1>0, A1<B1 ) 2 5 10 =OR( A2>0, A2>B2, B2>12 ) 3 5 10 =OR( A3<0, A3>B3, B3>12 )

Results: A B C 1 5 10 TRUE 2 5 10 TRUE 3 5 10 FALSE

Note that, in the above examples:

• the function in cell C1 evaluates to TRUE, as BOTH of the supplied conditions are TRUE
• the function in cell C2 evaluates to TRUE, as the first condition, A2>0, is FALSE
• the function in cell C3 evaluates to FALSE, as ALL of the supplied conditions are FALSE

OR Function Errors

The most common error from the Excel Or Function is the #VALUE! error:

Common Error

#VALUE!

-

Occurs if any of the supplied logical_test arguments are text values

Excel AND Function

Basic Description

 

The Excel AND function tests a number of user-defined conditions and returns a result of:

-	TRUE if ALL of the conditions evaluate to TRUE
or
-	FALSE otherwise (i.e. if ANY of the conditions evaluate to FALSE)

The syntax of the function is :

AND( logical_test1, [logical_test2], ... )

where the arguments, logical_test1, [logical_test2], etc, are conditions that evaluate to either TRUE or FALSE.
In Excel 2007 or 2010, you can enter up to 255 logical_test arguments to the Excel And function. However, in Excel 2003, the function can only handle up to 30 arguments.


Note that, if the logical_tests evaluate to numbers, instead of logical values, the value zero evaluates to FALSE and all non-zero values are treated as the logical value TRUE.

Excel And Function Examples

The following spreadsheet shows three examples of the Excel And function, with different conditions.

Formulas:   A B C 1 5 10 =AND( A1>0, A1<B1 ) 2 5 10 =AND( A2>0, A2<B2, B2>12 ) 3 5 10 =AND( A3<0, A3>B3, B3>12 )

Results:   A B C 1 5 10 TRUE 2 5 10 FALSE 3 5 10 FALSE

Note that, in the above example spreadsheet:

• the function in cell C1 evaluates to TRUE, as BOTH of the supplied conditions are TRUE
• the function in cell C2 evaluates to FALSE, as the third condition, B2>12, is FALSE
• the function in cell C3 evaluates to FALSE, as ALL of the supplied conditions are FALSE

And Function Common Error

If you get an error from the Excel And Function, this is likely to be the #VALUE! error:

Common Error

#VALUE!

-

Occurs if any of the supplied logical_test arguments are text values

Excel DVARP Function

Microsoft Excel DVARP Function

Basic Description

The Excel Dvarp function calculates the variance (for an entire population), of the values in a field (column) in a database for selected records only. The records to be included in the calculation are defined by a set of one or more user-specified criteria.

The syntax of the function is :

DVARP( database, field, criteria )

where the arguments are shown in the table below:

database	-	A range of cells containing the database. The top row of the database should specify the field names.
field	-	The field (column) within the database, for which the variance is to be calculated. This can either be a field number, or it can be the field name (ie. the header in the top row of the database) encased in quotes (eg. "Subject", "Name", etc)
criteria	-	A range of cells that contain the criteria, to specify which records should be included in the calculation. The range can include one or more criteria, which are presented as a field name in one cell and the condition for that field in the cell below. eg. Age Name >8 Amy

Wildcards

You can also use the following wildcards in text-related criteria:

?    -    matches any single character

*    -    matches any sequence of characters

if you do actually want to find the ? or * character, type the ~ symbol before this character in your search.

eg. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".

The criteria supplied beneath each field heading can be either:

	-	a numeric value (including an integer, decimal, date, time, or logical value) (eg. 10, 01/01/2011, FALSE)
or
	-	a text string (eg. "Text", "Monday")
or
	-	an expression (eg. ">8", "<>0")

Note that the Excel database functions are not case sensitive. So, for example, the criteria ="Name" will be satisfied by cells containing the text "Name" or "name".

Excel Dvar Function Examples

	A	B	C	D	E
1	Name	Gender	Age	Subject	Score
2	Amy	Female	10	Math	63%
3	Amy	Female	10	English	78%
4	Amy	Female	10	Science	39%
5	Bill	Male	8	Math	55%
6	Bill	Male	8	English	71%
7	Bill	Male	8	Science	51%
8	Sam	Male	9	Math	39%
9	Sam	Male	9	English	52%
10	Sam	Male	9	Science	48%
11	Tom	Male	9	Math	78%
12	Tom	Male	9	English	69%
13	Tom	Male	9	Science	65%

The following examples are based on the simple database on the right, which stores the examination marks scored by four children in three different subjects.

Example 1

In the example below, the Dvarp function is used to find the variance of scores obtained in Math, by male students. The criteria are specified in cells G1 - H2 and the Dvar formula is shown in cell G3.

	G	H
1	Subject	Gender
2	Math	Male
3	=DVARP( A1:E13, "Score", G1:H2 )

The above Dvarp function calculates the variance of the values in cells E5, E8 & E11, and therefore returns the value 2.6%.

Example 2

In the example below, the Dvarp function is used to find the variance for the Science scores obtained by students over 8 years of age.

	G	H
1	Subject	Age
2	Science	>8
3	=DVARP( A1:E13, "Score", G1:H2 )

The above Dvarp function finds the variance of the values in cells E4, E10 & E13 and so returns the value 1.2%.

Note that, in the above two examples, instead of typing in "Score" for the field argument, we could have simply used the number 5 (to denote the 5th column of the database).

 

Excel DVAR Function

Microsoft Excel DVAR Function

Basic Description

The Excel Dvar function calculates the sample variance of a field (column) in a database for selected records only. The records to be included in the calculation are defined by a set of one or more user-specified criteria.

The syntax of the function is :

DVAR( database, field, criteria )

where the arguments are shown in the table below:

database	-	A range of cells containing the database. The top row of the database should specify the field names.
field	-	The field (column) within the database, for which the sample variance is to be calculated. This can either be a field number, or it can be the field name (ie. the header in the top row of the database) encased in quotes (eg. "Subject", "Name", etc)
criteria	-	A range of cells that contain the criteria, to specify which records should be included in the calculation. The range can include one or more criteria, which are presented as a field name in one cell and the condition for that field in the cell below. eg. Age Name >8 Amy

Wildcards

You can also use the following wildcards in text-related criteria:

?    -    matches any single character

*    -    matches any sequence of characters

if you do actually want to find the ? or * character, type the ~ symbol before this character in your search.

eg. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".

The criteria supplied beneath each field heading can be either:

	-	a numeric value (including an integer, decimal, date, time, or logical value) (eg. 10, 01/01/2011, FALSE)
or
	-	a text string (eg. "Name", "Monday")
or
	-	an expression (eg. ">8", "<>0")

Note that the Excel database functions are not case sensitive. So, for example, the criteria ="Name" will be satisfied by cells containing the text "Name" or "name".

Excel Dvar Function Examples

	A	B	C	D	E
1	Name	Gender	Age	Subject	Score
2	Amy	Female	10	Math	63%
3	Amy	Female	10	English	78%
4	Amy	Female	10	Science	39%
5	Bill	Male	8	Math	55%
6	Bill	Male	8	English	71%
7	Bill	Male	8	Science	51%
8	Sam	Male	9	Math	39%
9	Sam	Male	9	English	52%
10	Sam	Male	9	Science	48%
11	Tom	Male	9	Math	78%
12	Tom	Male	9	English	69%
13	Tom	Male	9	Science	65%

The following examples are based on the simple database on the right, which stores the examination marks scored by four children in three different subjects.

Example 1

In the example below, the Dvar function is used to find the sample variance of scores obtained in Science, by male children. The criteria are specified in cells G1 - H2 and the Dvar formula is shown in cell G3.

	G	H
1	Subject	Gender
2	Math	Male
3	=DVAR( A1:E13, "Score", G1:H2 )

The above Dvar function calculates the sample variance of the values in cells E5, E8 & E11, and therefore returns the value 3.8%.

Example 2

In the example below, the Dvar function is used to find the sample variance for the Science scores obtained by students over 8 years of age.

	G	H
1	Subject	Age
2	Science	>8
3	=DVAR( A1:E13, "Score", G1:H2 )

The above Dvar function finds the sample variance of the values in cells E4, E10 & E13 and so returns the value 1.7%.

Note that, in the above two examples, instead of typing in "Score" for the field argument, we could have simply used the number 5 (to denote the 5th column of the database).