6. Chapter Mathematical and Statistical Calculations using MS-EXCEL Contents: 6.1 Step by step procedure to perform basic logical function using MS Excel 6.2 Step by step procedure to perform basic mathematical function using MS Excel 6.3 Step by step procedure to perform basic statistical function using MS Excel (101, 15 My 61 Introduction : In previous chapters we have studied some basic Mathematics and Statistics. In this chapter we will study some basic functions in MS-EXCEL which will help for mathematical and statistical calculations. Basic logical, mathematical and statistical functions using MS-EXCEL are described in this chapter. Step by step procedures to perform some built in functions are explained. About Microsoft Excel : Microsoft Excel is a spreadsheet program developed by Microsoft office system. It can be used to create workbooks (a collection of spreadsheet). These workbooks are used to track data, build models and analyse data. Analysis gives more informative decisions. Using formulas in excel even complex calculations can be performed. In excel data can be presented in variety of professions! looking charts. 6.2 Logical functions in Excel 2010 : Logical functions are used for decision making. Excel 2010 uses seven logical functions -i AND, 2. FALSE, 3. IF, 4, IFERROR, 5. NOT, 6. OR, 7. TRUE These functions appear on the Logical command button's drop-down menu on the Formulas tab of the Ribbon. When evaluated all the logical functions return either the logical TRUE ® logical FALSE. 118 « PRASHANT PUBLICATIONSge los Step gical arguments that are specifi = and <>), whi ang oon! foMCCTON: To Find Jogi real fun _ Open excels Worksheet sie Sy een coi SSS efi Be Sermon netion ii n iN excel 2010, follow the followin 2 step Ao Bie cee BCA 301 : FUNDAM [ENTAL, MATHEMATICS AND STATISTI ICS. ¢ 119In drop down menu seven logical functions — AND, FALSE, IF, IFERROR, NOT, OR, ang TRUE are listed. Functioning and syntax of these functions are given with illustrative examples, 1, AND function : AND function tests whether the logical arguments are TRUE or FALSE. If they are all TRUE, the AND funetion returns TRUE to the cell. If any are FALSE, the AND function returns FALSE. Syntax for AND funetion is: =AND (logical (logical2),. 1 SYNTAX reSuT 2 2. FALSE function : False function returns value FALSE. Syntax for FALSE function is: = FALSE() The FALSE function syntax has no argument, FALSE() takes no argument and simply enters logical FALSE in its cell. 120 # PRASHANT PUBLICATIONS,weeat also type the word FALSE direct) il interpret as the logical value FAL gxample Yon t e ee the worksheet or into the formula, Microsoft fetus the vale FALSE. 3. IF function : IF function is used to see if any condition is true or false This function as waa te “logical_test” expression is TRUE or FALSE. If TRUE, the IF function returns the “value if true” argument. If FALSE, the IF function returns the svalue_if_ false’ ecu Upto 64 IF functions can be nested to construct more elaborate tests. Suntax for IF function is: = [F(logical test, (value if true),(value if false)) Where, Logical test: value to be tested, Value if true: a value to be displayed if it is true. Value if false: a value to be displayed if it is false. Example: iF (A5>250, "Profit, "Loss ‘SYNTAX, RESULT 2320 —_IF(ADIS0,"Proit’,"Loss") Profit _ function retuns profit if production Ba, F{ A3>250,"Profit”,"Loss") LOSS __is>250and|oss f production is «250, 2s ‘=IF( A4>250,"Profit","Loss") Loss. fe Toei evom nama) 4. 1F ERROR funtion : IFERROR function is used to replace the error message. Syntax for IFERROR function is = IFERROR(value, value_if_error) Where, Value: Value to be tested. Value if error ; Expression to be returned if the value tested is error. BCA 301: FUNDAMENTAL MATHEMATICS AND STATISTICS 121IFERROR function tests. whether the “value” expression is an error, returns “value_if_error” if the expression is an error, or “value” of the expression ifit error. TFERROR 8 no ay Worksheet Example 5. NOT function : NOT function tests whether the “logical” argument is TRUE or FALSE. If TRUE, the NOT function returns FALSE. IF FALSE, the NOT function retums TRUE. Syntax for NOT function is : = NOT (logical) Where, Logical : This evaluates True or False. If True then False is returned and if False then Trué is Retumed, Example : NOT function returns False if the expression {s True and True if the expression is False +122 « PRASHANT PUBLICATIONSfunction? or function tests whether the “logical” arguments are TRUE or FALSE. If an: on returns TRUE. [all are FALSE, the OR function returns FALSE, Upto 255 be tested as true or false, are TRUE, the funet tions cam asin Pit for OR function is : s ‘OR(ogical (logieal2),.... where, : real ~ implies the cel reference that is being checked. ree Soe Since one expression is true, th function results Tue. 7. TRUE function : ‘TRUE function returns logical value True. TRUE function takes no argument and simply enters logical TRUE in its cell. TRUE function syntax has no argument. syntax for TRUE function is: = TRUE() Example 63 Excel Mathemat ee | Mathematical functions are used to carry out many of the common Mathematical calculations include : Built in Exce mathematical calculations and trigonometric ratios. arithmetic, conditional sums and products. Some basic and essential built in Excel Mathematical functions are explained below : 1. SUM 2.PRODUCT 3.POWER 4. SQRT 5, SUMPRODUCT 6.0DD 7. EVEN 8. ABS 9. SUMIF 10. ROUND To find the mathematical functions in Excel 2010, follow the following steps. BCA 301 : FUNDAMENTAL MATHEMATICS AND STATISTICS ¢ 123srep 1: Open Excel workshect Vine Bee Roem : Se EE Rm Beene a 124 » PRASHANT PUBLICATIONS4; Select the category as Maths and Trig, pear in alphabetic order in the select @ in dropout menu of Maths and Trig functions ap tax of some function box. We can select the function which is necded. Functioning and syn! sanctions are given with illustrative examples. 1. SUM: The SUM function gives the sum of range of numbers selected. Syntax of SUM function is : ; =SUM(number1,(number?),... Where, number!, number2, .......-- Example : a) are the numbers to be added. 2, PRODUCT : PRODUCT function gives the product of range Syntax of PRODUCT function is: =PRODUCT(number1 ,(number2),.... of numbers selected. ) BCA 301 : FUNDAMENTAL MATHEMATICS AND STATISTICS e 125Where, number number2,...........are the values to be multiplied Example : SYNTAX 21 =PROSUCTIA2:A6) 2 3. POWER : Power function gives the result of the number raised to a given power. Syntax for POWER funetion is: = POWER(number,power) Where. Number : the base number. Power: the exponent. Both number and power values must be numerical. Example : ‘S| =POWER(B2,83) [ai Bee : 1 "Syntax starts with"=" Result 2 NUMBER= 25 POWER(B2,B3) 3 POWER= 3 [4 OWER(B2,B3) CuN fa : "SYNTAX RESULT 2 |Numeer= 25 =POWER(B2,83) 2 POWER= 3 a =a saa 4, SQRT: SQRT function gives the positive square root of the given number. Syntax for SQRT function is : =SQRT(number) 126 © PRASHANT PUBLICATIONSle = pram |_*SORT(82) SYNTAX T E ‘ suUMPRODUCT t GUMPRODUCT function gives the sum of syntax for SUMPRODUCT function = SUMPRODUCT(arrayt, array? Product of corresponding arrays, ) array3, wa) array values may be r e a The array 'y be one or more cells Containing numeric values. The non-numeric values inthe aay are treated as the value zero, The array argument must have the same direction. Example | ssumpnoouctiazaszne c20m A Bete D pene 1 ARRAY] ARRAY2 ARRAY3 SYNTAX RESULT 2.8 15 =SUMPRODUCTIA2:A6,82:86) 1065 3] v7 415 =SuMPRoDuctiaz:n6.e2:86,ca:c[___a54sa] 4 ua 8 5 2 10 Ww 6 2 “4 2B 7 6. ODD: ODD function rounds the number to next odd number. Note positive number rounds up and negative number rounds down to the nearest odd integer. If the number is odd it gives same number. Syntax for ODD function is : =ODD(number) Example : syntax 24 =ODD(number) 25 55 =ODD(A3) 5S. -30 =ODD(A4) “Bt -43, =ODD(AS) 43 0 -onis) =. BCA 301 : FUNDAMENTAL. MATHEMATICS AND STATISTICS ¢ 1277. EVEN : EVEN function rounds the number to next even number. Note posit negative number rounds down to the nearest ov‘ number. If the number is zero it rounds to next even number. Syntax for EVEN function is : =EVEN(number) Example : go, y SYNTAX RESULT 2 41 =EVEN(A2) 42 [3. 66 =EVEN(A3) 66 4a -27 =EVEN(A4) 28 = -44 =EVEN(AS) 44 fs} ewan Cd tive number rounds upg 8 Up ang en integer. If the number is even it gives sany 8. ABS: ABS function gives the absolute value of the number i.e a number without its sign. Syntax for ABS function is: =ABS(number) Where, Number : a numeric value of which absolute value is required. 9. SUMIF : SUMIF function gives sum of the cells/numbers specified by a given condition. Syntax for SUMIF function is: _ =SUMIF(range,criteria,(sum range)) ‘Where, 128 « PRASHANT PUBLICATIONSpange range of cells Containing the values, criteria : condition to be tested against each value in range. sum range) : the cells containing the numbers which are to be added together if the range sites te erteria, i example ! 116,000 25. =SUMIF(A2:A7,"<30,000",62:87) _[ 73 25,000 3, =SUMIF(A2:A7,"41,00",B2:87) 18 55,000 15, =SUMIF(B2:B7,">15",A2:A7) 101000 41,000 18 60,000 30 20,000 2 Note: 1. Criteria must be expressed in quotes in syntax. 2. SUMIF function is non-case sensitive. 10. ROUND : ROUND function round the number to a specified number of digits’ Syntax for ROUND function is : =ROUND (number, num digits) Where, Number : numeric value which is to-be rounded to specified di Num digits : number of decimal places to round to. BCA 301 : FUNDAMENTAL MATHEMATICS AND STATISTICS ¢ 129ya cart Colibri “4 Paste BA Copy > caste : + Prorat painter B FU RESULT 2 37.23 =ROUND(A2,0) 37 3] 4358 43.58 4 -45.1 =ROUND(A4,1)—-45.1 5 -67.73 =ROUND(AS,1)__-67.7 |S 24.5671 =ROUND(A6,2) 2457], Zz 6-4 Statistical Functions in Exéel 2010 ‘ Excel provides large number of Statistical functions. Statistical functions help us to perform simple calculations to complex statistical distribution and probability tests. Some basic and essential built in Excel Statistical functions are explained below: Finding statistical function in Excel 2010 : STEP Click the insert function. 2c oma TNS A me Seen te Frome enter 130 e PRASHANT PUBLICATIONSSTEP 2: gelect the category as statistical, In select a function box, Statistical functions appear in alphabetic order. Some basic and essential built in Excel statistical functions are explained below : 1.MAX 2. MIN 3. LARGE 4. SMALL 5. AVERAGE 6. GEOMEAN 7. HARMEAN 8. MEDIAN 9. MODE 10. DECILE 11. PERCENTILE. 1. MAX: MAX function is used to firid the largest value in the given data set. It ignores logical values and text in the data. Syntax of MAX function is : =MAX(number1,(number2),. ) set of numerical values. Where,numberl number?, 2 = ie Ae =e [DATA SYNTAX as -manazas) Lo 2a a5 67 28 BCA 301 ; FUNDAMENTAL MATHEMATICS AND. STATISTICS ¢ 1312.MIN: MIN function is used to find the smallest value in the given data set. It ignores logical y, and text in the data, Syntax of MIN unetion is: =MIN(number1,(number?), ) Where,number! number2,.,....: set of numerical values. Example: [a DATA svNTAX 2] 45, =MIN(A2:A6) a] 3 23 4 a5 5 °7 6 28 3. LARGE: LARGE function is used to find the k" largest value in the data. Syntax for LARGE function is: =LARGE(array,k) Where, Array: given data set. K: index Example : Find 4* largest value from the given data set. 4. SMALL: SMALL function is used to find the k'* smallest value in the data. Syntax for SMALL function is: = SMALL(array,k) Where, Array: given data set. 132 « PRASHANT PUBLICATIONS lugsK: index Example: Find 3" smallest value from the given data set DATA SYNTAX retin | ag ssmaaui(az:aeo,a) [al is 67 28 35 90 5, AVERAGE : AVERAGE function gives the average (arithmetic mean) of the data. syntax for AVERAGE function is : =AVERAGE(number1,(number2),. Where, Number number2,. ...set of numerical values of which average is to be determined. Example : 23 =AVERAGE(A2:A8) 56 =AVERAGE(A3,AS,A8) 89 80 34 25 44 6. GEOMEAN : GEOMEAN function gives the Geometric Mean (G.M.) of the data. Syntax for GEOMEAN function is : =GEOMEAN(number 1 (number2),...+++++-- Where, Number] number?,...set of numerical values of which Geometric Mean is to be determined. ) BCA 301 : FUNDAMENTAL, MATHEMATICS AND STATISTICS ¢ 133Example : ve fe 2 or D4=2, “OK”, “LOW”) ae be 1r(OR(C4>1 0,D4>2,=2)=TRUE, “OK”, “LOW”) iL jp(OR(D4>=2,C4>10)=TRUE, “OK”, “LOW") ge lF(C4 10, D4>=2, “OK”, “LOW”) assume the value in cell A2 is 0 and the value in B2 is 1%. If the value in A2 is equal to 0 and the value in B2 is greater than 1%, then the output of the function should be OK. Otherwise, the output of the function should be REBAL. Which of the following IF functions will provide an accurate result? 4. =IF(A2=0, B2>1%, “OK”, “REBAL”) b,1)=TRUE, “OK”, “REBAL”) ¢..01)=TRUE, “OK”, “REBAL") d. Both a and c are correct. Il) State weather following functions are true or false - I 1 “The SUM function calculates the total of values contained in two or more cells. ‘The AVERAGE function CANNOT calculate the arithmetic mean or average of values in a ran, The MAX function identifies the highest value in a range. The IF function only evaluates a condition if the condition is true. The logical test evaluates true or false. The median function finds the midpoint value or a list. The MIN function finds the lowest value in a list. BCA 301 : FUNDAMENTAL MATHEMATICS AND. STATISTICS ¢ 141D Lb ideo, sd 6, 9.6 10. Ud 120° 13.0 Ma 1S.e 1B Ide 19.4 Wd We 2b AAA DS. 1) 1. True 2. False 3. True 4, False 5. True 6. True 7. True ane 142 ¢ PRASHANT PUBLICATIONS