☰ Contents · Computing

Mathematical and product functions

Lessons 13–14 · 2 lessons · N. I. Taylaqov (general editor), A. B. Akhmedov, M. D. Pardayeva, A. A. Abdug‘aniyev, U. M. Mirsanov. Informatics and Information Technologies, Grade 10, 1st edition. “Extremum-Press”, Tashkent, 2017
14

Functions for calculating products

Textbook: pp. 35–37
GoalCalculates a product with =A1*B1, PRODUCT and SUMPRODUCT and tells them apart.
New words
PRODUCT: multiplies all the numbers given in its arguments or range · PRODUCTSUMPRODUCT: multiplies matching elements of ranges and adds the products · SUMPRODUCTrange: a block of cells such as A1:A4 used as one argument · diapazonfactorial n!: the product 1·2·3·…·n · faktorial
Explanation

There are two ways to calculate a product: with the “*” sign (=A1*B1*C1) or with the PRODUCT function. PRODUCT(number1, number2, …) multiplies the arguments or all the numbers in a range and ignores empty and text cells in the range: for 1.5, 2, 4, 0.5 in A1:A4, =PRODUCT(A1:A4) = 6. When the range is long, the function shortens the entry and is easy to keep when a new row is added. SUMPRODUCT(array1, array2) multiplies matching cells and adds the products; it is useful for calculating an order total with one formula. A factorial can be written PRODUCT(1, 2, …, n) or FACT(n). SUM is used for a sum and PRODUCT for a product; do not mix them up.

Worked examples
=PRODUCT(2,3,4) = 24. If A1:A4 hold 1.5, 2, 4, 0.5, then =PRODUCT(A1:A4) = 6; =PRODUCT(A1:A5) still gives 6 when A5 is empty, because an empty cell is not counted. By hand: 1.5·2 = 3; 3·4 = 12; 12·0.5 = 6.
An order: the prices in A2:A4 are 4 200, 7 500 and 1 800 so‘m; the quantities in B2:B4 are 5, 2 and 10. =SUMPRODUCT(A2:A4,B2:B4) = 4 200·5 + 7 500·2 + 1 800·10 = 21 000 + 15 000 + 18 000 = 54 000 so‘m. If a price of 100 000 rises first by 10% and then by 20%: =PRODUCT(100000,1.1,1.2) = 132 000.
Class activity

“One formula”: groups build an order table with 5 products; first they calculate each row total separately and add them, then check the result with one SUMPRODUCT formula.

Practice
1
What is =PRODUCT(5,2,3)?
2
If A1 = 4, A2 is empty and A3 = 5, what is =PRODUCT(A1:A3)?
3
Prices are 4 200, 7 500, 1 800 and quantities 5, 2, 10. What is the SUMPRODUCT result in so‘m?
4
What is the advantage of writing PRODUCT(A1:A20) instead of A1*A2*…*A20 in a long list?