Kursy walut

EXCEL MATH FUNCTIONS A Handbook on MS-Excel Math Functions Mohit Khurana, CFA http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Contents Sum() .............................................................................................................................................................3 SumIf() ...........................................................................................................................................................5 Average() .......................................................................................................................................................7 Product() ........................................................................................................................................................8 Count()...........................................................................................................................................................9 Counta().......................................................................................................................................................10 Rand()..........................................................................................................................................................11 RandBetween()............................................................................................................................................12 Odd()............................................................................................................................................................13 Even() ..........................................................................................................................................................14 Int() ..............................................................................................................................................................15 Ceiling() .......................................................................................................................................................16 Floor() ..........................................................................................................................................................17 Sign() ...........................................................................................................................................................18 ABS() ...........................................................................................................................................................19 SQRT() ........................................................................................................................................................20 Fact() ...........................................................................................................................................................21 Exp() ............................................................................................................................................................22 SumSQ()......................................................................................................................................................23 http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Sum() This function is used to find the sum or total of values in a range. Syntax: = Sum (Number1, Number2, Range1,.....) Number1, Number2, Range1 = Numbers/Ranges for which the total is to be calculated Example: The function in the above image calculates the total (also called sum) for the range - C2:C6. The result of the function comes out to be 15,260. Tip: Sum() function proves to be much secure and robust than using ‘+’ symbol for addition as this function can bypass any errors (such as #NA, #Value) in the arguments entered. Let us look at the following example: In the below image, I have used ‘+’ sign to add up the 5 cells (C2, C3, C4, C5 and C6). The cell C4 contains an error and this causes the addition of the 5 cells to give an error. Also, if any of the cells in the given range contains a text, the result will again be an error. http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Now, if you look at the image below, in which I have used Sum() on the same data and same range, the result of the addition does not give any error. http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA SumIf() Sumif() is a variant of Sum() function and it calculates the sum of a given range, of only those cells (within the range) which meet the criteria mentioned in the function. Syntax: = SumIf (Range, Criteria,[SumRange]) Range = it specifies the range of cells on which the criterion is to be tested. Also, in case the SumRange is not provided in the function, the sum is also calculated on this range SumRange = this range specifies the cells on which the sum is calculated. This is optional. Note: The Sumif() has 2 formats - one with the Sumrange and one without it. In the first format, the range on which the criterion is to be tested is same as the one for which the sum is to be calculated. In the second format, the 2 ranges are different. It will become much easier to understand through the below examples. Example: Example 1: In the below image, the criteria for calculating the sum is - "the cell value should be more than or equal to 15,000" The sumif() function checks the range D2:D6 for the cells which contain value more than or equal to 15,000 and then add up those cells which meet the criteria. Note that the condition has been checked on the same range (i.e. - D2:D6) on which the sum is calculated. Hence, the SumRange argument is not required in this function. Example 2: Let us now assume that we need to calculate the total salary of all the 'Analysts' as given in the above image. For this, we will use the second format of SumIf() function. http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA In the image above, the cells which meet the condition, i.e. cells which have 'Analyst' in the corresponding range, gets added up in the total. The cells highlighted with a tick mark are the ones which meet the criteria. http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Average() This function is used to find the mean or average of values in a range. Syntax: = Average(Number1, Number2, Range1,.....) Number1, Number2, Range1 = Numbers/Ranges for which the average is to be calculated Example: The function in the above image calculates the average (also called mean) for the range - C2:C6. The result of the function comes out to be 15,260. http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Product() This function is used to calculate the product of values in the given range Syntax: = Product(Number1, Number2, Range1,.....) Number1, Number2, Range1 = Number/Ranges for which the product is to be calculated Example: The function in the above image performs the multiplication of the numbers in the range C2:C6. The result of the function comes out to be 34,020. Though I have rarely come across any spreadsheet that uses Product() function, but similar to Sum() function, this function is also more stable than using the multiplication sign (*). http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Count() This function is used to count the number of cells which contain numeric values. It does not count the cells that are empty or contain any alpha-numeric data. Syntax: = Count (Cell1, Cell2, Range1,....) Cell1, Cell2, Range1 = Cells/Ranges for which the count is to be calculated Example: For the above image, various usages of Count() function will give the following results: • • • • • =Count(B1:B6) =Count(B2:B6) =Count(A1:B6) =Count(A1:B8) =Count(B1,B2) - result will be - 5 - result will be - 5 - result will be - 5 - result will be - 6 - result will be - 1 {cell B1 is not counted} {all cells in the range are numeric} {cells with text are ignored} {blank cells are also ignored} http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Counta() Similar to Count(), the function Counta() is used to count the number of cells, but with this function you can count the number of cells that contain alpha-numeric data, i.e. - cells that contain numbers and alphabets (any text). Syntax: = Count (Cell1, Cell2, Range1,....) Cell1, Cell2, Range1 = Cells/Ranges for which the count is to be calculated Example: For the above image, various usages of Count() function will give the following results: • • • • • =Counta(B1:B6) =Counta(B2:B6) =Counta(A1:B6) =Counta(A1:B8) =Counta(B1,B2) - result will be - 6 - result will be - 5 - result will be - 12 - result will be - 14 - result will be - 2 The counta() function also ignores the blank cells from the total count. http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Rand() This function returns an evenly distributed random number between 0 and 1 Syntax: = Rand () Note: This function is a volatile function, i.e. this function is re-calculated whenever the worksheet gets updated. A heavy use of this function in a workbook can make your workbook calculations slow. In Excel 2007, there is also an advanced version of this function: Randbetween() http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA RandBetween() This function returns a random number between a given range of integer numbers Syntax: = Randbetween (Bottom_number, Top_number) Bottom_number: The smallest number that the function will return Top_number: The largest number that the function will return Example: = Randbetween (3,100) Note: This function is a volatile function, i.e. this function is re-calculated whenever the worksheet gets updated. A heavy use of this function in a workbook can make your workbook calculations slow http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Odd() This function returns the nearest ODD number which is - greater than the original number if the original number is positive and lesser than the original number if original number is negative Syntax: = Odd (Number) Number = is the number which is required to be rounded off to nearest odd number Example: • • • • • • • • = Odd (2) - result will be: 3 = Odd (3) - result will be: 3 = Odd (5) - result will be: 5 = Odd (8) - result will be: 9 = Odd (-2) - result will be: -3 = Odd (-3) - result will be: -3 = Odd (-5) - result will be: -5 = Odd (-8) - result will be: -9 http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Even() This function returns the nearest EVEN number which is - greater than the original number if the original number is positive and lesser than the original number if original number is negative Syntax: = Even (Number) Number = is the number which is required to be rounded off to nearest even number Example: • • • • • • • • = Even (2) - result will be: 2 = Even (3) - result will be: 4 = Even (5) - result will be: 6 = Even (8) - result will be: 8 = Even (-2) - result will be: -2 = Even (-3) - result will be: -4 = Even (-5) - result will be: -6 = Even (-8) - result will be: -8 http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Int() This function rounds off the number to nearest integer less than the original number Syntax: = Int (Number) Number = The number which is to be rounded off to nearest integer Example: • • • • = Int (5.5) - result will be - 5.0 = Int (5.0) - result will be - 5.0 = Int (5.1) - result will be - 5.0 = Int (5.9) - result will be - 5.0 http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Ceiling() This function is used to convert a number with decimal points to an integer greater than the number. When a number is input in this function, it adds the decimal points enough to convert the number to an integer more than the number itself Syntax: = Ceiling (Number, Significance) Number = the number that is to be rounded off Significance = multiple to which the number is to be rounded off Example: • • • • • • =Ceiling (26.2315 , 1) =Ceiling (26.2315 , 2) =Ceiling (26.2315 , 5) =Ceiling (27.2315 , 1) =Ceiling (27.2315 , 1) =Ceiling (27.2315 , 1) result will be - 27 result will be - 28 result will be - 30 result will be - 28 result will be - 28 result will be - 30 The ceiling function rounds off the number up to the nearest integer which is divisible by the significance value input in the function. Example - if I have to round up 26.33 to an integer, the result will be 27. But, if I want to round up the number, but to that integer which should also be divisible by 2, then my function will be =Ceiling(26.33,) The result of this function will be - 28 In the examples above I have used 26.2315 and 27.2315 to show the use of 'Significance' value. http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Floor() This function is used to convert a number with decimal points to an integer lower that the number. When a number is input to this function, it removes the decimal part of the number and returns only the integer value of that number Syntax: = Floor (Number, Significance) Number = the number that is to be rounded off to nearest integer less than the number Significance = multiple to which the number is to be rounded off to Example: • =Floor(28.2315 , 1) - result will be – 28 • =Floor(28.2315 , 2) - result will be – 28 • =Floor(28.2315 , 5) - result will be – 25 • =Floor(27.2315 , 1) - result will be – 27 • =Floor(27.2315 , 2) - result will be – 26 • =Floor(27.2315 , 5) - result will be – 25 The floor function rounds off the number down to the nearest integer which is divisible by the Significance value input in the function. Example, if I have to round down 29.33 to an integer, the result will be 29. But, if I want to round of the number down, but that number should also be divisible by 2, then my formula would be: =Floor(29.33,2) The result of the above formula will be - 28 In the examples above, I have used 28.2315 and 27.2315 to show what does ‘Significance’ values stands for. http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Sign() This function is used determine the sign (negative or positive) of a given number. It returns -1 for negative number, 1 for a positive number and, 0 if the number provided is 0 Syntax: = Sign (Number) Number: Number for which sign is to be determined Example: • • • • = Sign (-21) – result will be: -1 = Sign (-1) – result will be: -1 = Sign (21) – result will be: 1 = Sign (0) – result will be: 0 http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA ABS() This function returns the absolute value of the number Syntax: = ABS (Number) Number = The number for which the absolute value is required Example: • • = ABS (20) - result will be – 20 = ABS (-20) - result will be – 20 http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA SQRT() This is a simple function which is used to convert find a square root of a number Syntax: = SQRT (Number) Number_text: Number for which the square root is to be returned Example: • • • = SQRT (4) - result will be - 2 = SQRT (49) - result will be – 7 = SQRT (11) - result will be – 121 http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Fact() This function returns the factorial of a number Syntax: = Fact (Number) Number = The number for which the factorial is required Example: • • • • • • = Fact (0) - result will be - 1 = Fact (1) - result will be - 1 = Fact (2) - result will be - 2 = Fact (3) - result will be - 6 = Fact (4) - result will be - 24 = Fact (5) - result will be - 120 http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA Exp() This function returns the result of 'e' raised to the power a given number Syntax: = Exp (Number) Number = is the number which is used as x in the equation e^x Example: • • • • = Exp (0) - result will be: 1.0000 = Exp (1) - result will be: 2.7183 = Exp (2) - result will be: 7.3891 = Exp (3) - result will be: 20.0855 http://ExcelMatic.blogspot.com Excel Math Functions - Mohit Khurana, CFA SumSQ() This function calculates the sum of squares of the numbers provided as arguments to the function. The arguments provided to this function can be numbers, range of numbers, arrays, etc. Syntax: = SumSQ (Number1, Number2, Number…) Number1, Number2, Number: These are numbers for which sum of squares is to be calculated Example: • = SumSQ(1, 2, 3) - result will be - 14 This function does calculation as – ((1*1) + (2*2) + (3*3)) http://ExcelMatic.blogspot.com