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.
Search JabSto ::

Custom Search