DBMS = Database Management System (Microsoft Excel)
RDBMS = Relational Database Management System (Mysql, Foxpro, Access etc)
M.S Excel 2007 Key Points
Bill Gates is the author of Microsoft.
“. xls” Extension name of Excel 93 to 2003 version
“. xlsx ” Extension name of Excel 2007, 2010 & 2016 version
Working area of m.s excel is worksheet or sheet.
Electronic spreadsheet
An electronic spreadsheet application accepts data values and makes relationship between the data
value in a tabular formula constiting of rows and columns.
1 st electronic spread sheet is "visicale".
Now most popular is ms excel 2007 & lotus 1,2,3
Worksheet
A work sheet or a sheet is a sheet made up of horizontal rows and vertical columns.
It is the working areas of M. S Excel 2007 where we can make any planning and project or financial
documents of an organisation.
10, 48,576 rows and 16,384 columns in a worksheet.
Columns are name by (A, B, C...)
Rows are name by (1, 2, 3...)
16,384 columns name is XFD.
Workbook
Workbook is the collection of many worksheets.
Formula
Function
Functions are pre defined formula that takes value or values perform complex calculation by using a
specific value in a particular order to give a result that is called function.
Example: -
Sum (+) average, max, min
Click; - A single left click on items or tools
Double click- Double left click on tools
Right click- A single right click on tools
Position- Position or place the mouse pointer on an item
Drag - Point to an item click and hold the mouse left bottom as you move the mouse pointer to a new
location then relies the mouse bottom and the complete axon is known as drag.
How to open m.s excel 2007?
Go to Start bottom Go to all program Go to M. S office Select M. S excel 2007 then click.
Cell range
A selection of more than one adjacent cell is called cell range.
Cell name = Column Name Rows Number
Cell range name = First Cell Name: Last Cell Name
Entire Column Address = ColumnName:ColumnName
G:G C:F
Entire Row Address = RowNumber: RowNumber
4:4 3:6
When a user enter some data in a cell, The cell shows the result. But the formula bar shows entered data.
What shows in the cell = cell value
What shows in formula bar = cell data
Data Entry in Excel
To create line break inside the Excel cell, double click on the cell and set the keyboard cursor at required
position then press Alt +Enter
Alt + enter = meaning Press the alt key until click enter
AltH = meaning Click alt key then click H key
When we enter “=25+10” inside the cell, it shows the result as “35”. Here “=25+10” will be shown in the
formula bar and “35” will be shown in cell. So what shows in formula bar is “call data” and what shows in
cell after clicking enter key is called “cell value”.
Image of Excel Worksheet
Quick Access Tool Bar
1 Save
2 Undo
3 Redo
1 Save
We can use this tool to save a file. It is the shortcut to save. We can use CTRL+S instead.
2 Undo
This tool is undone the action taken by the user in Microsoft Word. Whenever we have done some mistake,
we can click this toll to undone it.
3 Redo
This tool is redone the undone action taken by the user earlier in Microsoft Word. Whenever we undone any
action and need to get the earlier action back again then we can use “redo” tool.
1. Office Button: -
This button is used to open, save or print and to see everything else you can edit/can do with your
document also.
Step: - Go to office buttonchose that one from the given option, which you want to do.
2. New: -
This button is used to create new page with new window.
If you want create a new page then….
Step: - Go to office buttonchose new button from the given option Click on create button from
the bottom.
3. Open: -
This button is used to open the files or folders which already exist in the window explorer.
Step: - Go to office button chose open button from the given option Type a folder name, which you
want to openclick on open button.
4. Save: -
This button is used save the documents.
Step: - Go to office buttonClick on save chose location (from left side) Give file nameclick on
save
5. Save as: -
This button is used to save again, those files already exist in the windows explorer or simply going
to says that resave the document.
Step: - Open the file or folder which you want to save again (after changes) Click on Save as
optionchose location (from left side) Rename file nameClick on save.
6. Print: -
This is a way in which soft copy can be converted to hard copy.
Step: -Open those pages which you want to printgo to office buttonclick on print tool from the
print preview tabClick pages ratio buttontype the page range in the pages box click dropdown
button printer namechose a printer click ok command button.
7. Prepare: -
By using this option we can set the password to the document for protection.
(Encryption of document)
8. Send: -
By using this potion we can send the document to others by using E-mail or Internet fax.
9. Publish: - By using this option we can published the document on the internet, share the document
with other user for group work and create document work space.
(*Work Space  Document to internet  link  net  share  / share )
10. Close: -
This option is used to close the document window.
11. Word option: -
This option is used to configure word setting.
12. Exit Word: -
This option is used to exit from the word application window.
Home tab
There are 5 sections in home tab.
1-Clipboard Section
2-Font Section
3-Paragraph Section
4-Style Section
5-Editing Section
Clipboard Section
1-Paste 2-Cut
3- Copy 4- Format Painter
Copy: -By the help of these tools we can copy that documents and characters.
Cut: - to move that documents and characters to another place.
Cut/Copy Step: - Select the text ---> go to Clipboard section ---> Click Cut/Copy tool ---> Click the place of
page where you want to paste the text ---> Click Paste tool.
Format Painter: - It applies the format & Styles of selected text to the Target Text.
Font Section: -
1- Font family 8- Border
2- Font Size 9- Fill Color
3- Grow font 10- Font Color
4- Shrink Font
5- Bold
6- Italic
7- Underline
1-Font Family
This tool is used to change the style of selected text.
Step- Select the Text Go To Font Section Click on Drop Down Of Font Family Select a Font Name
from the List
2-Font Size
This tool is used to set the text size according to the number shown in dropdown option.
Step: - select the text go to font sectionclick on dropdown of font sizeselect a size from the list.
3-Grow font
This is used to increase the size of the selected text.
Step: - select the texts go to font section click on grow font tool.
4-shrink font
This is used to decrease the size of the selected text.
Step- select the text go to font section click on shrink font tool.
5-Bold
This tool is used to make the selected text thicker.
Step: - select the texts go to font section click on bold tool.
6-Italic size
This tool is used to bands the letters to the right.
Step- select the text go to font section click on italic tool.
7-Underline
This tool is used to put underline to the text with its styles and different colors
Step to put underline: - select the texts go to font section click on underline tool.
Step to put underline style: - select the texts go to font section click on dropdown button of underline
tool then choose an underline Style.
Step to put underline color: - select the texts go to font section click on dropdown button of underline
tool go to underline colorchoose an underline color.
Step to withdraw underline: - select the underlined texts go to font section click on underline tool.
8. Border
This tool is used to customize the borders of the selected cells or text.
Step- Select the text Go to home tab on Font sectionclick on boarder tool select a border style
apply
9- Fill color
This tool is used to highlight the background of text with different color.
Step – select the text go to font section click on drop down button of text highlight select a color
from the listSelect the Target Text.
10- Font Color
This tool is used to apply the color to the text.
Step- select the text go to font section click on drop down button of font color select a color from the
list.
1. Alignment Section: -
1. Top align
Align text to the top of the cell.
Step: - Select the cell Go to home tabGo to Alignment Section Click on top align tool
2. Middle align
Align text so that it is centered between the top and bottom of the cell.
Step: - Select the cell Go to home tabGo to Alignment Section Click on middle align tool
3. Bottom align
Align text to the bottom of the cell.
Step: - Select the cell Go to home tabGo to Alignment Section Click on bottom align tool
4. Orientation
Through this tool, text can be rotate diagonally and vertically.
Step: - Select the cell Go to home tab Go to Alignment Section Click on Orientation tool Chose a
orientation style option.
5. Wrap text
This tool is used to display the contents (text) in a many lines within a cell.
Step: - Select the cell Go to home tabGo to Alignment Section Click on wrap text tool.
6. Align text left
Align text left of left alignment is a way of arranging the text from left of the margin.
Step: - Select the cell Go to home tabGo to Alignment Section Click on align text left tool
7. Center align
Center alignment is way of arranging the text in to center one i.e. to say the text will be aligning in
center area of the page.
Step: - Select the cell Go to home tabGo to Alignment Section Click on Central align tool
8. Align text right
Align text right or right alignment is a way of arranging the text from right of the margin.
Step: - Select the cell Go to home tabGo to Alignment Section Click on top align text right tool
9. Decrease indent
The decrease indent bottom reverses of increase indent operation.
10. Increase indent
The distance increase between the current paragraph and left page margin.
11. Merge and center
Convert more cells in to single cell.
Use CTRL while selection of multiple cell for multiple merging.
Data Entry & formating Rules In Excel
After each entry you should use TAB to navigate to the right side cell & for left side cell use Shift + Tab.
Never use arrow keys as much as possible. Use Enter key to bring down the selector & shift + enter key for
bring it up. It will saves your time while data entry.
Table Heading: Should be Align Center and top.
Serial No: Should be align center and middle.
Name: Should be align left and middle.
Numbers: Should be align center and middle.
Table caption: Should be center and center.
Single Merging: Select 2 or more cell Then click “Merge and center”
Multiple Merging Steps: Select 2 or more cell Press and hold CTRL Key Select other cells to merge
Then click “Merge and center” for multiple merging.
Number Section: -
1. Number Format: -
This tool is used to change the format of the value of a cell as percentage, currency, date…etc.
Step: - Select the cell Go to home Tab Go to number section chose a format from the drop down
button of number format.( Such as Percentage, Long date, Short date…. etc)
2. Accounting Number Format: -
Through this tool, an alternate currency format can be chose.
Step: - Select the cell Go to home Tab Go to number section chose a format for the value from
option
3. Percent Style: -
Value of the cell can be display as percentage.
Step: - Select the cell Go to home Tab Go to number section Click on percent style tool.
4. Comma : -
This tool is used to separate the thousand, of the cell value.
Step: - Select the cell Go to home Tab Go to number sectionClick on Comma tool.
5. Increase Decimal: -
Through this tool, decimal places can be increase by after the decimal point.
Step: - Select the cell Go to home Tab Go to number section Click on Increase Decimal Tool.
6. Decrease Decimal: -
Through this tool, decimal places can be decrease.
Step: - Select the cell Go to home Tab Go to number section Click on Decrease Decimal Tool.
Style Section: -
1) Conditional Formatting: - It shows the value of the cell, according to the condition.
Highlight Cells Rule: -
Greater Than: -
This sub tool is used to show those no. which greater than the user define no.
Step: - Select the cellsGo to Home tab Go to style sectionClick on drop down button of Conditional
Formatting tool Click on highlight cells rule click on “greater than”(a box will be appear) Type a
number in box & select a color right side click on “ok”
Less than: -
This sub tool is used to show those no. which smaller than the user define no.
Step: - Select the cellsGo to Home tab Go to style sectionClick on drop down button of Conditional
Formatting tool Click on highlight cells rule click on “less than”(a box will be appear) Type a number
in box & select a color from right side click on “ok”
Between: -
This sub tool is used to show those no. which lying between the user define two
no.
Step: - Select the cellsGo to Home tab Go to style sectionClick on drop down button of Conditional
Formatting tool Click on highlight cells rule click on “Between”(a box will be appear) Type two
numbers in different boxes & select a color right side click on “ok”
How to copy a Table from one sheet to another
Select the table click copy from clipboard section click a cell from another sheet
Then click the dropdown button of paste tool from clipboard section choose column width radio button
click OK
Then click the dropdown button of paste tool again from clipboard section choose value radio button
click OK
Then click the dropdown button of paste tool from clipboard section choose format radio button click
OK
How to Link a Table data to another Table
For Linking Tables in excel we need at least two tables, one for source data (called Source Table) another for
target data (called Target Table). When we change the data in source table, the data of target table will be
changed.
Steps:
Click on the Cell of the common field of Target Table type “=” sign click the worksheet where source
table is created click that cell from which you want to bring data to the target table click Enter.
How to Insert a picture in a Table
Set the Column width of a column where you want to insert the picture Select the rows of the tableGo
to Home Tab, Cells Section and click the Format Tools Choose Row Height type 60 in it click ok.
Go to Insert tab, Illustration section and click picture select a picture and click insert command button
go to format tab, size section type 1 in height combo box enter resize the picture according to
the cell size.
How to Insert a Chart in a worksheet
Select Table go to Insert Tab, Chart Section Choose a suitable chart category choose a Chart
option.
How to separate First Name, Middle Name & Surname using Text to Column
Select Table go to Insert Tab, Chart Section Choose a suitable chart category choose a Chart
option.
How to remove duplicate values in excel
Select Table go to Insert Tab, Chart Section Choose a suitable chart category choose a Chart
option.
How to validate a data (for Mobile Number Validation)
Select Cells which you want to validate go to Data Tab, Data Tools Section Click the Dropdown List of
Data Validation tool Then Choose the Data Validation option In the data validation window go to
Setting Tab and click the dropdown of “allow” and choose a validation option (Example Whole Number)
Set data as “Between” Set “Minimum” as 1000000000 and Set “Minimum” as 9999999999 Go to
Input Message Tab Type in the “Title” box as Note: Type a Message in “Input Message box” go to
Error Alert Tab Choose Error Style dropdown as “Stop” type a title for the alert message Type an
error Message in the “Error Message” box Click OK.
Data Validation Type and Purpose
Any Value: To Cancel the validation
Whole Number: To Apply validation between two whole number
Decimal: To Apply validation between two decimal number
Date: To Apply validation between two Dates
Time: To Apply validation between two Times
Text Length: To apply validation with no of text allowed to write in a cell (letter & number)
List: To apply a list to validate data. Means we write a list or the range address
of a list in the source box
EXCEL FUNCTION
Date and time function
A
B
C
D
E
F
G
H
1
date
time
hour
minute
second
2
2018
12
23
23/12/2018
9:15:20
9
15
20
3
2017
10
12
12/10/2017
11:55:12
11
55
12
4
2018
4
8
08/04/2018
5
Date()
Its returns the date or serial number of the date.
syntax
=date(yyyy,mm,dd) enter
Ex
=date (2018,12,23) enter
Result
23-dec-2018 (if the cell is in date format)
Or
43457 (if the cell is in number format)
Day()
It returns the day from a day.
Syntax
=day(“mm/dd/yyyy”)enter
=day(cell address of the date)enter
Ex
=day(“12/23/2018”)enter
=day(D3)enter
Result
1. 23
2. 12
Month()
It returns the month from the day.
Syntax
=month(“mm/dd/yyyy”)enter
=month(cell address of the date)enter
Ex
=month(“03/30/2018”)enter
=month(D2)enter
Result
1. 3
2. 12
Year()
It returns the year from the day.
Syntax
=year(“mm/dd/yyyy”)enter
Ex
=year(“03/30/2018”)enter
=year(D2)enter
Result
1. 2018
2. 2018
Datevalue()
It returns the serial number of the date.
Syntax
=datevalue(“mm/dd/yyyy”)enter
Ex
=datevalue(“25/07/1996”)enter
Result
35217
Days360()
It returns the number of days between two date based on 360 days per year this function is
only use to calculate payments.
Syntax
=days360(“start date”,”end date”)enter
=days360a(start date cell address ,end date cell address)enter
Ex
=days360(“3/1/2018”,”3/5/2018”)enter
=days360(D3,D4)enter
Result
1. 120
2. 176
3.
Today()
It returns the current date or serial number of the current date depending on the cell
formatting.
Syntax
=today()enter
Ex
=today()enter
Result
03/30/2018
Or
43189
Weekday()
It returns the day of the week corresponding to a date.
The date is given as integer from 1. Sunday,2. Monday …………..7. Saturday
Syntax
=weekday(cell address of the date)enter
Ex
=weekday(D2)enter
Result
1
Now()
It source the current date and time.
Syntax
=now()enter
Ex
=now()enter
Result
03-04-18 7:48
Time()
It returns the valid time from given hour, minute and second.
Syntax
=time(HH,MM,SS)enter
Ex
=time(9,15,20)enter
=time(F3,G3,H3)enter
Result
1. 9:15 am
2. 11:55 am
Hour()
It returns the hour from a date.
Syntax
=hour(cell address of time)enter
Ex
=hour(E2)enter
Result
9
Minute()
It returns the minute from a given time.
Syntax
=minute(cell address of the time)enter
Ex
=minute(E2)enter
Result
15
Second()
It returns the second from a given time.
Syntax
=second(cell address of time)enter
Ex
=second(E2)enter
Timevalue()
It returns the percentage of the time already covered in that day.
Syntax
=timevalue(“hh:mm:ss”)enter
Ex
=timevalue(“9:15:20”)enter
Result
0.385648148
Text and data function
B
C
1
Kumar
SaHu
2
rout
3
4
21
5
manas
6
7
24
Text and Data Function
Upper()
It converts the text in to “UPPER CASE”.
Syntax
=upper(cell address of the text) enter
Ex
=upper(B2) enter
Result
ROUT
Lower()
It converts the text in to “lower case”.
Syntax
=lower(cell address of the text)enter
Ex
=lower(A2)enter
Result
bikas
proper()
This function converts the text to Capitalize Each Word.
Syntax
=proper(cell address of the text)enter
Ex
=proper(A3) enter
Result
Arun Sharma
Char()
It returns a character specify by its ASCII code.
Syntax
=char(ASCII code)enter
=char(cell address)enter
Ex
=char(65)enter
=char(A5)enter
Result
1. A
2. B
Clean()
It removes the non-printable character of a cell.
Syntax
=clean(cell address of the text)enter
Ex
=clean(B4)enter
Result
21
Len()
It returns the text length of a cell.
Syntax
=len(cell address)enter
Ex
=len(A1)enter
=len(A2)enter
Result
1. 4
2. 5
Left()
It returns the text length of a cell.
Syntax
=left(cell address, no. of character to display)enter
Ex
=left(A2,3)enter
=left(A1,2)enter
Result
1. Bik
2. Al
Right()
It returns the specify number of character from the right side.
Syntax
=right(celladdress, no of character to display)enter
Ex
=right(A1,2)enter
=right(A2,3)enter
Result
1. ok
2. kas
ASCII
American standard code for
information interchange
256 character possible
codingly ASCII.
Mid()
It returns the specify number of character from a specified position.
Syntax
=mid(cell address,specified position, no. of character to show)enter
Ex
=mid(A2,2,3)enter
Result
lok
Trim()
It removes the unwanted space of the text
(it removes the space before the text and after the text )
As you know that the spaces can’t be seen, so spaces are invisible.
Syntax
=trim( cell address of the text) enter
Ex
=trim(B5)enter
Result
Manas
(the additional space is removed)
Concatenate()
It joined the text enter in different cell.
Syntax
=concatenate(cell address,”-“,cell address)enter
Ex
=concatenate(A1,”-“, B1)enter
=concatenate(A2,”-“,B2)enter
Result
1. Alok Kumar
2. Bikas rout
Rept()
This function repeats specify text in a given number of time.
Syntax
=rept(“text to repeat ”,”no of time to repeat”)enter
Ex
=Rept(“jai jagannath”,108)enter
=Rept(capital(A6))enter
Result
1. Jai jagannath……….108
2. JAI JAGANNATH
Value()
When a number is returns as text in a cell then this function convert that numerical text value as number.
Syntax
=value(cell address)enter
Ex
=value(B7)enter
Result
24
Find()
We can find a particular text position from the text of a cell.
Syntax
=find(“text to find “,”Cell address where to find the text “,”start no.”)enter
Ex
=find(“best friend”,A7,1)enter
Result
10,24,48
Math or trigonometric function
A
B
C
1
-51
2.5
25
2
-9
1.4
4.76
3
24
3
5.74
4
5
4
95
Abs()
Returns the absolute value of the number.
Syntax
=abs(cell address)enter
Ex
=Abs(A1)enter
=Abs9A2)enter
Result
1. 51
2. 9
Even()
It returns the number by rounding-up to its nearest even integer.
Syntax
=Even9cell address)enter
Ex
=Even(B1)enter
=Even(B2)enter
=even(B3)enter
Result
1. 4
2. 2
3. 4
Odd()
It returns the number by the rounding to it’s nearest to its odd number.
Syntax
=odd(cell address)enter
Ex
=odd(B1)enter
=odd(B2)enter
=odd(B3)enter
Result
1. 3
2. 3
3. 3
Int()
It returns the number by rounding-down to its nearer integer.
Or
It returns the integer of a given number.
Syntax
=Int(cell address)enter
Ex
=Int(B1)enter
=Int(C3)enter
Result
1. 2
2. 5
Round()
It returns the number
By rounding of to the specify digit if the decimal value is greater the and equal
to 0.5
by rounding down to the specify digit if the decimal value is less then 0.5
syntax
=Round(cell address, no of decimal digit to show)enter
Ex
=Round(B1,0)enter
=Round(C2,1)enter
Result
1. 2
2. 4.7
Roundup()
It returns the number by rounding of to its left most digit with adding 1.
Syntax
=Roundup(cell address,no of decimal digit to show)enter
Ex
=Roundup(B1,0)enter
=Roundup(C3,1)enter
Result
1 . 3
2 . 5.8
Rounddown()
It returns the number by rounding of to its left most digit with adding 0.
Syntax
=Rounddown(cell address, no of decimal digit to show)enter
Ex
=Rounddown(B2,0)enter
=Rounddown(C3,1)enter
Result
1 . 2 2 . 5.7
Fact()
It returns the factorial of the number.
If we want to calculate the factorial of file then we can calculate as follow.
1*2*3*4*5=120
Syntax
=Fact(cell address or number)enter
Ex
=Fact(A4)enter
Result
120
Lcm()
It returns the least common multiple of the integer.
Syntax
=Lcm(cell1,cell2,cell3…..)enter
Ex
=Lcm(A4,C1,C4)enter
Result
475
Power()
It returns the power of a number we can also use zor (x^y=x
y
)mark to calculate the power
of number insured of using power function.
Syntax
=Power(base value cell address, power cell address)enter
Ex
=Power(B3,B4)enter
=Power(B3^B4)enter
Result
1 . 81
2 . 81
Product()
It returns the product of the number if we want to calculate the product value of 3,5,4
then the value will be 3*5*4=60
Syntax
=Product(cell1,cell2,cell3…..)enter
Ex
=Product(B3,B4,A4)enter
Result
60
Pi()
It retuns the numerical value of the mathematical constant pi in accuracy of to 15 digit.
Syntax
=Pi()enter
Ex
=Pi()
Result
3.141593
Sqrt()
It retuns the square root of a number.
Syntax
=Sqrt(cell address)enter
Ex
=Sqrt(B4)enter
Result
2
Sum()
This function adds all the number in a range.
Syntax
=sum(range)
Ex
=Sum(A4:C4)enter
Result
14.8
Statistical function
A
B
1
7
50
2
9
40
3
11
90
4
20
20
5
5
RAM
6
7
X
Average()
It returns the average of the number in a range.
Syntax
=Average(range)enter
=Average(cell1,cell2,cell3…..)enter
=Average(range,cell number)enter
Ex
=Average(A1:A5)enter
Result
10.4
Averagea()
It calculate the averagea of the number in a range by ignoring non-numerical dat.
Syntax
=Average(range)enter
Ex
=Average(B1:B7)enter
Result
34.28
( it ignores the value of x and result will be 34.28)
Count()
It count the number of cells having only number in a range.
Syntax
=Count(range address)enter
Ex
=Count(B1:B7)enter
Result
4
Counta()
It count the number of cell that are not empty in a range.
Syntax
=Counta(range address)enter
Ex
=Counta(B1:B7)enter
Result
6
Countblank()
It count the empty cell in a range .
Syntax
=countblank(range address)enter
Ex
=countblank()enter
Result
Min()
It returns the minimum value in a range.
Syntax
= min(range address) enter
Ex
= min(B1:B7)enter
Result
20
Max()
It returns the maximum value in a range.
Syntax
=max(range address) enter
Ex
=max(B1:B7)enter
Result
90
A
B
C
D
E
F
G
name
Gender
eng
hindi
percentage
reward
village
1
Alok
M
25
54
52
500
snp
2
Bikas
M
45
66
48
500
bdn
3
Suresh
M
67
24
68
400
khr
4
Ajay
M
34
32
75
400
snp
5
meena
F
91
76
87
500
bdn
6
neha
F
86
21
31
400
snp
7
Sanju
F
72
52
89
500
snp
Countif()
It counts the number of cells within a range that need the given criteria it means if a condition given
by user becomes true then it will count that cell.
Syntax
=countif(range,criteria)enter
Ex
=countif(B1:B7,”M”)enter
=countif(B1:B7,”F”)enter
Result
1. 4
2. 3
Countifs()
Count the number of cells in a given set of ranges that need the given set of criteria it means it
count only that cells which need the multiple criteria given by the user.
Syntax
=countifs(range1,criteria 1,range2,criteria 2...)enter
Ex
=countifs(G1:G7,”snp”,B1:B7,”M”,F1:F7,500)enter
Result
1
Sumif()
It calculates the cell value of a range defined by user if a condition is true.
Syntax
=sumif(condition range, criteria, sum range)enter
Ex
=sumif(G1:G7,”snp”,F1:F7)enter
Result
1800
Sumifs()
It calculate the cell value of a range defined by user if the given set of conditions becomes true.
Syntax
=sumifs(sum range,condition range1,criteria1,condition range2,criteria2)enter
Ex
=sumifs(F1:F7,G1:G7,”snp”,B1:B7,”M”)enter
Result
1800
Averageif()
It returns the average from the range defined by user if the conditiom is true.
Syntax
=averageif(condition range criteria,average range)enter
Ex
Averageif(G1:G7,”snp”,F1:F7)enter
Result
450
Averageifs()
It calculate the average value of range defined by user if the given set of condition becomes true.
Syntax
= averageifs(average range,condition range1,criteria1,condition range 2,criteria2) enter
Ex
=averageifs(F1:F7,G1:G7,”snp”,B1:B7,”F”)enter
Result
450
Logical function
And()
It returns "true" if all the argument logical function statement are true. But if one of the statement
or argument is "false" than it returns "false".
Syntax
=and(argument1,argument2...)enter
Ex
=and(2+2=4,3*5=15)enter
=and(2+2=4,3*5=15,7+1=9)enter
=and(A1+B1=4,A2*B2=15,A3+B3=9)enter
Result
1. True
2. False
3. False
Or()
It returns " true" if any of the given argument is true but returns false if all the argument are false.
Syntax
=or(argument 1,argument 2...)enter
Ex
=or(2+2=4,3*5=15)enter
=or(2+2=4,3*5=15,7+1=9)enter
=or(A1+B1=4,A2+B2=15,A3+B3=9)enter
=or(2+2=5,4+3=7,7+1=9)enter
Result
1. True
2. True
3. True
4. False
If()
This function returns a value if a specify condition is true and returns another value if the condition
is false.
Syntax
=if(logical text or criteria ,value if true,value if false)enter
Ex
=if(c2>600,"over payment","correct payment")
=if(c2<600,"correct payment","over payment")
Result
1. In D2=correct payment
2. In D4 = over payment
Not()
It returns false is the argument true and if returns true is the argument is false.
Syntax
= not(argument) enter
Ex
=not(2+2=4)enter
=not(A1+B1=4)enter
=not2+2=5)enter
Result
1. False
2. False
3. True
Nestedif()
This function is used to mix multiple function to obtain a result.
=if(logical test,value if true,value if false)enter
Ex
More than 60% paid 5000 rupees
More than 50% paid 3000 rupees
=if(C2>50,3000,if(C2>60,5000,0))enter