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 the match exists.
These functions can be used, as they make use of a criteria panel that can be easily modified. In addition, each modification that is made in the criteria table will automatically offer a correct answer.
It is not necessary that all the fields of the criteria found in the table be filled, in addition, 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.
Easily use the DPRODUCT function
The “BDPRODUCT function” allows you to multiply the values of a column that is included in a database and that is related to the criteria supplied. 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 box in the debt section the following formula is written : = BDPRODUCT (it can be done manually and the entire area of the box is selected where there is information that in this case would be B2; H13 which would be the database name).
Next, the name of the field would be missing , so as you want to obtain the debt, 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 criterion cell of the result 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.
Ultimately 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 .