Difference between revisions of "Manuals/calci/DPRODUCT"
Jump to navigation
Jump to search
| (5 intermediate revisions by the same user not shown) | |||
| Line 15: | Line 15: | ||
This function returns the product of values from the database that satisfy a given condition. | This function returns the product of values from the database that satisfy a given condition. | ||
| − | *A 'field' argument can be expressed as " | + | *A 'field' argument can be expressed as "Price" (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. | ||
*'criteria' columns should not overlap the list of data. | *'criteria' columns should not overlap the list of data. | ||
| Line 21: | Line 21: | ||
== Examples == | == Examples == | ||
| − | Consider the following table with | + | Consider the following table in Zcalci with Stationary Items, Quantity, Price and Percentage Profit. |
| − | |||
| − | |||
| − | <div id=" | + | <div id="4SpaceContent" class="zcontent" align="left"> |
| − | {| id=" | + | {| id="TABLE2" class="SpreadSheet blue" |
|- class="even" | |- class="even" | ||
| − | | class="sshl_f" | | + | | class="sshl_f" | |
| − | | class="sshl_f" | | + | | class="sshl_f" | A |
| − | | class="sshl_f" | | + | | class="sshl_f" | B |
| − | | class="sshl_f" | | + | | class="sshl_f" | C |
| + | | class="sshl_f" | D | ||
| + | |||
|- class="odd" | |- class="odd" | ||
| − | | class="sshl_f" | | + | | class="sshl_f" | 1 |
| − | | class="sshl_f" | | + | | class="sshl_f" | '''Item''' |
| − | | class="sshl_f" | | + | | class="sshl_f" | '''Quantity''' |
| − | | class="sshl_f" | | + | | class="sshl_f" | '''Price''' |
| + | | class="sshl_f" | '''Profit''' | ||
| + | |||
|- class="even" | |- class="even" | ||
| − | | class="sshl_f" | | + | | class="sshl_f" | 2 |
| − | | class="sshl_f" | > | + | | class="sshl_f" | Pen |
| + | | class="sshl_f" | >10 | ||
| class="sshl_f" | | | class="sshl_f" | | ||
| − | | class=" | + | | class="ssh1_f" | |
| + | |||
| + | |||
|- class="odd" | |- class="odd" | ||
| − | | class="sshl_f" | ''' | + | | class="sshl_f" | 3 |
| − | | class="sshl_f" | ''' | + | | class="sshl_f" | '''Item''' |
| − | | class="sshl_f" | ''' | + | | class="sshl_f" | '''Quantity''' |
| − | | class="sshl_f" | | + | | class="sshl_f" | '''Price''' |
| + | | class="sshl_f" | '''Profit''' | ||
| + | |||
|- class="even" | |- class="even" | ||
| − | | class="sshl_f" | | + | | class="sshl_f" | 4 |
| − | | class="sshl_f" | | + | | class="sshl_f" | Pen |
| − | | class="sshl_f" | | + | | class="sshl_f" | 12 |
| − | | class="sshl_f" | | + | | class="sshl_f" | 40 |
| + | | class="sshl_f" | 2 | ||
| + | |||
|- class="odd" | |- class="odd" | ||
| − | | class="sshl_f" | | + | | class="sshl_f" | 5 |
| − | | class="sshl_f" | | + | | class="sshl_f" | Pencil |
| − | | class="sshl_f" | | + | | class="sshl_f" | 20 |
| − | | class="sshl_f" | | + | | class="sshl_f" | 20 |
| + | | class="sshl_f" | 1 | ||
| + | |||
|- class="even" | |- class="even" | ||
| − | | class="sshl_f" | | + | | class="sshl_f" | 6 |
| − | | class="sshl_f" | | + | | class="sshl_f" | Books |
| − | | class="sshl_f" | | + | | class="sshl_f" | 25 |
| − | | class="sshl_f" | | + | | class="sshl_f" | 75 |
| − | | | + | | class="ssh1_f" | 5 |
| − | + | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
|- class="odd" | |- class="odd" | ||
| − | | class="sshl_f" | | + | | class="sshl_f" | 7 |
| − | | class="sshl_f" | | + | | class="sshl_f" | Pen |
| − | | class=" | + | | class="sshl_f" | 30 |
| − | | class="sshl_f" | | + | | class="sshl_f" | 93.33 |
| + | | class="sshl_f" | 4 | ||
|} | |} | ||
| − | =DPRODUCT() | + | = DPRODUCT(A3:D7,"Profit",A1:B2) : Calculates the product of percentage profits of Pens in the range A3 to D7, for the cells with Quantity>10. Returns '''8''' as a result. |
| + | |||
| + | = DPRODUCT(A3:D7,4,A3:D7) : Calculates the product in the range A3 to D7, of all values in Profit column. Returns '''40''' as a result. | ||
| − | = | + | ==Related Videos== |
| − | + | {{#ev:youtube|loDtcf7Z0xs|280|center|DPRODUCT}} | |
== See Also == | == See Also == | ||
Latest revision as of 13:27, 10 October 2015
DPRODUCT(database, field, criteria)
- Where 'database' 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.
DPRODUCT() multiplies the values in a column of a list or database that match the specified condition.
Description
DPRODUCT(database, field, criteria)
This function returns the product of values from the database that satisfy a given condition.
- A 'field' argument can be expressed as "Price" (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' columns should not overlap the list of data.
Examples
Consider the following table in Zcalci with Stationary Items, Quantity, Price and Percentage Profit.
| A | B | C | D | |
| 1 | Item | Quantity | Price | Profit |
| 2 | Pen | >10 |
| |
| 3 | Item | Quantity | Price | Profit |
| 4 | Pen | 12 | 40 | 2 |
| 5 | Pencil | 20 | 20 | 1 |
| 6 | Books | 25 | 75 | 5 |
| 7 | Pen | 30 | 93.33 | 4 |
= DPRODUCT(A3:D7,"Profit",A1:B2) : Calculates the product of percentage profits of Pens in the range A3 to D7, for the cells with Quantity>10. Returns 8 as a result.
= DPRODUCT(A3:D7,4,A3:D7) : Calculates the product in the range A3 to D7, of all values in Profit column. Returns 40 as a result.