Mathematical & Trigonometric Functions

Abs() :This function is used to return the absolute value of a number.
Syntax : Abs(number)
Example : =Abs(A1) , Abs(-54)
Result : 5 54

Sqrt() : This function is used to return the square root of a number.
Syntax : Sqrt(number)
Example : =Sqrt(A5) , Sqrt(D1*5)
Result : 5 10

[hidepost=1]

Round(): This function is used to round a number to a specified number of digits.
Syntax : Round(number, number of digits)
Example : =Round(5.1583,2) , round(B2,1)
Result : 5.16 5.5

MOD() : This function is used to the remainder after a number is divided by a divisor.
Syntax : Mod(number, divisor)
Mod(9/4) Mod(A2,A3) Mod(A2,6)
Result : 1 5 4

Power() : This function is used to return the result of a number raised to a power.
Syntax : Power(number1,number2)
Example : =Power(D3,D4) , Power(A1,3)
Result : 64 125

Int() : This function is used to return the round a number down to the nearest integer.
Syntax : Int(number)
Example : =Int(A1) , Int(14.4324)
Result : 5 14
Note : It does not round the number.

Exp() : This function is used to return e raised to the power of a given number.
Syntax : Exp(number)
Example : =Exp(A1) , Exp(4)
Result : 148.4132 54.59815

Practice Problem

The following example calculates the gross salary which is the total of basic+HRA+25% of basic as DA(C3*25/100). Income tax is 30% ((F3*30/100)of gross pay which is deducted from gross pay to get net pay(F3-G3).

Logical Functions

If() : This function is used to return one value if a condition is true then it returns TRUE otherwise returns FALSE.
Syntax : If(condition, value1, value2)
Example : =If(A1>A2,”Pass”,”Fail”> =If(A1>75,Grade A”,” Grade B”)
Result : Fail Grade B

AND() : This function is used to return True if all the arguments are True, otherwise returns false if any argument is false.
Syntax : And(Logical 1, Logical 2)
Example : =AND(A1,A2) AND(5,5)
Result : false True

Or() : This function is used to return true if any argument is true otherwise returns false. If all arguments are false.
Syntax : OR(Logical 1, Logical 2)
Example : =OR(A1, A2) =OR(5,6)
Result : True True

NOT() : This function is used to reverse the logic of its argument, returns False for a True argument and True for a False argument.
Syntax: NOT(Logical)
Example: =NOT(A1) =NOT(6)
See the following example, here Grade is added based on the following condition
=IF ((H4>10000),”Grade A”,” Grade B”)

Text Functions

LEFT() : This function is used to return the leftmost characters from the text.
Syntax : Left(text, number characters)
Example : =Left(A1,3) =Left(“education”,4)
(if the value of A1 is computer)
Result : com educ

Len() : This function is used to return the number of characters in a text string.
Syntax : Len(text)
Example : =Len(A1) =Len(“educa”)
(if the value of A1 is computer)
Result : 8 5

Lower() : This function is used to convert all uppercase characters in a text to lower case.
Syntax : Lower(text)
Example : =Lower(A1) =Lower(“EDUCATION”)
(if the value of A1 is COMPUTER)
Computer education

Upper() : This function is used to convert all lowercase characters in a text to upper case.
Syntax : Upper(text)
Example : =Upper(A1) =Upper(“education”)
(if the value of A1 is computer)
COMPUTER EDUCATION

Rept() : This function is used to repeat text a given number of times.
Syntax : Rept(text, number-times)
Example : =Rept(A1,20) =Rept(“E”,5)
(if the value of A1 is c)
cccccccccccccccccccc EEEEE

Right() : This function is used to return the rightmost character from a text value.
Syntax : Right(text, num-chars)
Example : =Right(A1,3) =right(“EDUCATION”,4)
(if the value of A1 is computer)
Ter TION

Concatenation() : This function is used to join several text strings into one text strings.
Syntax : Concatenation(text1,text2)
Example : =concatenation(A1,A2) Concatenation(“edu”,”cation”)
(if the value of A1 is computer and B1 is science)
Computerscience education

Auto Fill

Ms Excel provides you a unique feature that is AutoFill. The Autofill function fills the cells automatically. It is used to quickly fill cells with repetitive or sequential data such as sequential numbers, chronological dates or repeated text. Excel automatically fills in times, the days of the week or months of the year, years, and other types of series. Days of the week and months of the year fill in a similar fashion. The following demonstrates filling the days of the week:

• Click on cell A1.
• Type Sun
• Click on cell B1.
• Type Sunday.
• Highlight cells A1 to B1.
• Notice the small black square in the lower right corner of the highlighted area. This is called the Fill Handle.
• Grab the Fill Handle and drag with your mouse to fill cell A1 to B24. Note how the days of the week fill the cells in a series. Also, notice that the Auto Fill Options icon appears.

• Click the Auto Fill Options icon.
• Choose the Copy Cells radio button. The entry in cells A1 and B1 are copied to all the cells highlighted.
• Click the Auto Fill Options icon again.
• Choose the Fill Series radio button. The cells fill as a series from Sunday to Saturday again.
• Click the Auto Fill Options icon again.
• Choose the Fill without Formatting radio button. The cells fill as a series from Sunday to Saturday, but the entries are not bolded.
• Click the Auto Fill Options icon again.
• Choose the Fill Weekdays radio button. The cells fill as a series from Monday to Friday.

If you want to autofill a column with cells displaying the same number or date you must enter identical data to two adjacent cells in a column. Highlight the two cells and drag the handle of the selection with the mouse.

Auto Filling Functions

Autofill can also be used to copy functions. In the example below, column A and column B each contain lists of numbers and column C contains the sums of columns A and B for each row. The function in cell C2 would be “=SUM(A2:B2)”. This function can then be copied to the remaining cells of column C by activating cell C2 and dragging the handle down to fill in the remaining cells. The autofill feature will automatically update the row numbers as shown below if the cells are reference relatively.

Series Dialog Box

The series dialog box offers options for generating a series. The steps to generate a series are:

• Enter the initial values.
• Select the range of cells that need to be filled.
• Select the Fill option from the Edit menu.
• Select the Series option from the Fill submenu. The Series dialog box is displayed.
• Select the appropriate options and click the OK button.

The Series dialog box offers the following options:

Series in: Rows or Columns
Excel will guess the correct orientation, depending upon the range selected. If you have selected a single cell, you must indicate in which direction you want the series to be placed across a row or down a column.

Type Linear
This option creates a straight linear progression, starting at the current value of the cell and incrementing each cell in the range by the number entered in the step value. For example, if you have entered 3 in the first cell and the step value is 3, the numbers will incremented by 3, i.e., 3, 6, 9, and so on.

Type growth
This option grow the data by the number entered in the step value box using simple multiplication. If you start with a value of 1 and grow the data by a step value of 3, the series will be 1, 3 , 9, 27, and so on.

Type Date
This allows you to enter a series of dates a specific number of days apart. For example, if you enter 9/30/99 in the first cell and choose Monthly, the data series will be read 9/30/99, 10/30/99, 11/30/99, and so on.

Type AutoFill
This option allows you to enter a series of numbers and have Excel determine the relationship between them in order to fill the remaining cells in the series. For example, if you enter 10 in the first cell and 20 in the next, Excel will fill the remaining cell with 30, 40, 50, and so on.

Step Value
The Step Value is used to determine the increment Value for a linear series or date series and multiplication rate for a growth series.

Stop Value
Stop value is the maximum value for the series.

Trend
It is available only in conjunction with Linear or Growth series. In this case Excel analyzes the data selected and determines what the trend line for the data should be, ignoring any step value and replacing the current values of the cells with the calculated series.

Note : Both step value and stop value are grayed out when you choose the Trend option

Auto Sum Icon

The AutoSum icon on the Standard toolbar automatically adds a column of numbers. The following illustrates the SUM function:

• Go to cell F1.
• Type 6. Press Enter.
• Type 6. Press Enter.
• Type 6. Press Enter.
• Click the AutoSum button, which is located on the Standard toolbar.
  F1 to F3 should now be highlighted.
• Press Enter. Cells F1 through F3 are added.

Automatic Calculation

If you have automatic calculation turned on, Microsoft Excel recalculates the worksheet as you change cell entries. You can check to make sure automatic calculation is turned on.

[/hidepost]

Like what you read?

If so, please join over 28,000 people who receive our exclusive weekly newsletter and computer tips, and get FREE COPIES of 5 eBooks we created, as our gift to you for subscribing. Just enter your name and email below: