Database functions dvar, dvarp, dstdev, dstdevp
Assuming the following data.
| Year | Quarter | Profit | Criteria | |
| 2011 | 1 | 1000 | Quarter | Profit |
| 2012 | 1 | 2000 | 2 | >500 |
| 2011 | 2 | 1340 | ||
| 2012 | 2 | 3400 | Quarter | Profit |
| 2011 | 3 | 2345 | 1 | <3000 |
| 2012 | 3 | 4500 | ||
| 2011 | 4 | 6000 | Quarter | Profit |
| 2012 | 4 | 10000 | 4 | <>5000 |
Excel Function DVAR
The DVAR() function is used to calculate the variance of a column from a database.
The calculation is based on a user defined condition.
The syntax of this function is as given below:
DVAR(database,field,condition)
| DVAR() | Formula |
| 2121800 | =DVAR(L4:N12,"Profit",O5:P6) |
| 500000 | =DVAR(L4:N12,"Profit",O8:P9) |
| 8000000 | =DVAR(L4:N12,"Profit",O11:P12) |
Excel Function DVARP
The DVARP() function is used to calculate the variance of a column from a database.
The calculation is based on a user defined condition.
This function can include text and other Boolean values in the selected field unlike DVAR, where only
numbers are taken into account.
The syntax of this function is as given below:
DVARP(database,field,condition)
| DVARP() | Formula |
| 1060900 | =DVARP(L4:N12,"Profit",O5:P6) |
| 4000000 | =DVARP(L4:N12,"Profit",O11:P12) |
| 250000 | =DVARP(L4:N12,"Profit",O8:P9) |
Excel Function DSTDEV
The DSTDEV() function is used to calculate the standard deviation of a column from a database.
The calculation is based on a user defined condition.
The syntax of this function is as given below:
DSTDEV(database,field,condition)
| DSTDEV() | Formula |
| 1456.63996924429 | =DSTDEV(L4:N12,"Profit",O5:P6) |
| 707.106781186548 | =DSTDEV(L4:N12,"Profit",O8:P9) |
| 2828.42712474619 | =DSTDEV(L4:N12,"Profit",O11:P12) |
Excel Function DSTDEVP
The DSTDEVP() function is used to calculate the standard deviation of a column from a database.
The calculation is based on a user defined condition.
This function can include text and other Boolean values in the selected field unlike DSTDEV, where only
numbers are taken into account.
The syntax of this function is as given below:
DSTDEVP(database,field,condition)
| DSTDEVP() | Formula |
| 1030 | =DSTDEVP(L4:N12,"Profit",O5:P6) |
| 500 | =DSTDEVP(L4:N12,"Profit",O8:P9) |
| 2000 | =DSTDEVP(L4:N12,"Profit",O11:P12) |