NAME PHYSICS CHEMISTRY MATHS CS ENGLISH TOTAL PERCENTAGE
NRITEV 9 3 9 66 54 141 28.2
VASANT 63 30 42 46 90 271 54.2
ABHIMANYU 61 82 40 82 58 323 64.6
ANAMIKA 52 28 84 16 60 240 48
RAHUL 25 36 73 20 67 221 44.2
RITIKA 61 69 11 51 69 261 52.2
a to xfd
column--16384
a to xfd
row
1048576
ctrl+a --all select
shift+arrow keys--step by step select
ctrl+shift+arrow keys--complete at once
ctrl+d---downward drag
excel? function operator + addition
microsoft analytical
tool which handle
spreadsheets dynamic calculation pre defined formula - subtraction
sum(b2:f2) * multiply (astrerisk)
functions region select / division (slash)
pre defined formulas
ctrl+D ^ power
functions-->pre defined formula
conditional functions
if
single criteria/condition
muliple criteria
nested criteria
STATUS
fAIL
pass if(logical_test,value_if_true,value_if_false)
pass calculation dynamic
pass
pass
pass
percentage
formula function
user defined pre defined(developer)
m/mm*100 average()
NAMES AGE ELIGIBILITY
Nritesh Singh 18 Eligble
Tripti Suri 21 Eligble check whether a candidate is eligilible for casting votes or not?
Lavanya Gupta 20 Eligble
Krishka Pandey 28 Eligble
Soni Dubey 11 nope
Kiara Doda 21 Eligble
Dinesh Keshwana 11 nope
Mayank Yadav 26 Eligble
Aman Chauhan 29 Eligble
Ritvik Verne 21 Eligble
Nakul Chattopadhyay 14 nope
Abhishek Kapoor 27 Eligble
Sunny Pathak 24 Eligble
Vikrant Gautam 13 nope
Sidhharth Suri Nomnom 25 Eligble
Wari Sem Doda 29 Eligble
Indir Turi Chand 19 Eligble
Chandni Rawat 24 Eligble
ble for casting votes or not?
SALES HEAD TARGET target(dynamic) target(manual)
Abhishek Kapoor 60000 nope no commission ctrl+pg dn forward
Sunny Pathak 49999 nope no commission ctrl+pg up backward
Vikrant Gautam 81715 8171.5 8171.5 if sales>avg
Sidhharth Suri Nomnom 96064 9606.4 9606.4 then 10%commission
Chandni Rawat 67689 nope no commission else no commission
Aman Rastogy 100500 10050 10050
Nishita Sharma 93536 9353.6 9353.6
Samiksha Kohli 62324 nope no commission
nope
ctrl+d--downwrd drag
problem
row chnge
solution
fix/freeze/lock
f4/fn+f4
NAMES sales even/odd even/odd
Abhishek Kapoor 24 even
Sunny Pathak 47 odd EVEN
Vikrant Gautam 52 even ODD
Sidhharth Suri Nomnom 44 even EVEN
Chandni Rawat 70 even EVEN
Aman Rastogy 31 odd EVEN
Nishita Sharma 55 odd ODD
Samiksha Kohli 42 even EVEN
EVEN--divide by 2--REMAINDER(0)
ODD--divide by 2--REMAINDER(1)
REMAINDER-MODULUS
or function is used when options are available
adhar card 0 only one condition
bank account--> or
pan card 1 atleast one condition should be true
18+ y both condition necessary
voting--> AND
votercard Y all conditions should be true
And function is used when all conditions are necessary
single,nested,multiple
compulsory---and--all should be true
optional--or--any one should be true
COMPULSORY CRITERIA
ALL SHOULD BE TRUE
MOM AND DAD
IF 1FALSE THEN ALL
MUTILPLE
FALSE CRITERIA
YES U CAN OPEN ACC
ELIGIBLE
OPTIONAL CRITERIA
ANY ONE SHOULD BE TRUE
MOM OR DAD
IF 1TRUE THEN ALL TRUE
Res
pon
Confirm se
Accura
Coun ations Rec Expected
Request Type/Activity Actual Processing te
SL# try/E GL Code sent eiv Processing
Name Date (Yes/N
ntity (Yes/No ed Date
o)
) (Ye
s/N
o)
1 Order Management India 98765 Yes Yes 24/08/18 24/08/18 Yes
2 Order Management India 98765 Yes Yes 24/08/18 24/08/18 Yes
3 Order Management India 98765 Yes Yes 24/08/18 24/08/18 Yes
4 Order Management India 98765 Yes Yes 24/08/18 24/08/18 Yes
5 Order Management India 98765 Yes Yes 24/08/18 24/08/18 Yes
Use Formulae to get the below results. Use the figures already filled in the Cells
Column "L" If ("J" = Yes and "K" = No) then Status in column "L" should be "Within TAT" else it should be "Outside TAT". If it
Column "M" If ("J" = Yes and "L" = Within TAT) then Status in column "M" should be "Pass" else it should be "Fail".
Column "N" IF K IS NO THEN N SHOULD BE [Link] VERSA
Final Exclusion
Delayed Status
STATUS Yes/No
no Within TAT PASS Yes
YES Outside TAT FAIL No
No Within TAT PASS Yes
Yes Outside TAT FAIL No
No Within TAT PASS Yes
in the Cells
se it should be "Outside TAT". If it is Within TAT - the cell color should change to Green, if it is Outside TAT - the cell color should change to Red.
lse it should be "Fail".
NAME SILVER GOLD DIAMOND TOTAL SALES COMISSON
NEHA 34000 34000 20000 201565 yes
MAHESH 81207 55571 63680 200458 yes if sales of each the elements>50k
ROHIT 86712 53362 91105 12000 no then yes
RITIK 61759 56186 59568 17000 no else no
ROSHAN 47179 66913 80573 194665 yes
NINA 72845 67044 56326 10000 no
TEENA 45121 68871 86959 200951 yes
ROSHAN 85677 69403 82540 237620 yes
XXZX
ch the elements>50k
**** Assign Remarks
Le Let
tte ter
Student r Gra
Ayan B Good B
Barry B Good E
Charles E Failed D
Dan B Good C
Emma D POOR A
Fletcher D POOR
Gary C Acceptable
Harris C Acceptable
Indy A EXCELLENT
Jones D POOR 1
Kamalini A EXCELLENT 2
Larry B Good 3
Munroe D POOR 4
Ayan B Good
Barry B Good
Charles E Failed
Dan B Good
Emma D POOR
Fletcher D POOR
Gary C Acceptable
Harris C Acceptable
Indy A EXCELLENT
Jones D POOR
Kamalini A EXCELLENT
Larry B Good
Munroe D POOR
Ayan B Good
Barry B Good
Charles E Failed
Dan B Good
Emma D POOR
Fletcher D POOR
Gary C Acceptable
Harris C Acceptable
Indy A EXCELLENT
Jones D POOR
Kamalini A EXCELLENT
Larry B Good
Munroe D POOR
Remar
ks
Good
Failed
Poor
Acceptable
Excellent
$A$1 absolute reference(both row & column lock)
A$1 absolute row/relative column(only row is fixed)
$A1 absolute column/relative row(only column is fixed)
A1 relative reference(free)
IF(C4=$H$4,$I$4,IF(C4=$H$5,$I$5,IF(C4=$H$6,$I$6,IF(C4=$H$7,$I$7,IF(C4=$H$8,$I$8)))))
Roll No Student Name Physics Chemistry Mathematics Grand Total
1 Amarjeet Singh 78 99 74 251
2 Sachin Kumar Sharma 32 52 66 150
3 Mohit Malik 92 87 76 255
4 Deepak Sharma 38 31 47 116
5 Kratika Bhatnagar 67 72 50 189
6 Gaurav Khanna 31 27 33 91
7 Asif Khan 56 65 69 190
8 Ajaj Kumar Rana 99 98 93 290
9 Aakash Bhutani 83 92 80 255
10 Vivek Pant 53 50 55 158
REPORT SHEET
1 Number of Students 10
1 Class Overall Grade
1 Highest Marks in Chemistry
1 Lowest Marks in Physics
1 3rd Highest Marks in Mathematics
1 2nd Lowest Marks in Physics
Top Scorer Name
Bottom Scorer Name
1 Number of Student Failed in Physics
1 3rd Highest Scorer in Physics
Percentage status Grade
83.67 #CALC!
50.00 GRADE CRITERIA
85.00 32
38.67 44
63.00 59
30.33 79
63.33 90
96.67 100
85.00
52.67
GRADE CRITERIA
91-100 Outstanding
81-90 Distinction
61-80 First Division
44-60 Second Division
33-44 Third Division
0-32 Fail
Note: All marks are out of 100
GRADE CRITERIA
Fail
Third Division
Second Division
First Division
Distinction
Outstanding
Name Salary Total Tax CESS
Akhil Gupta ₹ 11,66,320.00
Amarjeet Singh ₹ 2,71,445.00
Asif Khan ₹ 10,00,000.00
Deepak Sharma ₹ 1,45,000.00
Gaurav Khanna ₹ 10,95,769.00
Kratika Bhatnagar ₹ 13,57,978.00
Mohit Malik ₹ 5,00,000.00
Pallavi Srivastava ₹ 4,65,804.00
Praveen Sharma ₹ 3,17,683.00
Ravi Ranjan ₹ 9,79,305.00
Vivek Pant ₹ 2,50,000.00
Yatish Kumar ₹ 12,05,518.00
#TOTAL TAX YEARLY
Income Slab Tax Rate
Income up to ₹ 2,50,000 0%
Income from ₹ 2,50,000 – ₹ 5,00,000 5%
Income from ₹ 5,00,000 – ₹ 10,00,000 20%
Income more than ₹ 10,00,000 30%
SURCHARGE:
10% of income tax, where total income is between ₹ 50 lakhs and ₹ 1 crore
15% of income tax, where total income exceeds ₹ 1 crore
CESS:
3% on total of income tax + surcharge
Country Capital Status without nested if Correct Situation
Thailand Bangkok Country Capital
India Lucknow Thailand Bangkok
USA Malaysia India New Delhi
Thailand chicago USA Washington DC
India Kolkata
USA Washington DC
Thailand Hong Kong
India New Delhi
USA Germany
If Capital is Matching with Country then Status should be Correct Else Wrong
Driver's Number of
Date Item TOTAL SALES
name items
01/02/13 John May TV 25
01/02/13 Peter Whitewashing machine 30
02/02/13 Carl Nowak tv 15
03/02/13 Peter WhiteTV 32
03/02/13 George Ramsay
refrigerator 25
03/02/13 Carl Nowak washing machine 18
03/02/13 John May refrigerator 15
04/02/13 Carl Nowak refrigerator 25
04/02/13 Peter WhiteTV 30
04/02/13 George Ramsay
refrigerator 15
04/02/13 Mertl Pavel microwave 25
04/02/13 John May washing machine 14
05/02/13 John May washing machine 25
05/02/13 Carl Nowak TV 30
05/02/13 George Ramsay
microwave 15
05/02/13 Peter WhiteTV 15
06/02/13 John May microwave 25
07/02/13 John May TV 30
08/02/13 George Ramsay
washing machine 13
08/02/13 Peter Whiterefrigerator 25
08/02/13 Carl Nowak microwave 30
08/02/13 Peter Whitewashing machine 15
08/02/13 John May microwave 25
09/02/13 George Ramsay
washing machine 34
ITEM PRICE
TV 25000 25000
washing machine 15000 25000
refrigerator 28000 25000
microwave 15000 25000
PROBLEM
CTRL+D--DOWNAWARD DRAG--ROW CHNGE
SOLUTION-FIX/FREEZE LOCK
F4/FN+F4
Roll Number Student Name Score Rank
151507 Julia Putin 65
167508 Michael Hicks 90
184782 Anna Newhart 74
199210 John Bradshaw 81
217184 Andy Richards 36
254223 Stephen Bell 53
265147 John Jennings 33
265791 Gloria Silverstone 60
283505 Jane Anderson 99
334673 Elizabeth Diamond 97
353809 Dan Armstrong 75
366796 Frank Lee 89
380729 Jessica Sagan 88
405608 Charles Davis 95
466104 Margaret Streep 61
491600 Chuck West 100
535974 Lucille Ashe 32
565143 Meryl Simpson 98
574587 Howard Simpson 51
695118 Alfred Hawking 55
723867 Julia Carrey 34
763457 Julie Spears 43
920259 Marilyn Manning 94
946474 Paul Goodman 70