Advanced Mathematical Functions in Excel – Part 2

 Advanced Mathematical Functions in Excel – Part 2

Advanced Mathematical Functions in Excel – Part 2

SUMPRODUCT is a very useful mathematical function in Excel. SUMPRODUCT function returns the sum of the products of the corresponding values in two or more arrays. The syntax of this function is SUMPRODUCT(array1, array2, ……). array1, array2 etc could be directly supplied values or a range of cells. You can pass up to 255 arrays to this function. If you enter a formula =SUMPRODUCT({3,4,5}, {1,2,3}), the result would be 26 ((3*1)+(4*2)+(5*3)). You can also have an excel sheet as follows.

Now enter the formula =SUMPRODUCT(A2:A4,B2:B4,C2:C4) in D2, =SUMPRODUCT(A2:A4,B2:B4,{0;1;3}) in D3 and =SUMPRODUCT(A2:A4,B2:B4,{“hai”;”hello”;”welcome”}) in D4. Now the result value in D2 would be 87 ((2*3*4)+(3*5*1)+(6*2*4)) and value in D3 would be 51 ((2*3*0)+(3*5*1)+(6*2*3)) and value in D4 would be 0 as string values are considered 0 in the calculation.

SUMSQ function returns the sum of squares of a given set of numbers and the syntax is SUMSQ(num1, num2,………). Using the above excel sheet that contains ARR 1 to ARR 3, if you create a formula =SUMSQ(A2:A4), the result would be 49 (2*2+3*3+6*6). You can also create another function =SUMSQ(A4,B2,C3,B4) to get the result 50 (6*6+3*3+1*1+2*2).

SERIESSUM returns the sum of a power series based on the below expansion.

SERIESSUM (x, n, m, a) = a1xn + a2x(n+m) + a3x(n+2m) + ……… + ajx(n+(j-1)m)

The syntax of SERIESSUM is SERIESSUM (x, n, m, coefficients) and if you create a function =SERIESSUM(2,2,1,{ 3,4,5}), then the result would be 124 (3*22+4*23+5*24).

SUMX2MY2 returns the sum of differences of squares of two sets of values and the syntax is SUMX2MY2 (arr1, arr2). arr1 and arr2 are two different sets of values. You can specify a range of cells parameters or pass values directly to the parameters. Consider the following excel sheet.

If you enter the formula =SUMX2MY2(A2:A6,B2:B6) in any of the cells, you will get the result 98. Here the calculation is ((49-9)+(16-1)+(64-25)+(4-0)+(9-9))=(40+15+39+4+0)=98. You can also try another function =SUMX2MY2(A2:A5,{5,-2,-7,6}) to get the result 19. Here the calculation is ((49-25)+(16-4)+(64-49)+(4-36))=(24+12+15+(-32))=19.

SUMX2PY2 returns the sum of sum of squares of two different set of provided numbers and the syntax is SUMX2PY2 (arr1, arr2). If we use the same Excel sheet given above and enter the formula =SUMX2PY2(A2:A6,B2:B6) in any of the cells, then you will get the result 186. Here the calculation is ((49+9)+(16+1)+(64+25)+(4+0)+(9+9))=(58+17+89+4+18)= 186. If you try passing parameters directly and write the function =SUMX2PY2(A2:A5,{5,-2,-7,6}), you will get the result 247. Here the calculation is ((49+25)+(16+4)+(64+49)+(4+36))=(74+20+113+40)=247.

SUMXMY2 returns the sum of the squares of differences between two set of provided numbers and the syntax is SUMXMY2 (arr1, arr2). If we use the same Excel sheet given above and enter the formula =SUMXMY2(A2:A6,B2:B6), then the result would be 234. Here the calculation is ((7-3)2 + (4-1)2 + (8- -5)2 + (2-0)2 + (-3-3)2) = (42+32+132+22+(-6)2) = 16+9+169+4+36 = 234. If you write the formula =SUMXMY2(A2:A5,{5,-2,-7,6}), then the result would be 281 as per the calculation ((7-5)2+(4–2)2+(8–7)2+(2-6)2) = (22+62+152+(-4)2) = 4+36+225+16=281.

FACT function returns the factorial of a number and the syntax is FACT (number). If you supply a negative number as the argument, then you will get a #NUM! error and if you supply a non-numeric value, then you will get a #VALUE! error. If you enter the formula =FACT(4), you will get the result 24 (1*2*3*4).

Read More about Excel here

ExcelWorkbook.com

Related post

Leave a Reply