Difference between revisions of "Manuals/calci/DMIN"
Jump to navigation
Jump to search
| (12 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | = DMIN( | + | <div style="font-size:30px">'''DMIN (Array,Field,Criteria)'''</div><br/> |
| − | + | *Where 'Array' is the range of cells that makes up the list or database | |
| − | *Where ' | + | *'Field' shows which column is used in the function and |
| − | + | *'Criteria' is the range of cells that contains the specified condition. | |
| − | *' | + | **DMIN(), returns the minimum value from selected database entries. |
| − | |||
| − | *' | ||
| − | |||
| − | DMIN() | ||
== Description == | == Description == | ||
| − | DMIN( | + | DMIN (Array,Field,Criteria) |
This function returns the smallest number from the database that satisfies the given condition. | This function returns the smallest number from the database that satisfies the given condition. | ||
| − | *A ' | + | *A 'Field' argument can be expressed as "Salary" (a column name) or 3(position of the column). |
*Criteria can be any range on the Calci with minimum of one column and at least one row indicating the condition. | *Criteria can be any range on the Calci with minimum of one column and at least one row indicating the condition. | ||
*The Criteria columns should not overlap the list of data. | *The Criteria columns should not overlap the list of data. | ||
| Line 28: | Line 24: | ||
{| id="TABLE3" class="SpreadSheet blue" | {| id="TABLE3" class="SpreadSheet blue" | ||
|- class="even" | |- class="even" | ||
| + | | class="sshl_f" | | ||
| + | | class="sshl_f" | A | ||
| + | | class="sshl_f" | B | ||
| + | | class="sshl_f" | C | ||
| + | | class="sshl_f" | D | ||
| + | |- class="odd" | ||
| + | | class="sshl_f" | 1 | ||
| class="sshl_f" | '''Name''' | | class="sshl_f" | '''Name''' | ||
| class="sshl_f" | '''Age''' | | class="sshl_f" | '''Age''' | ||
| class="sshl_f" | '''Salary''' | | class="sshl_f" | '''Salary''' | ||
| class="sshl_f" | '''Age''' | | class="sshl_f" | '''Age''' | ||
| − | |- class=" | + | |- class="even" |
| + | | class="sshl_f" | 2 | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| class="sshl_f" | <40 | | class="sshl_f" | <40 | ||
| class="sshl_f" | >5000 | | class="sshl_f" | >5000 | ||
| class="sshl_f" | >32 | | class="sshl_f" | >32 | ||
| − | |- class=" | + | |- class="odd" |
| + | | class="sshl_f" | 3 | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| class="sshl_f" | >30 | | class="sshl_f" | >30 | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| − | |- class=" | + | |- class="even" |
| + | | class="sshl_f" | 4 | ||
| class="sshl_f" | '''Name''' | | class="sshl_f" | '''Name''' | ||
| class="sshl_f" | '''Age''' | | class="sshl_f" | '''Age''' | ||
| class="sshl_f" | '''Salary''' | | class="sshl_f" | '''Salary''' | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| − | |- class=" | + | |- class="odd" |
| + | | class="sshl_f" | 5 | ||
| class="sshl_f" | John | | class="sshl_f" | John | ||
| class="sshl_f" | 34 | | class="sshl_f" | 34 | ||
| class="sshl_f" | 5500 | | class="sshl_f" | 5500 | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| − | |- class=" | + | |- class="even" |
| + | | class="sshl_f" | 6 | ||
| class="sshl_f" | Bill | | class="sshl_f" | Bill | ||
| class="sshl_f" | 35 | | class="sshl_f" | 35 | ||
| class="sshl_f" | 6500 | | class="sshl_f" | 6500 | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| − | |- class=" | + | |- class="odd" |
| + | | class="sshl_f" | 7 | ||
| class="sshl_f" | Clark | | class="sshl_f" | Clark | ||
| class="sshl_f" | 29 | | class="sshl_f" | 29 | ||
| class="sshl_f" | 7000 | | class="sshl_f" | 7000 | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| − | |- class=" | + | |- class="even" |
| + | | class="sshl_f" | 8 | ||
| class="sshl_f" | Bob | | class="sshl_f" | Bob | ||
| class="sshl_f" | 45 | | class="sshl_f" | 45 | ||
| class="sshl_f" | 9000 | | class="sshl_f" | 9000 | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| − | |- class=" | + | |- class="odd" |
| + | | class="sshl_f" | 9 | ||
| class="sshl_f" | Susan | | class="sshl_f" | Susan | ||
| class="sshl_f" | 37 | | class="sshl_f" | 37 | ||
| class="sshl_f" | 4000 | | class="sshl_f" | 4000 | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| − | |- class=" | + | |- class="even" |
| + | | class="sshl_f" | 10 | ||
| class="sshl_f" | Jill | | class="sshl_f" | Jill | ||
| class="sshl_f" | 45 | | class="sshl_f" | 45 | ||
| Line 79: | Line 91: | ||
|} | |} | ||
| − | =DMIN(A4:C10,"Salary",D1:D2) | + | =DMIN(A4:C10,"Salary",D1:D2) : Calculates the minimum value in the range A4 to C10, from the cells in the Salary column where Age>32. <br>Returns '''4000''' as a result. |
| + | |||
| + | =DMIN(A4:C10,2,A1:A3) : Calculates the minimum value in the range A4 to C10, from the cells in the second column (Age) for all employees. <br>Returns '''29''' as a result. | ||
| + | |||
| + | =DMIN(A4:C10,"Age",B1:B3) : Calculates the minimum value in the range A4 to C10, from the cells in the Age column where [Age<40 OR Age>30]. <br>Returns '''29''' as a result. | ||
| + | |||
| + | =DMIN(A4:C10,3,C4:C10) : Calculates the minimum value in the range A4 to C10, from the cells in the Salary column for all the employees. <br>Returns '''4000''' as a result. | ||
| − | = | + | ==Related Videos== |
| − | + | {{#ev:youtube|eiIAd5xU3Vo|280|center|DMIN}} | |
== See Also == | == See Also == | ||
| Line 93: | Line 111: | ||
*[http://en.wikipedia.org/wiki/Aggregate_function List of Aggregate Functions] | *[http://en.wikipedia.org/wiki/Aggregate_function List of Aggregate Functions] | ||
*[http://en.wikipedia.org/wiki/Aggregate_function DMIN] | *[http://en.wikipedia.org/wiki/Aggregate_function DMIN] | ||
| + | |||
| + | |||
| + | |||
| + | |||
| + | *[[Z_API_Functions | List of Main Z Functions]] | ||
| + | |||
| + | *[[ Z3 | Z3 home ]] | ||
Latest revision as of 13:48, 24 August 2018
DMIN (Array,Field,Criteria)
- Where 'Array' is the range of cells that makes up the list or database
- 'Field' shows which column is used in the function and
- 'Criteria' is the range of cells that contains the specified condition.
- DMIN(), returns the minimum value from selected database entries.
Description
DMIN (Array,Field,Criteria)
This function returns the smallest number from the database that satisfies the given condition.
- A 'Field' argument can be expressed as "Salary" (a column name) or 3(position of the column).
- Criteria can be any range on the Calci with minimum of one column and at least one row indicating the condition.
- The Criteria columns should not overlap the list of data.
Examples
Consider the following table with Employee names, Age and Salary columns.
Row1 to Row3 has the criteria fields. Row4 to Row10 display the actual data.
| A | B | C | D | |
| 1 | Name | Age | Salary | Age |
| 2 | <40 | >5000 | >32 | |
| 3 | >30 | |||
| 4 | Name | Age | Salary | |
| 5 | John | 34 | 5500 | |
| 6 | Bill | 35 | 6500 | |
| 7 | Clark | 29 | 7000 | |
| 8 | Bob | 45 | 9000 | |
| 9 | Susan | 37 | 4000 | |
| 10 | Jill | 45 | 8000 |
=DMIN(A4:C10,"Salary",D1:D2) : Calculates the minimum value in the range A4 to C10, from the cells in the Salary column where Age>32.
Returns 4000 as a result.
=DMIN(A4:C10,2,A1:A3) : Calculates the minimum value in the range A4 to C10, from the cells in the second column (Age) for all employees.
Returns 29 as a result.
=DMIN(A4:C10,"Age",B1:B3) : Calculates the minimum value in the range A4 to C10, from the cells in the Age column where [Age<40 OR Age>30].
Returns 29 as a result.
=DMIN(A4:C10,3,C4:C10) : Calculates the minimum value in the range A4 to C10, from the cells in the Salary column for all the employees.
Returns 4000 as a result.
Related Videos
See Also
References