Excel has a group of functions that allows you to perform a series of operations with the requirement that you have a criteria table . And that the fields of the criteria are equal to the fields of the database so that there is a match.These functions can be used as they make use of a criteria panel that can be easily modified . In addition, each modification made in the criteria table will automatically offer a correct answer.
It is not necessary to fill in all the fields of the criteria that are in the table, also if you do not fill anything it also provides you with a result.
It should be noted that it is very important to have the Excel program, because it allows us very useful tools and options . Emphasizing that you can create custom tables , or apply any function as appropriate.
Using the PRODUCT and AVERAGE functions in Excel is straightforward. Here’s a guide in tabular form to illustrate how to use these functions:
| Step | Action | Description | Example |
|---|---|---|---|
| 1 | Select a cell | Click on the cell where you want the result to appear. | Click on cell A3 |
| 2 | Enter the PRODUCT formula |
Type =PRODUCT( followed by the range of cells you want to multiply. |
Type =PRODUCT(A1:A2) |
| 3 | Complete the formula | Press Enter to complete the formula and see the product of the selected cells. |
Press Enter and see the result in A3 |
| 4 | Select another cell | Click on the cell where you want to calculate the average. | Click on cell B3 |
| 5 | Enter the AVERAGE formula |
Type =AVERAGE( followed by the range of cells for which you want the average. |
Type =AVERAGE(B1:B2) |
| 6 | Complete the formula | Press Enter to complete the formula and see the average of the selected cells. |
Press Enter and see the result in B3 |
Easily use the DPRODUCT function
The “PDRODUCT function” allows you to multiply the values of a column that is included in a database and that is related to the criteria provided. You can have a table with a data series and the result is based on what is selected.
For example, if there is a column with the name of the level that has: primary and secondary, which are two levels and the entire column is selected, the result must be placed in level: primary and the multiplication of all the primary ones will be obtained.
So in the result table in the debt section, the following formula is written : = BDPRODUCT (it can be done manually and the entire area of the table is selected where there is information that in this case would be B2; H13 which would be the database name).
Then the name of the field would be missing , so as you want to obtain the debt, you click on the cell where the name is, so it would be H2. The criterion would be missing, which in this case would be the level.
For this reason , the cells where it says level in the result and the cell below that is where primary or secondary would go are selected. It should also be noted that the name of the criterion must be written the same as the one in the work table.
But it can be left blank and the final formula would be: = PRODUCT (B2; H13; J3; J4) highlighting that J3 is where the result criteria cell is located and J4 is the blank field. Subsequently, giving enter at the end of the formula will automatically obtain a result that would be: 1,458E + 12. But if primary is written in the blank space, the visible result would be another: 4320.
BDAVERAGE function in Excel
It should be noted that the DBAVERAGE function in Excel is responsible for averaging the values in relation to two columns . So you can use the function to be able to obtain the average of a list or table based on a column and applying a criterion found in another column.
In this case the data in the table will be: name, age, department and to obtain the formula, it is applied in another column where the data of: departments, data, column, average are found, so in the average box the formula. Emphasizing that in the database, the entire work table that is between cells A1 and C7 must be selected.
You should consider that if you want to enter data, you can create a specific form for it. This will make it easier for you.
Then, in the field name, the age box that is in cell B1 is selected and in criteria the department box is selected. Together with the box that is located below that would be purchases in the result table that are E1 and E2.
So the final formula would be: = BDAVERAGE (A1; C7; B1; E1; E2) then pressing between the result will be: 25.5. So that’s the sum of the ages of all the people in the purchasing department.
In short, it is easy to use BDPAVERAGE to calculate the average of a column based on the criteria of another column. You can also calculate the percentage of several cells .
