|
![]() |
![]() |
For more information about criteria ranges, see "Criteria Ranges" . DAVE, or DAVERAGE Takes the average of numeric records that match criteria. DAVE(database, offset, crit) database the range address of the database offset the offset of the column to be averaged crit the criteria range This function takes a range and averages all the numbers in the column at the offset value from the first column of the range that meet the criteria defined in another column. This is a sample database: A B C 1 NAME GRADE GPA GRADE 2 Joe B 3.2 B 3 Sue A 4.7 4 Robert C 2.7 5 Steve A 4.5 6 Ann B 4.2 The database range is A1:C6. The criteria range is D1:D2. To calculate the average GPA of everyone who earned a B use this formula: =DAVE(A1:C6,2,D1:D2) -> 3.7 @DAVE or @DAVG Takes the average of numeric records that match criteria. Strings have a value of zero. @DAVE(database, offset, crit) database the range address of the database offset the offset of the column to be averaged crit the criteria range This function takes a range and averages all the numbers in the column at the offset value from the first column of the range that meet the criteria defined in another column. This function is basically the same as DAVE() or DAVERAGE() except that those function ignore string values. This is a sample database: A B C 1 NAME GRADE GPA GRADE 2 Joe B 3.2 B 3 Sue A 4.7 4 Robert C 2.7 5 Steve A 4.5 6 Ann B 4.2 The database range is A1:C6. The criteria range is D1:D2. To calculate the average GPA of everyone who earned a B use this formula: @DAVE(A1:C6,2,D1:D2) -> 3.7 DCOUNT or @DCOUNT Counts all the numeric items matching criteria in the column offset from the upper left corner of the database range. DCOUNT(database, offset, crit) database the range address of the database offset the offset of the relevant column crit the criteria range This function takes a range and counts all the numbers in the column at the offset value from the first column of the range that meet the criteria defined in another column. This is a sample database: A B C D 1 NAME GRADE GPA GRADE 2 Joe B 3.2 B 3 Sue A 4.7 4 Robert C 2.7 5 Steve A 4.5 6 Ann B 4.2 The database range is A1:C6. The criteria range is D1:D2. To count the number of students who earned B's use this formula: =DCOUNT(A1:C6,1,D1:D2) -> 2 DMAX Returns the maximum numeric value of the records in database that match criteria. DMAX(database, offset, crit) database the range address of the database offset the offset of the relevant column crit the criteria range This function takes a range and finds the maximum number of the numbers in the column at the offset value from the first column of the range that meet the criteria defined in another column. This is a sample database: A B C D 1 NAME SALES REGION REGION 2 Joe 3000 East East 3 Sue 4500 North 4 Robert 2800 East 5 Steve 3700 South 6 Ann 2400 West The database range is A1:C6. The criteria range is D1:D2. To find the largest sales figure in the Eastern region use this formula: =DMAX(A1:C6,1,D1:D2) = 3000 @DMAX Returns the maximum value of the records in database that match criteria. Strings have a value of zero. @DMAX(database, offset, crit) database the range address of the database offset the offset of the relevant column crit the criteria range This function takes a range and finds the highest number of the numbers in the column at the offset value from the first column of the range that meet the criteria defined in another column. Strings are assumed to have a value of zero. This function is basically the same as DMAX except that those function ignore string values This is a sample database: A B C D 1 NAME SALES REGION REGION 2 Joe 3000 East East 3 Sue 4500 North 4 Robert 2800 East 5 Steve 3700 South 6 Ann 2400 West The database range is A1:C6. The criteria range is D1:D2. To find the largest sales figure in the Eastern region use this formula: @DMAX(A1:C6,1,D1:D2) -> 3000 DMAX() and @DMAX differ in that DMAX ignores string values and @DMAX treats strings as having a value of zero. DMIN Returns the smallest numeric item in the records that match the criteria. DMIN(database, offset, crit) database the range address of the database offset the offset of the relevant column crit the criteria range This function takes a range and finds the minimum number of the numbers in the column at the offset value from the first column of the range that meet the criteria defined in another column. This is a sample database: A B C D 1 NAME SALES REGION REGION 2 Joe 3000 East East 3 Sue 4500 North 4 Robert 2800 East 5 Steve 3700 South 6 Ann 2400 West The database range is A1:C6. The criteria range is D1:D2. To find the smallest sales figure in the Eastern region use this formula: =DMIN(A1:C6,1,D1:D2) = 2800 @DMIN Returns the smallest numeric item in the records that match the criteria. String values will count as zeros. @DMIN(database, offset, crit) database the range address of the database offset the offset of the relevant column crit the criteria range This function takes a range and finds the minimum number of the numbers in the column at the offset value from the first column of the range that meet the criteria defined in another column. Strings are assumed to have a value of zero. This function is basically the same as DMIN except that those function ignore string values This is a sample database: A B C D 1 NAME SALES REGION REGION 2 Joe 3000 East East 3 Sue 4500 North 4 Robert 2800 East 5 Steve 3700 South 6 Ann 2400 West The database range is A1:C6. The criteria range is D1:D2. To find the smallest sales figure in the Eastern region use this formula: @DMIN(A1:C6,1,D1:D2) -> 2800 DMIN() and @DMIN() differ in that DMIN() ignores strings, while @DMIN() treats strings as having a value of zero. DPROD Returns the product of all the items matching the criteria in the column offset from the upper left corner of the range. Strings are ignored. DPROD(database, offset, crit) database the range address of the database offset the offset of the relevant column crit the criteria range This function takes a range and multiplies all the numbers in the column at the offset value from the first column of the range that meet the criteria defined in another column. This is a sample database: A B C D 1 GATE PATH % PASSING PATH 2 1 North 0.96 East 3 2 South 0.95 4 3 West 0.97 5 4 East 0.95 6 5 East 0.93 7 6 North 0.88 8 7 West 0.92 9 8 East 0.99 10 9 South 0.94 The database range is A1:C10. The criteria range is D1:D2. To find the product of the percentages for the East path use this formula: =DPROD(A1:C10, 2, D1:D2) -> 0.874665 @DPROD Returns the product of all the items matching the criteria in the column offset from the upper left corner of the range. Strings are treated as zero values. @DPROD(database, offset, crit) database the range address of the database offset the offset of the relevant column crit the criteria range This function takes a range and multiplies all the numbers in the column at the offset value from the first column of the range that meet the criteria defined in another column. Strings are treated as having a value of zero. This function is basically the same as DPROD except that those function ignore string values This is a sample database: A B C D 1 GATE PATH % PASSING PATH 2 1 North 0.96 East 3 2 South 0.95 4 3 West 0.97 5 4 East 0.95 6 5 East 0.93 7 6 North 0.88 8 7 West 0.92 9 8 East 0.99 10 9 South 0.94 The database range is A1:C10. The criteria range is D1:D2. To find the product of the percentages for the East path use this formula: @DPROD(A1:C10, 2, D1:D2) -> 0.874665 DPROD() and @DPROD differ in that DPROD() ignores string values, but @DPROD() treats strings as having a value of zero. DSTDDEV or DSTDEV Returns the standard deviation of the numeric records that match the criteria. DSTDEV(database, offset, crit) database the range address of the database offset the offset of the relevant column crit the criteria range This function takes a range and finds the standard deviation of the numbers in the column at the offset value from the first column of the range that meet the criteria defined in another column. This is a sample database: A B C D 1 STATE REGION AVG RF REGION 2 AL South 3.7 West 3 CA West 2.1 4 NJ East 5.2 5 FL South 4.9 6 LA South 6.8 7 TX West 5.0 8 MA North 7.6 9 AZ West 2.2 10 NV West 2.4 The database range is A1:C10. The criteria range is D1:D2. To find the standard deviation of rainfall in the Western region use this formula: =DSTDEV(A1:C10,2,D1:D2) -> 1.2029 @DSTD Returns the standard deviation of the records that match the criteria. Strings have a value of zero. @DSTD(database, offset, crit) database the range address of the database offset the offset of the relevant column crit the criteria range This function takes a range and finds the standard deviation of the numbers in the column at the offset value from the first column of the range that meet the criteria defined in another column. Strings are assumed to have a value of zero. This function is basically the same as DSTDEV except that those function ignore string values This is a sample database: A B C D 1 NAME SALES REGION REGION 2 Joe 3000 East East 3 Sue 4500 North 4 Robert 2800 East 5 Steve 3700 South 6 Ann 2400 West The database range is A1:C10. The criteria range is D1:D2. To find the standard deviation of rainfall in the Western region use this formula: @DSTD(A1:C10,2,D1:D2) -> 1.2029 DSUM or @DSUM Sums all the items matching criteria in the column offset from the upper left corner of the database range. DSUM(database, offset, crit) database the range address of the database offset the offset of the relevant column crit the criteria range This function takes a range and sums all the numbers in the column at the offset value from the first column of the range that meet the criteria defined in another column. This is a sample database: A B C D 1 STATE REGION AVG RF REGION 2 AL South 3.7 South 3 CA West 2.1 4 NJ East 5.2 5 FL South 4.9 6 LA South 6.8 7 TX West 5.0 8 MA North 7.6 9 AZ West 2.2 10 NV West 2.4 The database range is A1:C10. The criteria range is D1:D2. To find the total amount of rainfall for the Southern region use this formula: =DSUM(A1:C10,2,D1:D2) -> 15.4 DVAR Returns the variance of the numeric records that match the criteria. Strings are ignored. DVAR(database, offset, crit) database the range address of the database offset the offset of the relevant column crit the criteria range This function takes a range and finds the variance of the numbers in the column at the offset value from the first column of the range that meet the criteria defined in another column. This is a sample database: A B C D 1 STATE REGION AVG RF REGION 2 AL South 3.7 West 3 CA West 2.1 South 4 NJ East 5.2 5 FL South 4.9 6 LA South 6.8 7 TX West 5.0 8 MA North 7.6 9 AZ West 2.2 10 NV West 2.4 The database range is A1:C10. The criteria range is D1:D2. To find the variance of rainfall in the Southern and Western regions use this formula: =DVAR(A1:C10,2,D1:D3) -> 2.7192 @DVAR Returns the variance of the records that match the criteria. Strings have a value of zero. @DVAR(database, offset, crit) database the range address of the database offset the offset of the relevant column crit the criteria range This function takes a range and finds the variance of the numbers in the column at the offset value from the first column of the range that meet the criteria defined in another column. Strings are treated as having a value of zero. This function is basically the same as DVAR except that those function ignore string values This is a sample database: A B C D 1 STATE REGION AVG RF REGION 2 AL South 3.7 West 3 CA West 2.1 South 4 NJ East 5.2 5 FL South 4.9 6 LA South 6.8 7 TX West 5.0 8 MA North 7.6 9 AZ West 2.2 10 NV West 2.4 The database range is A1:C10. The criteria range is D1:D3. To find the variance of rainfall in the Southern and Western regions use this formula: @DVAR(A1:C10,2,D1:D3) -> 2.7192 DVAR() and @DVAR() are different in that DVAR() ignores string values, while @DVAR treats strings as having a value of zero. |
||
|
||