MS OFFICE Excel Formulas

 

FORMULAS

 

FUNCTION&LIBRARY

INSERT FNCTION  (SHIFT+F3)-> edit the formula in current cell by choosing function and editing the arguments

£ AUTOSUM-> total ,average, count, maximum, minimum

RECENTLY USED-> browse and select from a list of recently used function

Text

code-> return a numeric code for a first character in the text string, in the character  EX:(Code A=65,Code a=97)

concatenate->joins several text sting in to one text string EX:(Text-a,p,p,l,e = apple)

dollar->convert to number in text , using current format EX:( Number 45 = Rs 45.00)

left->return the specify number of character from the start of text string (Text-aravind,num-of-characters- 4 = arav)

length->return the number of characters in a text string(Text - aravind =7)

lower->convert all letters in a text string to lower case (Text - ARAVIND =aravind)

mid->return the character from the middle of a text string, given a starting position with length (Text - dhineshkumar,start-num-8,num-of-chars-5=kumar)

replace->replace part of the text string with a different text string(Oldtext-Rohith,starting-num-3,no-of-chars-1,new text = Rokith)

repeat-> repeat a text given number of times use repeat to fill a cell with a  number of instance of a text string(Text-Siva,no-of-times-2=SivaSiva)

right->return the specified number of characters from the end of text string(Text-Arjunkumar,num-of-chars-5=kumar)

upper->convert a text string to all upper case letter

(Text-vishnu =VISHNU)

 

DATE&TIME

 

Now->return the current date and time formatted date and time(current date and current time ex:21/08/2023.13.22)

Today->return the current date formatted as a date(current date ex:21/08/2023)

Time->covert as hour,minute,second given as a number to an excel serial number formatted with a time format(hour-5,minute-34 ,second-25 = 5:34am)

Year->return the year of date with a year format(show an current year = 2023)

defined name

Name manager (Ctrl+f3)->create,delete,edit and find all names used in the workbook

Define name->name cell so that you can refer to them in formulas by that name

Use in formula->choose a name in this workbook and insert in to current formula

Create from selection(Ctrl+Shift+f3)->Automatically generates name from the selected text

formula aditing

Trace precedents-> show arrow that indicate what cells affect the value of the currently selected cell

Trace dependents-> show arrow that indicate what cells affected the value of the currently selected cell

Remove arrow->remove the arrow drawn by trace precedents or trace dependents

Show formula->display the formula in each cell instead of the resulting value

Error checking->check for common errors that occurs in formula

Evaluate formula->the evaluate formula dialog box debug a formula by evaluating each part of the formula individually

Watch window-> the values is displayed separate window that remains visible , regardless what area of the workbook  

calculations

Calculation option->specifies when formulas are calculated

Calculate sheet-> calculate the entire workbook now

Calculate now->calculate the current sheet now