Total Rows 1048576

Columns xfd 1111

Columns in nos 16384 1111
sheets 1111
sheets no specifed limit Sheet Name 1111
Sheet Name 31 0 1111
0 1111
1 0 1111
2 0 1111
3 0 1111
4 0 1111
5 1111
6 1111
7 1111
8 1111
9 1111
10 1111
11 1111
1111
1111
1111
 SALESMAN REPORT

SALESMAN CODE NO. MAY JUNE JULY AUGUST TOTAL (su AVERAGE COMMISSION
A001 5700 3600 4500 5600 19400 4850 1940 4850
A002 5600 5000 4800 4800 20200 5050 5050
A003 7500 4100 5300 5800 22700 5675 5675
A004 3400 5600 2800 5600 17400 4350 1740
A005 6700 4700 7400 4700 23500 5875 5875
A006 jhg 8400 7900 6400 22700 7566.667 5675
A007 3700 3900 6700 4300 18600 4650 1860

Min 3400 3600 2800 4300 17400 4350

max 7500 8400 7900 6400 23500 7566.667

count 6
counta 8

PREPARE CHART FOR SALESMAN REPORT FORM MONTH M̀

Calculate 25% commission on total if average Sales is greater than 5000 else calcuate 10% commission on total
Now there are three kinds of cell references that you can use in Excel:

1. Relative Cell References

2. Absolute Cell References
3. Mixed Cell References

Understanding these different types of cell references will help you work with formulas and save time (especially w

Types of Cell Reference in Excel

1. Relative cell references:
It does not contain dollar signs in a row or column, e.g., A2. Relative cell reference type in excel changes when a for
in Excel, cell referencing is relative by default; it is the most commonly used cell reference in the formula.

2. Absolute cell references:

Absolute Cell Referen cecontains dollar signs attached to each letter or number in a reference, e.g., $B$4, Here if w
it makes absolute or locks both the column and the row, i.e., where Cell reference remains constant even if it copie

3. Mixed cell references in Excel:

It contains dollar signs attached to either the letter or the number in a reference. E.g., $B2 or B$4. It is a combinatio

Relative Reference Absolute Reference

Product Qty Rate Amt Product Qty rate

MOBILE 5 16000 80000 MOBILE 5 16000
COMPUTER 23 35000 805000 COMPUTER 23 35000
AC 4 50000 200000 AC 4 50000
TV 30 42000 1260000 TV 30 42000
LAPTOP 10 65000 650000 LAPTOP 10 65000
SPEAKER 20 25000 500000 SPEAKER 20 25000

Short cut for cell reference

For Fixed whole cell or range Press F4 one time
For Fixed only Row Press F4 Two times
For Fixed only Column Press F4 Three times
To Remove dollor sign Press F4 Four times

Product Qty Price Amount Product Qty Price

5 Star 25 360 9000 5 Star 25 360
Cadbury Dairy Milk 32 450 14400 Cadbury Dairy Milk 32 450
Cadbury Dairy Milk Silk 15 550 8250 Cadbury Dairy Milk Silk 15 550
Park 24 150 3600 Park 24 150
Gems 36 250 9000 Gems 36 250
Cadbury Nutties 24 300 7200 Cadbury Nutties 24 300
s and save time (especially when copy-pasting formulas).

e in excel changes when a formula is copied or dragged to another cell;

nce in the formula.

erence, e.g., $B$4, Here if we mention a dollar sign before the column and row identifiers,
ains constant even if it copied or dragged to another cell.

$B2 or B$4. It is a combination of relative and absolute references.

Mixed Reference
Discount Discount
Amt 10% 15% 1% 2% 3% 4% 5%
80000 8000 12000 1000 10 20 30 40 50
805000 80500 120750 2000 20 40 60 80 100
200000 20000 30000 3000 30 60 90 120 150
1260000 126000 189000 4000 40 80 120 160 200
650000 65000 97500 5000 50 100 150 200 250
500000 50000 75000 6000 60 120 180 240 300

10%
Amount Commission 1 2 3 4 5 6 7 8 9
9000 900 900 2 4 6 8 10 12 14 16 18
14400 1440 1440 3 6 9 12 15 18 21 24 27
8250 825 4 8 12 16 20 24 28 32 36
3600 360 5 10 15 20 25 30 35 40 45
9000 900 6 12 18 24 30 36 42 48 54
7200 720 7 14 21 28 35 42 49 56 63
 8 16 24 32 40 48 56 64 72
9 18 27 36 45 54 63 72 81
10 20 30 40 50 60 70 80 90
10 11
20 22
30 33
40 44
50 55
60 66
70 77
 80 88
90 99
100 110
Sr.No Product City Sales Rep Sales Qty Rate Amount
1 Mobile Mumbai Swati 25 18900 1 count how many time mobile is repeated
2 Camera Kolkata Anjali 28 34700 971600 countif 3
3 Laptop Pune Neha 10 28700 287000
4 Speaker Mumbai Savitri 25 46200 1155000 2 count how many time pune is repeated
5 AC Amritsar Jassi 17 43500 739500 countif 8
6 TV Pune Prajakta 29 39600 1148400
7 Speaker Kochi Swarupa 27 11200 302400 3 calculate total sales qty of mobile calculate average sales qt
8 AC Pune Neha 7 31500 220500 sumif 58 58 averageif
9 AC Lucknow Afreen 7 37700 263900
10 TV Kolkata Kiran 13 13500 175500 4 calculate total of sales qty which is greater than 15
11 Speaker Mumbai Archana 25 19900 497500 Sumif 583 583
12 Computer Mumbai Swati 22 21100 464200
13 Camera Jalandhar Jassi 29 13600 394400 5 count Product mobile from Mumbai city which sales qty is greater than 1
14 Laptop Mumbai Swati 25 33400 835000 countifs 2
15 Speaker Kolkata Anjali 30 28700 861000
16 Computer Pune Neha 15 34100 511500 6 give total of sales qty of Product mobile from Mumbai city which sales q
17 TV Mumbai Savitri 27 31600 853200 sumifs 45
18 Speaker Kanpur Seeta 7 20300 142100
19 Computer Lucknow Radhika 15 29500 442500 7 give average of amt of product computer sold by neha
20 Computer Amritsar Hema 21 33200 697200 averageifs 647900
21 Mobile Mumbai Swati 20 37700 754000
22 Camera Kolkata Anjali 24 22800 547200 8 countblank 0 There is no Blank cells in table
23 Laptop Pune Neha 24 13400 321600
24 Speaker Mumbai Savitri 30 12500 375000
25 Computer Patiala Jassi 19 30800 585200 9 Calcalute total and average Sales amount of laptop From Mumbai city w
26 TV Pune Prajakta 6 22500 135000 sumifs averageifs
27 Mobile thrissur Swarupa 13 20500 266500
28 Camera Pune Neha 5 28700 143500
29 Laptop Kanpur Afreen 22 45600 1003200
30 Speaker Kolkata Kiran 8 22100 176800
31 Laptop Mumbai Archana 18 36900 43000
 32 Speaker Kolkata Anjali 22 28700 631400
33 Computer Pune Neha 23 34100 784300
34 TV Mumbai Savitri 12 31600 379200
35 Speaker Kanpur Seeta 24 20300 487200
36 Computer Lucknow Radhika 27 29500 796500

count 36
counta 36
 count numbers
counta no & albh
calculate average sales qty of mobile countif
19.33333 sumif
averageif
>15 countifs
sumifs
averageifs
h sales qty is greater than 10

Mumbai city which sales qty is greater than 10

aptop From Mumbai city whose sales qty is greater than 15

countifs
ID NAME OF EMPLOYEE AGE GENDER DEPARTMENT BRANCH+F1:FPOST SALARY
1 ANU MISTRA 31 FEMALE SALES ANDHERI MANAGER 55000
2 VEENA K 48 FEMALE ACCOUNTS DADAR CA 60000
3 BHAVIK SHAH 46 MALE ACCOUNTS DOMBIVALI CA 59000
4 BHAKTI GUJAR 39 FEMALE FINANCE DOMBIVALI EXECUTIVE 20000
5 KIRAN SANE 30 MALE FINANCE BADLAPUR MANAGER 35000
6 ARVIND SINGH 55 MALE IT ANDHERI OFFICER 60000
7 SHWETA SHINDE 25 FEMALE SALES MIRA ROAD EXECUTIVE 25000
8 RAKSHA MENAN 36 FEMALE IT DADAR EXECUTIVE 20000
9 SANDEEP GUPTE 45 MALE PURCHASE MIRA ROAD MANAGER 58000
10 CHIRAG DHOPTE 25 MALE ACCOUNTS THANE EXECUTIVE 22000
11 RIYA SINHA 25 FEMALE SALES ANDHERI EXECUTIVE 15000
12 RAVINDRA PATHAK 50 MALE SALES THANE OFFICER 65000
13 SACHIN TEMBULKAR 49 MALE PURCHASE BANDRA OFFICER 82000
14 JAVED ALI 32 MALE PRODUCTION ANDHERI MANAGER 45000
15 AJAZ SAYED 22 MALE PRODUCTION MIRA ROAD EXECUTIVE 19000
16 RINA DETHE 44 FEMALE ACCOUNTS ANDHERI CA 75000
17 SWEETY GULANI 31 FEMALE MARKETING BANDRA OFFICER 70000
18 CHINTAN GORE 45 MALE ACCOUNTS DADAR CA 80000
19 JITIN JAIN 25 MALE PRODUCTION BORIVALI EXECUTIVE 20000
20 NITIN MANE 56 MALE HR BORIVALI MANAGER 65000
21 YATIN KANE 46 MALE ACCOUNTS ANDHERI CA 75000
22 JAYASHREE KAMAT 32 FEMALE R&D THANE OFFICER 60000
23 JANKI SINGH 46 FEMALE R&D THANE MANAGER 52000
24 VINA BHAGAVAT 39 FEMALE R&D BADLAPUR EXECUTIVE 25000
25 MAYARA SHAIKH 45 FEMALE ACCOUNTS ANDHERI CA 55000
26 ANIRUDHA NARVEKAR 29 MALE R&D BADLAPUR EXECUTIVE 21000
27 POONAM SEN 36 FEMALE R&D THANE EXECUTIVE 25000
28 SUJAY GUPTA 20 MALE ACCOUNTS DADAR EXECUTIVE 36000
29 CHETNA KOLI 36 FEMALE FINANCE MIRA ROAD MANAGER 45000
30 ihj 21 FEMALE PURCHASE ANDHERI EXECUTIVE 24000
31 SAISHA KENI 23 FEMALE MARKETING BELAPUR EXECUTIVE 33000
32 SAKSHI RATHI 45 FEMALE ACCOUNTS MIRA ROAD HEAD CLEARK 23000
33 KATNTILAL DAMODAR 50 MALE MARKETING THANE MANAGER 50000
34 SHEETAL PANCHAL 32 FEMALE MARKETING DADAR EXECUTIVE 35000
35 NAMBRATA SALVI 29 FEMALE SALES ANDHERI SALES MANAGER 36000
36 SUNDAR KUMAR 36 MALE FINANCE DADAR MANAGER 63000
GIVE ANS. HERE QUESTIONS

1 Count how many employee are there by name of employee coloum

2 Count how many 'females' are there working.
3 Count how many 'Males' are there working.
4 Count how many 'CA' are there.
5 Count employee whose age is below 25
6 Count how many employee are erning above 50000
7 Count how many females are there from 'Andheri' branch
8 Count how many managers are there working
9 Count how many 'males' are there from 'Dombivali' branch of post 'CA'
10 How much total 'salary' is given to 'CA' post?
11 How much total 'salary' is given to 'Acconts' department?
12 How much total 'salary' is give to 'Accounts' department from 'Andheri' branch?
13 Calculate 'average salary' of 'Accounts' department
14 Calculate 'average salary' of 'Accounts' department from 'Dombivali' branch.
15 Calculate 'average salary' of 'IT' department
16 Calculate sum of salary of greater than age 50
17 Count how many 'executive' are there of 'sales' department
18 Calculate total 'salary' of 'Finance' from 'Dadar' branch of 'Manager'
19 Calculate 'average salary' of 'Andheri' branch of 'Accounts' department
20 count blank cells
me of employee coloum COUNTA
COUNTIF
COUNTIF
COUNTIF
COUNTIF
e 50000 COUNTIF
ndheri' branch COUNTIFS
COUNTIF
mbivali' branch of post 'CA' COUNTIFS
SUMIF
department? SUMIF
department from 'Andheri' branch? SUMIFS
artment AVERAGEIF
artment from 'Dombivali' branch. AVERAGEIFS
AVERAGEIF
SUMIF
les' department COUNTIFS
dar' branch of 'Manager' SUMIFS
h of 'Accounts' department AVERAGEIFS
COUNTBLANK
DIVISION PRODUCTS REGION QTY REVENUE COST PROFIT
B S968 West 4900 415,700 315,700
B K675 East 2000 320,750 220,550 100,200
A J789 West 3050 416,800 316,800 100,000
B F452 East 4500 540,750 350,250 190,500
A K675 West 5950 664,700 383,700 281,000
A J789 West 2500 400,500 300,750 99,750
B K675 West 2000 320,750 220,550 100,200
A F452 East 4500 540,750 350,250 190,500
A J789 East 5200 675,070 500,650 174,420

Count Sold Product Total Profit

Count A Division Total Profit of Division A
Count Division A for East region Total Profit of Division A for East region

Count each division region wise Calculate total profit of each division region wise
Type criteria manually Type criteria manualy
East West East West
A A
B B

With Cell Ref With Cell Ref

East West East West
A A
B B
 Average Profit
A Average Profit of Division A
A for East region Average Profit of Division A for East region

each division region wise Calculate average profit of each division region wise
Type criteria manualy
East West
A
B

With Cell Ref

East West
A
B
Shortcuts to get today's date in Excel
1. To inset today's date: Ctrl + ;
2. To insert the current time: Ctrl + Shift + ;
3. To enter the current date and time: Ctrl + ; then Space and then Ctrl + Shift + ;

Extract following from current date

(Cell D9)
insert current date with short-cut today day month
4/23/2024 8/21/2025 21 8

insert current time with short cut

10:17 Extract following from current date
& time (Cell A15)
insert standard date and time now hour minute
4/23/2024 10:17 8/21/2025 11:15 11 15

The Excel DATEDIF function returns the difference between two date values in years, months, or days.
The DATEDIF (Date + Dif) function is a "compatibility" function that comes from Lotus 1-2-3. For reasons unknown,
it is only documented in Excel 2000, but you can use it in your formulas in all Excel versions since that time.

Old Date New Date diference in edate

8/15/1947 8/15/2021 years 74 to calculate end date by no of mo
3/21/2020 8/21/2025 years 5 given from a particular date
Months 117
days 3573 date
8/27/2021
DOB current date 8/27/2021
11/9/2015 8/21/2025 8/27/2021

age
year 9
months 117
day 3573

To calculate end date of a task from the no of days given from a par(same , but as per country weeken
workday workday.intl
start date day end date end date
8/21/2025 10 9/4/2025 #NAME?

workday workday.intl
start date day end date end date
8/21/2025 30 10/2/2025 #NAME?
8/21/2025 21
August
4/25/2024
5/1/2024
4/29/2024
wing from current date combine date from extract date which is weekday number of
(use cell F9, G9 and H9) current date (cell J9)
year date weekday
2025 8/21/2025 5

wing from current date combine time from extract time

(use cell F15, G15 and H15)
second time
36 11:15 AM

ths, or days.
3. For reasons unknown,
s since that time.

eomonth
o calculate end date by no of months same as edate only gives us end date of the month
iven from a particular date calculates the end of month by no of months given from a particular date

months edate date months eomonth

15 11/27/2022 8/27/2021 15 11/30/2022
-15 5/27/2020 8/27/2021 -15 5/31/2020
10 6/27/2022 6/1/2021 10 4/30/2022
6/1/2021 120 6/30/2031

Holidays
1/26/2022 Republic Day To calculate the no of work days for a task
3/1/2022 Maha Shivratri
3/18/2022 Rangwali Holi start date end date
4/2/2022 Gudi Padwa 8/21/2025 9/5/2025
4/10/2022 Rama Navami
4/14/2022 Ambedkar Jayanti
4/15/2022 Good Friday
5/1/2022 Maharashtra Day start date end date
5/2/2022 Eid al-Fitr 8/21/2025 9/20/2025
7/9/2022 Eid al-Adha
 8/15/2022 Indian Independence Day
8/31/2022 Gandhi Jayanti
10/5/2022 Dussehra
10/8/2022 Prophet's Birthday
10/24/2022 Diwali
10/25/2022 Bhai Dooj
11/8/2022 Guru Nanak Gurpurab
12/25/2022 Christmas Day
 which is week number of
current date (cell J9)
weeknum
34

en from a particular date

similar as networkday, international.

ork days for a task
networkdays networkdays.intl
days days
12 #NAME?

networkdays networkdays.intl

22 #NAME?
Name len (length of text) trim len
subhangi pandey 25 subhangi pandey 15
dharsti shah 22 dharsti shah 12
manju yadav 17 manju yadav 11
ranjana aCharekar 27 ranjana aCharekar 17
azar Sayed 25 azar Sayed 10

left right mid

Subhangi Pandey Subh Pandey ubha
Dharsti Shah Dhars i Shah hars
Manju Yadav Manju Yadav anju
Ranjana Acharekar Ranja arekar anja
Azar Sayed Azar Sayed zar

first name last name

Subhangi Pandey Subhangi Pandey
Dharsti Shah Dharsti Shah
Manju Yadav Manju Yadav
Ranjana Acharekar Ranjana Acharekar
Azar Sayed Azar Sayed

concatenate
Subhangi Pandey
Dharsti Shah
Manju Yadav
Ranjana Acharekar
Azar Sayed

replace
green grey grey
cool tool tool
took talk talk

substitute
bubble tuttle Substitute all be
bubble burble Substitute second instant
Bubble Buttle

exact
Jam Jam TRUE

0
rept
5 $$$$$
6 $$$$$$
3 $$$
2 $$
8 $$$$$$$$

char code
66 B t
99 c 6
55 7

text day in text Month in text

8/21/2025 Thursday August 2025
proper upper lower
Subhangi Pandey SUBHANGI PANDEY subhangi pandey
Dharsti Shah DHARSTI SHAH dharsti shah
Manju Yadav MANJU YADAV manju yadav
Ranjana Acharekar RANJANA ACHAREKAR ranjana acharekar
Azar Sayed AZAR SAYED azar sayed

search find find

5 5 5
3 3 11
2 2 8
2 2 5
3 3 7

I
 discount on amt discount
Product City Sales Rep Sales Qty Rate Amt sales qty >15 discount discount Nested if amt and
Mobile Mumbai Swati 9 18900 150000 5% 7500 2% 3000 FALSE
Camera Kolkata Anjali 13 34700 451100 5% 22555 7% 31577 FALSE
Laptop Pune Neha 10 36000 600000 5% 30000 10% 60000 TRUE
Speaker Mumbai Savitri 15 46200 693000 5% 34650 10% 69300 FALSE
AC Amritsar Jassi 1 43500 43500 5% 2175 0% 0 FALSE
TV Pune Prajakta 8 39600 316800 5% 15840 5% 15840 FALSE
Speaker Kochi Swarupa 15 11200 168000 5% 8400 2% 3360 FALSE
AC Pune Neha 17 31500 535500 17 20% 107100 9% 48195 FALSE
AC Lucknow Afreen 12 37700 452400 5% 22620 7% 31668 FALSE
TV Kolkata Kiran 23 13500 310500 23 20% 62100 5% 15525 FALSE
Speaker Mumbai Archana 8 19900 159200 5% 7960 2% 3184 FALSE
Computer Mumbai Swati 17 21100 358700 17 20% 71740 5% 17935 FALSE
Camera Jalandhar Jassi 17 13600 231200 17 20% 46240 3% 6936 FALSE
Laptop Mumbai Swati 15 33400 501000 5% 25050 9% 45090 TRUE
Speaker Kolkata Anjali 22 28700 631400 22 20% 126280 10% 63140 FALSE
Computer Pune Neha 5 34100 170500 5% 8525 2% 3410 FALSE
TV Mumbai Savitri 16 31600 505600 16 20% 101120 9% 45504 FALSE
Speaker Kanpur Seeta 9 20300 182700 5% 9135 2% 3654 FALSE
Computer Lucknow Radhika 15 29500 442500 5% 22125 7% 30975 FALSE
Computer Amritsar Hema 10 33200 332000 5% 16600 5% 16600 FALSE
Mobile Mumbai Swati 4 37700 150800 5% 7540 2% 3016 FALSE
Camera Kolkata Anjali 9 22800 205200 5% 10260 3% 6156 FALSE
Laptop Pune Neha 2 13400 26800 5% 1340 0% 0 FALSE
Speaker Mumbai Savitri 16 12500 200000 16 20% 40000 3% 6000 FALSE
Computer Patiala Jassi 6 30800 184800 5% 9240 2% 3696 FALSE
TV Pune Prajakta 6 22500 135000 5% 6750 2% 2700 FALSE
Mobile thrissur Swarupa 24 20500 492000 24 20% 98400 7% 34440 FALSE
Camera Pune Neha 12 28700 344400 5% 17220 5% 17220 FALSE
Laptop Kanpur Afreen 11 45600 501600 5% 25080 9% 45144 TRUE
Speaker Kolkata Kiran 1 22100 22100 5% 1105 0% 0 FALSE
Laptop Mumbai Archana 5 36900 184500 5% 9225 2% 3690 FALSE
Speaker Kolkata Anjali 22 28700 631400 22 20% 126280 10% 63140 FALSE
Computer Pune Neha 5 34100 170500 5% 8525 2% 3410 FALSE
TV Mumbai Savitri 16 31600 505600 16 20% 101120 9% 45504 FALSE
Speaker Kanpur Seeta 9 20300 182700 5% 9135 2% 3654 FALSE
Computer Lucknow Radhika 15 29500 442500 5% 22125 7% 30975 FALSE
stars (if(and)) or if(or) xor if(xor) if(not)
if
if sales qty is greater than 15 then show that qty, if sales qty less than 15 then show blank.
* if sales qty is greater than 15 then give 20% discount else give 5%

nested if
gift if amt is
discount will be
<100000 0
<200000 2%
<300000 3%
<400000 5%
<500000 7%
<600000 9%
* gift 10%
if and
if product is laptop and amt is greater than 300000 than give 5 stars else give 1 star

if or
gift if sales qty is greater than 20 or amt is greater than 500000 then give
gift else keep it blank( "")

if xor
if sales qty is greater than 20 or amt is greater than 500000 then give
gift gift else keep it blank( "")
if sales qty is not greater than 15
if not
if sales qty is not greater than 15

*
s than 15 then show blank.

stars else give 1 star

1 1 1 1 1 1
0 1 1 1 1 1
0 0 1 1 1 1
0 0 0 1 1 1
0 0 0 0 1 1
0 0 0 0 0 1
0 0 0 0 0 0
 Nested if
ID NAME OF EMPLOYEE AGE GENDER DEPARTMENT BRANCH POST SALARY SENIORITY GRADE IF (AND)
1 ANU MISTRA 31 F SALES ANDHERI MANAGER 55000 senior o
2 VEENA K 48 F ACCOUNTS DADAR CA 60000 super senior o
3 RANJAN SAWANT 46 M ACCOUNTS DOMBIVALI CA 59000 super senior o
4 BHAKTI GUJAR 39 F FINANCE DOMBIVALI EXECUTIVE 20000 senior a
5 ANIL GUPTA 30 M FINANCE BADLAPUR MANAGER 35000 junior c
6 ARVIND SINGH 55 M IT ANDHERI OFFICER 60000 super senior o
7 SHWETA SHINDE 25 F SALES MIRA ROAD EXECUTIVE 25000 junior b
8 RAKSHA MENAN 36 F IT DADAR EXECUTIVE 20000 senior a
9 SANDEEP KINI 45 M PURCHASE MIRA ROAD MANAGER 58000 senior o
10 CHIRAG DHOPTE 25 M ACCOUNTS THANE EXECUTIVE 22000 junior B
11 RIYA SINHA 25 F SALES ANDHERI EXECUTIVE 15000 junior A
12 RAVINDRA PATHAK 50 M SALES THANE OFFICER 65000 super senior o
13 SACHIN TEMBULKAR 49 M PURCHASE BANDRA OFFICER 82000 super senior o
14 JAVED ALI 32 M PRODUCTION ANDHERI MANAGER 45000 senior D
15 AJAZ SAYED 22 M PRODUCTION MIRA ROAD EXECUTIVE 19000 fresher A
16 RINA DETHE 44 F ACCOUNTS ANDHERI CA 75000 senior o
17 SWEETY GULANI 31 F MARKETING BANDRA OFFICER 70000 senior o
18 CHINTAN GORE 45 M ACCOUNTS DADAR CA 80000 senior o
19 JITIN JAIN 25 M PRODUCTION BORIVALI EXECUTIVE 20000 junior A
20 NITIN MANE 56 M HR BORIVALI MANAGER 65000 super senior o
21 YATIN KANE 46 M ACCOUNTS ANDHERI CA 75000 super senior o
22 JAYASHREE KAMAT 32 F R&D THANE OFFICER 60000 senior o
23 JANKI SINGH 46 F R&D THANE MANAGER 52000 super senior o
24 VINA BHAGAVAT 39 F R&D BADLAPUR EXECUTIVE 25000 senior B
25 MAYARA SHAIKH 45 F ACCOUNTS ANDHERI CA 55000 senior o
26 ANIRUDHA NARVEKAR 29 M R&D BADLAPUR EXECUTIVE 21000 junior B
27 POONAM SEN 36 F R&D THANE EXECUTIVE 25000 senior B
28 AJAY GUPTA 20 M ACCOUNTS DADAR EXECUTIVE 36000 fresher C BEST
29 CHINTAN NARE 36 M FINANCE MIRA ROAD MANAGER 45000 senior D
30 ISHA KENI 21 F PURCHASE ANDHERI EXECUTIVE 24000 fresher B
31 SAISHA KENI 23 F MARKETING BELAPUR EXECUTIVE 33000 junior C BEST
32 SAKSHI RATHI 45 F ACCOUNTS MIRA ROAD HEAD CLEARK 23000 senior B
33 KATNTILAL DAMODAR 50 M MARKETING THANE MANAGER 50000 super senior D
34 SHEETAL PANCHAL 32 F MARKETING DADAR EXECUTIVE 35000 senior C BEST
35 NAMBRATA SALVI 29 F SALES ANDHERI SALES MANAGER 36000 junior C
IF (OR) IF(AND) Question
5500 5%
6000 5% 1 If age is above 45 then employee is super senior if age is above 30 employee is senior if age is above 22 junior or else fresher
5900 5%
2000 5% 2 If salary is greater than 50000 grade will be' O' if salary is greater than 40000 grage will be 'D', if salary is greater than 30000 t
1750 5%
6000 20% 3 If post is executive and Salary is greater than 30000 then output will be BEST else keep blank
1250 5%
2000 5% 4 If employe super senior or senior give bonus 10% on salary or give 5% on salary
5800 5%
1100 10% 5 In Branch Andheri if Dept is IT and salary is greater than 50000 then give incentive 20% else 10%
750 10%
6500 10% grade greater than
8200 10% 50000 o
4500 10% 40000 D
950 10% 30000 C
7500 10% 20000 B
7000 10% 10000 A
8000 10% no grade
1000 10%
6500 10%
7500 10%
6000 10%
5200 10%
2500 10%
5500 10%
1050 10%
2500 10%
1800 10%
4500 10%
1200 10%
1650 10%
2300 10%
5000 10%
3500 10%
1800 10%
above 22 junior or else fresher

if salary is greater than 30000 than salary is 'C', if salary is greater than 20000, than grade is 'B', if salary is above 10000, than gread is 'A' eles no grade
cell address col content filename

Name of Formula value formula

isnumber 36 TRUE
iseven 2 TRUE
isodd 3
istext 323322 FALSE
isnontext hbjg'
islogical TRUE
isformula 12
iserror #DIV/0!
isna #N/A
iserr 100

sheet
sheets
Sr.No. Product City Sales Rep Sales Qty Rate Amount Product City Sales Rep Sales Qty Rate Amount
1 Mobile Mumbai Swati 25 18900 472500
2 Camera Kolkata Anjali 28 34700 971600
3 Laptop Pune Neha 10 28700 287000
4 Speaker Mumbai Savitri 25 46200 1155000
5 AC Amritsar Jassi 17 43500 739500 dcount dmin
6 TV Pune Prajakta 29 39600 1148400 Rate amount
7 Speaker Kochi Swarupa 27 11200 302400
8 AC Pune Neha 7 31500 220500 dcounta dmax
9 AC Lucknow Afreen 7 37700 263900 Product amount
10 TV Kolkata Kiran 13 13500 175500
11 Speaker Mumbai Archana 25 19900 497500 dsum
12 Computer Mumbai Swati 22 21100 464200 Amount
13 Camera Jalandhar Jassi 29 13600 394400
14 Laptop Mumbai Swati 25 33400 835000 daverage
15 Speaker Kolkata Anjali 30 28700 861000 Amount
16 Computer Pune Neha 15 34100 511500
17 TV Mumbai Savitri 27 31600 853200
18 Speaker Kanpur Seeta 7 20300 142100
19 Computer Lucknow Radhika 15 29500 442500
20 Computer Amritsar Hema 21 33200 697200
21 Mobile Mumbai Swati 20 37700 754000
22 Camera Kolkata Anjali 24 22800 547200
23 Laptop Pune Neha 24 13400 321600
24 Speaker Mumbai Savitri 30 12500 375000
25 Computer Patiala Jassi 19 30800 585200
26 TV Pune Prajakta 6 22500 135000
27 Mobile thrissur Swarupa 13 20500 266500
28 Camera Pune Neha 5 28700 143500
29 Laptop Kanpur Afreen 22 45600 1003200
30 Speaker Kolkata Kiran 8 22100 176800
31 Laptop Mumbai Archana 18 36900 43000
32 Speaker Kolkata Anjali 22 28700 631400
33 Computer Pune Neha 23 34100 784300
34 TV Mumbai Savitri 12 31600 379200
35 Speaker Kanpur Seeta 24 20300 487200
36 Computer Lucknow Radhika 27 29500 796500
Principle Amount (PV) 100000
Interest Rate 9.50% Periods pmt cumulative pmt ipmt cumulative ipmt ppmt
No. of Periods (NPER) 36
1
Periodical payment 2
pmt ₹ 376.44 3
4
Periodical interest amount 5
ipmt ₹ 791.67 6
7
Periodical principal amount 8
ppmt ₹ 2,411.63 9
10
11
pv ₹ 11,751.55 12
13
14
fv ₹ 15,609.24 15
16
17
rate 9.50% 18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
cumalative ppmt
100000