Unit-1
OR
Find The value where Colour should be "RED" or size should be "Small"
Example-1
Color Size Result
Red Small TRUE
Green Large FALSE
Red Medium TRUE
Orange Small TRUE
Red Large TRUE
AND
Find The value where Colour should be "RED" and size should be "Small"
Example-2
Color Size Result
Red Small TRUE
Green Large FALSE
Red Medium FALSE
Orange Small FALSE
Red Large TRUE
Example-3
Calculate the Incentive as per the above given criteria
Sales Incentive
<40000 500
<60000 1000
<80000 1500
>80000 2000
Salesman Sales Incentive
Rajesh 108248 2000
Mohit 80000 2000
Alan 56000 1000
Sunil 100994 2000
Brijesh 64247 1500
Bob 78125 1500
Neera 89647 2000
Harry 50760 1000
Lovesh 29487 500
Sandeep 75000 1500
Total 15000
SUM
SUM
NAME WEEK 1 WEEK 2 WEEK 3 WEEK 4 WEEK 5 WEEK 6 WEEK 7
PANKAJ 12433 9726 10763 13737 6427 8450 6034
KUNDAN 12246 14685 13200 8630 14550 8264 5512
ADITYA 13912 5812 13108 10638 10446 13433 10101
MIN
MINIMUM
NAME WEEK 1 WEEK 2 WEEK 3 WEEK 4 WEEK 5 WEEK 6 WEEK 7
PANKAJ 12433 9726 10763 13737 6427 8450 6034
KUNDAN 12246 14685 13200 8630 14550 8264 5512
ADITYA 13912 5812 13108 10638 10446 13433 10101
MAX
MAX
NAME WEEK 1 WEEK 2 WEEK 3 WEEK 4 WEEK 5 WEEK 6 WEEK 7
PANKAJ 12433 9726 10763 13737 6427 8450 6034
KUNDAN 12246 14685 13200 8630 14550 8264 5512
ADITYA 13912 5812 13108 10638 10446 13433 10101
Percentage
Percentage
NAME HINDI ENG MATHS SCIENCE SST COMP TOTAL M
PANKAJ 73 57 93 51 91 76 441
KUNDAN 73 87 66 64 71 51 412
ADITYA 54 62 53 63 69 72 373
Average
NAME HINDI
PANKAJ 73
KUNDAN 73
ADITYA 54
HARIOM 79
SHIVAM 56
SATYAM 66
MEGLOWS 83
PALLAVI 64
PARI 72
SUGGA 53
CHASMIS 72
SHARMA 88
KARAN 50
RAHUL 89
RAJAT 71
RAJNISH 72
AMAN RAJ 91
SUMIT TYAGI 87
BARUN 67
Average 71.57894737
SUMIF
Region Product Status Order Date Order Qty
South Oneplus Delivered 9-Sep-19 321
North Oneplus Pending 22-Apr-19 468
East Nokia In Transit 23-Mar-19 268
South Oneplus In Transit 18-Jun-19 494
North Vivo Delivered 24-Apr-19 140
South Oneplus Pending 20-Sep-19 155
North Samsung Pending 18-Jul-19 499
West Oneplus Delivered 25-Aug-19 325
North Nokia Pending 31-Dec-19 151
South Oneplus In Transit 4-Dec-19 466
Central Vivo Pending 17-Apr-19 213
Central Vivo Delivered 4-Aug-19 219
South Nokia Delivered 24-Oct-19 147
Central Vivo Delivered 23-Oct-19 63
Central Oneplus Pending 4-Sep-19 363
South Vivo Delivered 5-Feb-19 338
SUMIFS
Sum Values between 5-Jul-2018 and 10-Jul-2018
Custom Date Amt
1-Jul-18 18
2-Jul-18 15
10-Jul-18 14
14-Jul-18 11
5-Jul-18 14
6-Jul-18 15
7-Jul-18 12 Sum 77
8-Jul-18 12
9-Jul-18 20
10-Jul-18 15
5-Jul-18 12
7-Jul-18 18
11-Jul-18 12
12-Jul-18 10
3-Jul-18 11
14-Jul-18 20
15-Jul-18 20
COUNT COUNTA
Items >> Counts Non blank cells onl
5
-8 Country Value
10 Japan 260
0 China abc
22 India
-12 Japan ""
34 Shrilanka RK_22
50 Pakistan
America 22-May
Bhutan !
Count >> Numeric values>> 8 India
Nepal #DIV
Count non blank 7
DATEDIF
diff between date (in Year Month and Day)
Difference
Start Date End Date Year Month Day
15-Aug-47 16-Nov-25 78 3 1
17-Feb-00 16-Nov-25 25 8 30
14-Jan-01 16-Nov-25
15-Aug-47 16-Nov-25
25-Dec-24 16-Nov-25
13-Apr-19 16-Nov-25
Mode Mean
Value-1 Value-2 Value-3 Mode Value-1 Value-2 Value-3
12 43 12 12 12 43 12
45 22 45 45 45 22 45
67 12 12 12 67 12 12
9 45 9 9 9 45 9
56 678 56 56 56 678 56
2387 9 9 9 2387 9 9
9876 56 9876 9876 9876 56 9876
45 78 45 45 45 78 45
Data Validation
Registration Fee
400 Before 31-Jan-2021
500 After 31-Jan-2021
Name Date Fee
Rajesh Kumar 1-Jan-21 400
Mamta Jain 2-Jan-21 400
Rajeev Aggrawal 7-Jan-21 400
Surender Singh 12-Jan-21 400
Saurabh Gupta 17-Jan-21 400
Anil Kumar 22-Jan-21 400
Umesh Kumar 27-Jan-21 400
Kapil Singh 1-Feb-21 500
Mohit Sinha 6-Feb-21 500
Dinesh Gupta 11-Feb-21
Ramesh 16-Feb-21
Tribhuvan 21-Feb-21
Sattu Ram 26-Feb-21
Bhagwan Das 3-Mar-21
Bhuwan 8-Mar-21
Brahm Singh 13-Mar-21
Rinku 18-Mar-21
Naresh 23-Mar-21
Ram Naresh 28-Mar-21
Ratan Singh 2-Apr-21
SUM
WEEK 8 WEEK 9 WEEK 10 TOTAL SUM OF INDIVIDUAL SELLER
9965 10176 11082 98793
9382 5655 13509 105633
5671 7384 14669 105174
M
WEEK 8 WEEK 9 WEEK 10 MINIMUM
9965 10176 11082 6034
9382 5655 13509 5512
5671 7384 14669 5671
WEEK 8 WEEK 9 WEEK 10 MINIMUM
9965 10176 11082 13737
9382 5655 13509 14685
5671 7384 14669 14669
centage
AVERAGE MMIN MARKS MAXIMUM MARKS PERCENTAGE
73.5 51 93 73.5
68.666667 51 87 68.66666667
62.166667 53 72 62.16666667
Product Order Qty
Oneplus 2592
Nokia 566
Vivo 973
Samsung 499
Realme 0
COUNTBLANK
unts Non blank cells only >> Counts blank cells only
Country
Japan
China
India
Japan
Shrilanka
Pakistan
America
Bhutan
India
Nepal
EOMONTH
To find end date or 1st date of a month
1-Jan-19 31-Jan-19
12-Jan-19 31-Jan-19
3-Mar-19 31-Mar-19
3-Dec-19 31-Dec-19
4-Mar-19 31-Mar-19
4-Feb-19 28-Feb-19
16-Nov-25 30-Nov-25
MEDIAN
Mean/Avg Value-1
22.333333 12
37.333333 45
30.333333 67
21 9
263.33333 56
801.66667 2387
6602.6667 9876
56 45
OTAL SUM OF INDIVIDUAL SELLER
COUNTIF
OUNTBLANK Category Status
Counts blank cells only Hardware Issue Complete
New Hardware reqst Cancelled
Value Software Issue Complete
26 Network Problem Complete
88 Unknown error Complete
User error Pending
& System fault Cancelled
90 Reports related Pending
Laptop not Started Cancelled
* Internet issue Pending
Key board damaged Complete
36 Printer replacement Pending
12@AS Total Text 12
WORKDAY
This function excludes weekends (Saturday/Sunday and other specified dates)
Order Date Transit time Delivery Date Holiday
Fri 18-May-18 2 Tue 22-May-18
Wed 28-Feb-18 5 Thu 08-Mar-18 1-Mar-18
Fri 20-Apr-18 7 Wed 02-May-18 24-Apr-18
Thu 28-Dec-17 8 Tue 09-Jan-18 31-Dec-17
Value-2 Value-3 Median
43 13 13
22 67 45
89 12 67
45 88 45
678 678 678
6 9 9
56 9876 9876
78 45 45
COUNTIFS
Branch Order type Product Code Product
TN Single 1201031 Printer
Count which are complete. KA Double 5100301 Scanner
5 AS Blended 1000304 Mobile
AS Single 1000305 Laptop
Complete which are Cancelled. AS Blended 1000307 Mobile
3 BH Double 1000201 Printer
CH Blended 1201004 Laptop
Complete which are Pending. CH Double 1203002 Scanner
4 FBD Single 1000304 Printer
FBD Single 1201004 Scanner
FBD Double 1204003 Mobile
MU Blended 1201034 Laptop
JK Double 1000201 Mobile
JK Double 5101013 Laptop
KO Single 1000301 Scanner
DL Single 1001001 Mobile
DL Single 1201002 Printer
GJ Double 1000304 Laptop
GJ Single 5100501 Laptop
HP Blended 1201006 Scanner
HR Double 1001201 Printer
Noida Double 1201003 Mobile
MP Blended 1000601 Mobile
[Link]
Order Date Delivery Days Weedend Holiday Final workday
Fri 18-May-18 1 1 Sat 19-May-18
Mon 30-Apr-18 5 1 Mon 07-May-18 Sat 05-May-18
Color Size Status PKD Courier Amount
White Medium Delivered 20-Aug-17 DHL 47,877
Grey Medium Delivered 21-May-19 DTDC 44,880
Blue Small Delivered 14-Oct-19 Superfast 24,284
Blue Medium Delivered 31-Mar-18 DHL 29,834
Grey Large Dispatched 24-Aug-17 Fdx 17,122
Black Medium Out of Stock 13-Oct-17 DTDC 24,507
Black Large Pending 1-Jul-19 DHL 38,924
Black Medium Delivered 13-Jan-17 DTDC 23,885
Grey Medium Dispatched 26-Dec-18 DTDC 26,383
Grey Large Pending 17-Jun-18 Fdx 38,786
White Medium Delivered 24-Dec-18 Blue Dart 26,462
Blue Small Dispatched 16-Dec-19 Superfast 47,904
Blue Large Out of Stock 6-Dec-19 Mail cart 7,541
Grey Medium Delivered 23-May-19 Superfast 5,012
White Large Dispatched 25-Jun-19 DTDC 45,451
Blue Small Out of Stock 4-Jul-17 DHL 15,904
Black Large Dispatched 11-Apr-17 Mail cart 34,244
Black Large Dispatched 10-Jan-18 DTDC 46,063
White Large Delivered 3-Feb-18 Fdx 40,138
White Small Delivered 28-Jul-17 DHL 27,530
Black Large Delivered 25-Nov-18 Fdx 33,116
White Small Delivered 1-Aug-18 Mail cart 30,538
Blue Medium Dispatched 6-Jan-18 Blue Dart 45,206
NETWORKINGDAYS
Start Date End Date Networking days
Mon 21-May-18 Tue 22-May-18 Mon 14-May-18 Sun 20-May-18 5
Sun 01-Apr-18 Mon 30-Apr-18 21
Mon 01-Oct-18 Wed 31-Oct-18 23
Mon 01-Jan-18 Mon 31-Dec-18 261
1-Mar-21 31-Mar-21 23
Branch Product Color
TN Printer White
KA Scanner Grey
AS Mobile Blue
BH Laptop Black
CH Printer Grey
FBD Mobile Black
MU Scanner Blue
JK Laptop White
KO Printer Blue
[Link]
Net working days (exl Saturday/Sunday with other specified holiday list)
Start Date End Date Networking days
Mon 14-May-18 Sun 20-May-18
Sun 01-Apr-18 Mon 30-Apr-18 21
Wed 01-Aug-18 Fri 31-Aug-18
1-Mar-21 31-Mar-21 22
Sat 01-Dec-18 Mon 31-Dec-18
Courier Total Count
DHL 1
DTDC
Superfast
Fdx
Blue Dart
Mail cart
Superfast
Blue Dart
Fdx 0
Networking days incl holidays
20 1-Apr-18 14-Apr-18 18-Apr-18
15-Aug-18 26-Aug-18
26 29-Mar-21
25-Dec-18