SumSQ() This function calculates the sum of squares of the numbers provided as arguments to the function. The arguments provided to this fun...
Read More
Showing posts with label Math Functions. Show all posts
Showing posts with label Math Functions. Show all posts
Function RandBetween()
RandBetween() This function returns a random number between a given range of integer numbers Syntax : = Randbetween (Bottom_number, Top_numb...
Read More
Function Rand()
Rand () This function returns an evenly distributed random number between 0 and 1 Syntax : = Rand () Note: This function is a volatile func...
Read More
Function Odd()
Odd() This function returns the nearest ODD number which is - greater than the original number if the original number is positive and lesser...
Read More
Function Sign()
Sign() This function is used determine the sign (negative or positive) of a given number. It returns -1 for negative number, 1 for a positiv...
Read More
Function SQRT()
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 wh...
Read More
Function Fact()
Fact() This function returns the factorial of a number Syntax : = Fact (Number) Number = The number for which the factorial is required Exam...
Read More
Function Int()
Int() This function rounds off the numnber to nearest integer less than the original number Syntax : = Int (Number) Number = The number whic...
Read More
Function Exp()
Exp() This function returns the result of ' e ' raised to the power a given number Syntax : = Exp (Number) Number = is the number wh...
Read More
Function Even()
Even() This function returns the nearest EVEN number which is - greater than the original number if the original number is positive and less...
Read More
Function ABS()
ABS() This function returns the absolute value of the number Syntax : = ABS (Number) Number = The number for which the absolute value is req...
Read More
Functions Count() and Counta()
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 conta...
Read More
Function Ceiling()
Ceiling () This functions is used to convert a number with decimal points to an ineger greater than the number. When a number is input in th...
Read More
Function Sumif()
SumIf () Sumif() is a variant of Sum() function and it calcuates the sum of a given range, of only those cells (within the range) which meet...
Read More
Function Product()
Product() This function is used to calculate the product of values in the given range Syntax : = Product(Number1, Number2, Range1,.....) Num...
Read More
Subscribe to:
Posts
(
Atom
)
Showing posts with label Math Functions. Show all posts
Showing posts with label Math Functions. Show all posts
Sunday, June 21, 2009
Function SumSQ()
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)) Read more...
Function RandBetween()
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
Function Rand()
This function returns an evenly distributed random number between 0 and 1
Syntax:
=
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()
Function Odd()
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
Function Sign()
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
Function SQRT()
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 willl be - 2
- = SQRT (49) - result willl be - 7
- = SQRT (11) - result willl be - 121
Saturday, June 13, 2009
Function Fact()
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
Function Int()
Int()
This function rounds off the numnber 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
Function Exp()
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
Function Even()
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
Function ABS()
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
Wednesday, May 27, 2009
Functions Count() and Counta()
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:
.PNG)
For the above image, various usages of Count() function will give the following results:
- =Count(B1:B6) - result will be - 5 {cell B1 is not counted}
- =Count(B2:B6) - result will be - 5 {all cells in the range are numeric}
- =Count(A1:B6) - result will be - 5 {cells with text are ignored}
- =Count(A1:B8) - result will be - 6 {blank cells are also ignored}
- =Count(B1,B2) - result will be - 1
Counta()
Similar to Count(), the function Counta() is ised 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) - result will be - 6
- =Counta(B2:B6) - result will be - 5
- =Counta(A1:B6) - result will be - 12
- =Counta(A1:B8) - result will be - 14
- =Counta(B1,B2) - result will be - 2
The counta() function also ignores the blank cells from the total count.
Tuesday, May 26, 2009
Function Ceiling()
Ceiling ()
This functions is used to convert a number with decimal points to an ineger 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) result will be - 27
- =Ceiling (26.2315 , 2) result will be - 28
- =Ceiling (26.2315 , 5) result will be - 30
- =Ceiling (27.2315 , 1) result will be - 28
- =Ceiling (27.2315 , 1) result will be - 28
- =Ceiling (27.2315 , 1) 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, th 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 wil 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.
Function Sumif()
SumIf()
Sumif() is a variant of Sum() function and it calcuates 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 creterion 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 calcuated. 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.
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.
Read more...
Sunday, May 24, 2009
Function Product()
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 abovce 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 Similar() function, this function is also more stable than using the multiplication sign (*).
Read more...
Subscribe to:
Posts (Atom)