Explore model answers categorized by subjects like Java, HTML, DBMS, and more.
In one word we can say that Microsoft Excel is an electronic spreadsheet.Microsoft provide us Excel to perform mathematical operations on data that was organized into rows and columns.
Spreadsheet is like as paper ledger sheet which open on computer.It consists of huge number of rows and columns.Using this associated number made easy to search.
And it has also give us facility to perform mathematical operations like: addition, multiplication, average etc.
It has 65536 rows and columns.
Spreadsheet consist of columns, rows and their intersections are called as cells.
Each cells consist of text(labels), number data (constants) and mathematical formulas.
I have given you three basic types of data that we used in MS Excel.
labels : These are text without any numerical value.(like: name or wage or days)
constants : These are numbers(like: 3 or 2.61 or -3.5 etc).
formulas : These are mathematical equations which used to make calculation(like: =2-1+8 or =4*2-6+5.
Firstly I told you basic math functions are addition, subtraction, division and multiplication.
Now, I show you how to use these operations in MS Excel with an exmple:
_________________
|_____|__A__|__B__|
|__1__|__6__|__8__|
|__2__|__3__|__7__|
|__3__|__9__|__2__|
Above sheet is looking like Excel sheet. We perform basic operations in this Excel sheet like that,
Mathematical Symbol Constant Referenced Answer
Operation Data Data
Addition + =6+7 =A1+B2 13
subtraction - =9-2 =A3-B3 7
multiplication * =3*2 =A2*B3 6
Division / =6/2 =A1/B3 3
Selecting cells is like an equations.These are most useful concept of selecting cells.
Example:
A Excel sheet,
_________________
|_____|__A__|__B__|
|__1__|__6__|__8__|
|__2__|__3__|__7__|
|__3__|__9__|__2__|
Now, we can use methods of selecting cells like that.I given you how to perform addition with selecting cells method.
Syntax:
(to select)
=sum(type in)
=sum(click on)
to select type in click on
A1 A1 click to A1
A1,A2,A3,A4 A1:A4 click to A1 and
drag to A1 with
button down
A1,A2,B1,B2 A1:B2 click to A1 and
drag to B2 with
button down
Now, I have given you how to perform some specific functions like: SUM, AVERAGE, MAX, MIN, COUNT, COUNTA, IF, PMT, SIN COS TAN SEC etc.
A Excel sheet,
_________________
|_____|__A__|__B__|
|__1__|__6__|__8__|
|__2__|__3__|__7__|
|__3__|__9__|__2__|
|__4__|__8__|__5__|
|__5__|_abc_|_____|
|__6__|_____|_xyz_|
Syntax to perform SUM operation.
=SUM(first value, second value, etc)
Example:
=sum(A1:A3) A1,A2,A3 18
=sum(A1:A2,10) A1,A2,10 19
=sum(B1:B2,B4) B1,B2,B4 20
Syntax to perform AVERAGE operation.
=average(first value, second value, etc)
Example:
=average(A1:A3) A1,A2,A3 6
=average(A1:A2,10) A1,A2,10 6.33
=average(B1:B2,B4) B1,B2,B4 6.67
Syntax to perform MAX operation.
=MAX(first value, second value, etc)
Example:
=max(A1:A3) A1,A2,A3 9(max)
=max(A1:A2,10) A1,A2,10 10(max)
=max(B1:B2,B4) B1,B2,B4 8(max)
Syntax to perform MIN operation.
=MIN(first value, second value, etc)
Example:
=min(A1:A2) A1,A2,A3 3
=min(A1:A2,10) A1,A2,10 3
=min(B1:B2,B4) B1,B2,B4 5
Syntax to perform COUNT operation.
=Count(first value, second value, etc)
Example:
=Count(A1:A3) A1,A2,A3 3
=Count(A1:A2,10) A1,A2,10 3
=Count(A1,A2) A1,A2 2
=Count(A1,A4) A1,A4 2
=Count(A1,A5) A1,A5 1
=Count(A1,A6) A1,A6 1
Syntax to perform COUNTA operation.
=CountA(first value, second value, etc)
Example:
=CountA(A1:A3) A1,A2,A3 3
=CountA(A1:A2,10) A1,A2,10 3
=CountA(A1,A2) A1,A2 2
=CountA(A1,A4) A1,A4 2
=CountA(A1,A5) A1,A5 2
=CountA(B1,B5) B1,B5 1
In this blank entries are not counted but text entries are counted.
Syntax to perform PMT(use to perform loan operation) operation.
=PMT(rate, NPER, PV, FV, type)
Where,
rate = interest per second
NPER = number of payments until repaid
PV = present value of loan
FV = future value of amount
type = to use 0 or 1 indicates payments are
due.
Example:
__________________________
|_____|_____A______|___B___|
|__1__|bike loan |$12,000|
|__2__|interest | 9.60%|
|__3__|# of payment| 60|
|__4__| | |
|__5__|Monthly PMT |$252.61|
B5 =PMT(B2/12,B3,-B1)
Syntax to perform IF operation.
=IF (condition, value-if-true, value-if-false)
Example:
|_____|_____A______|___B___|
|__1__|Price |Over dollar|
|__2__|$.92 | Yes |
|__3__|$1.21 | No |
|__4__|comparing # |returning #|
|__5__|13034 |0.09 |
|__6__|7657 |0.06 |
=IF (A2<1,\"Yes\",\"No\") is(.92<1) Yes
=IF (A3<1,\"Yes\",\"No\") is(1.21<1) No
=IF (A5>10000,\".09\",\".06\")is(13034>10000) .09
=IF (A6>10000,\".09\",\".06\")is(7657>10000) .06
Syntax to perform SIN,COS,TAN operation.
degrees for formula = sin (angle * pi()/180)
radians for formula = sin (angle)
Example:
angle sin cos tan
REF =sin(REF) =cos(REF) =tan(REF)
0 0.00 1.00 0.00
30 0.50 0.87 0.58
45 0.71 0.71 1.00
90 1.00 0.00
180 0.00 -1.00 0.00
I given you some steps using them you can unlock a cell.
Step1: To select cell or cells that you want to unlock.
Step2: Choose format for cell.
Step3: to select the protection tab from dialog box of format cell.
Step4: Than now remove checkbox from locked checkbox.
We can protect our worksheet to follow following path Tools-Protection-Protect Sheet.In Protect Sheet
it give facility to set password(It is optional).
Now, when you set password your worksheet will password protected. and you can again unlock this worksheet by using same password.
By using these steps we can hide our cell or cells.
Step1: Select cell and cells on which you want to perform hide operation.
Step2: Now, select Choose Format of cell.
Step3: set protection tab when you open the dialog box of Format Cells.
Step4: Now, set checkmark to Hidden checkbox.