Microsoft Office Tutorials and References

In Depth Information

**Being Productive with DPRODUCT**

By testing to see whether DGET returns an error, you can discover problems

with your data. Perhaps you suspect that a student has registered twice for

a specific class. If this is true, two records will have the same Student ID

and Class.

Figure 17-13 shows how to check if student NR5090 is entered more than

once for Calculus 101. If there is more than one record, DGET returns an

error. Cell F5 contains a formula that nests the DGET function inside the

ISERROR function; all that is inside the IF function. If DGET returns an error,

return one message; if DGET does not return an error, return a different

message. Here is the formula:

=IF(ISERROR(DGET(Students,”Student ID”,F2:G3)),F3 & “ has

duplicate records”, F3 & “ has one record”)

Figure 17-13:

Using DGET

to test for

duplicate

records in a

database.

Being Productive with DPRODUCT

DPRODUCT multiplies values that match the criterion in a database. This is

powerful but also able to produce results that are not the intention. In other

words, it’s one to thing to add and derive a sum. That is a common operation

on a set of data. Looking back at Figure 17-8, you can see that the total sales

for Jack Bennet, $79,123, is the sum of three amounts: $43,234, $12,450, and

$23,450. If multiplication was applied to the three amounts, the answer (the

product) would be $12,622,274,385,000. Oops! That’s over 12
trillion
dollars!

DPRODUCT multiplies and, therefore, is not likely to be used as often as

a function like DSUM, but when you need to multiply items in a database,

DPRODUCT is a tool of choice.

Figure 17-14 shows a situation in which DPRODUCT is productive. The

database area contains shirts. For each shirt size, there are two rows — the price

per shirt and the number of shirts that are packed in a carton. The cost for

a carton of shirts is, therefore, the product of the price per shirt times the

number of shirts. There are four shirts sizes, each with its own price and

carton count.