Identify & Remove Duplicates
Product January February
Appliances 496 623
Binders and Binder Accessories 239 979
Binders and Binder Accessories 239 979
Bookcases 500 349
Chairs & Chairmats 579 635
Computer Peripherals 991 1133
Copiers and Fax 100 70
Chairs & Chairmats 579 635
TRIM RESULT
Bookcases
Chairs & Chairmats Chairs & Chairmats
Computer Peripherals
Binders and Binder Accessories
Copiers and Fax
CLEAN RESULT
PROGRAF PROGRAF
PARIET
CONCERTA
LEUSTATIN
VELCADE
LEUSTATIN
VELCADE
SUBSTITUTE RESULT
Version 1.0 vs 1.0
Version 1.1
Version 1.2
Version 1.3
Version 1.4
can also use FIND & REPLACE tool
PROPER, CONCANTENATE, LEN, UPPER, LOWER
Cities Country Proper
LAGOS Nigeria Lagos
CAMPINA GRANDE Brazil
NATAL South Africa
BELÉM Brazil
JOÃO PESSOA Brazil
PARIS France
CASTANHAL Brazil
MARANGUAPE Ceará
Sample Date TEXT LEFT
6/25/2022 Saturday June 2022 Satu
6/26/2022 Sunday June 2022
2/11/2020 Tuesday February 2020
10/14/2021 Thursday October 2021
1/10/2003 Friday January 2003
TEXT_TO_COLUMNS Names Domain
opara.ronaldo careerinsights tv
messi.anyanwu careerinsights.tv messi
lauren.eze@careerinsights.tv
oladimeji.mayowa@careerinsights.tv
sandra.rihanna@careerinsights.tv
timothy.odubanjo@careerinsights.tv
March April May June
2 8 55 48
1 6 46 35
1 6 46 35
51 0 1 40
35 18 11 38
8 11 15 27
0 0 0 0
35 18 11 38
Concatenate LEN UPPER LOWER
Lagos,Nigeria
RIGHT MID
urday
First Name Last Name
anyanwu
DATE: Create a valid date from year, month, and day in the right format
DATE
2/14/2022
6/28/2022
DateDif: (StartDate, EndDate, Method{"d" - days, "m" - months, "y" - years})
Difference between dates (DATEDIF: Get days, months, or years between two dates, DAYS, DAYS360, NETWORKDAYS, N
Start Date End Date DATEDIF Diff in Months Diff in years DAYS
4/17/1976 9/28/2020 16235 16235
6/7/2000 10/14/2020
11/15/2020 11/26/2020
3/8/2015 8/19/2022
DATEVALUE, MONTH, YEAR, WEEKDAY, WEEKNUM, ISOWeekNum, EDATE, EOMONTH
DateText DateValue Day Month Year WeekDay
1 Nov 2019 11/1/2019 6
2 Apr 2020 2
18 Jul 2022
28 Dec 1996
NOW: Get the current date and time
TODAY: Get the current date (CTRL ;)
, DAYS360, NETWORKDAYS, NETWORKDAYS.INTL)
DAYS360 NETWORKDAYS
-11594
-5311
-9
-1945
0
WeekNum
44
SUM, COUNT, COUNTA, MAX, MIN, AVERAGE, SUBTOTAL
Product January February March April May June
Appliances 496 623 2 8 55 48
Binders and Binder Accessories 239 979 1 6 46 35
Bookcases 500 349 51 0 1 40
Chairs & Chairmats 579 635 35 18 11 38
Computer Peripherals 991 1133 8 11 15 27
Copiers and Fax 100 70 0 0 0 0
Product January February March April May June
Appliances 496 623 2 8 55 48
Binders and Binder Accessories 239 979 1 6 46 35
Bookcases 500 349 51 0 1 40
Chairs & Chairmats 579 635 35 18 11 38
Computer Peripherals 991 1133 8 11 15 27
Copiers and Fax 100 70 0 0 0 0
COUNTIF SUMIF AVERAGEIF COUNTIFS SUMIFS
Count athlethes Sum of 2020 Pay
Sport Count of Athletes Sum of 2020 Avg Bonus per Sport with Bonus > for athletes with
per Sport Pay per Sport avg Bonus per Bonus > avg Bonus
Sport per Sport
Basketball 18 526.9 19.7333333333333 6 188.8
Tennis 6 203.8
(IF)
Athlete 2020 Pay (M) Bonus (M) Endorsements Sport Earnings Grade
Amar'e Stoudemire $26.7 $21.7 5 Basketball Above
Blake Griffin $23.0 $16.5 7 Basketball Above
Carmelo Anthony $29.4 $21.4 8 Basketball Above
Chris Bosh $21.8 $19.3 3 Basketball Above
Chris Paul $24.2 $18.7 6 Basketball Above
Deron Williams $20.0 $18.5 2 Basketball Above
Derrick Rose $36.6 $17.6 19 Basketball Above
Dirk Nowitzki $23.2 $22.7 1 Basketball Above
Dwight Howard $25.5 $20.5 5 Basketball Above
Dwyane Wade $29.9 $18.9 11 Basketball Above
Joe Johnson $22.5 $21.5 1 Basketball Below
Kevin Durant $31.9 $17.9 14 Basketball Above
Kobe Bryant $61.5 $30.5 31 Basketball Above
LeBron James $72.3 $19.3 53 Basketball Above
Li Na $23.6 $5.6 18 Tennis Above
Maria Sharapova $24.4 $2.4 22 Tennis Above
Novak Djokovic $33.1 $12.1 21 Tennis Above
Pau Gasol $21.8 $19.3 3 Basketball Above
Rafael Nadal $44.5 $14.5 30 Tennis Above
Roger Federer $56.2 $4.2 52 Tennis Above
Rudy Gay $18.6 $17.9 1 Basketball Above
Russell Westbrook $19.2 $14.7 5 Basketball Above
Serena Williams $22.0 $11.0 11 Tennis Above
Zach Randolph $18.8 $18.3 1 Basketball Below
Conditions
1. Earning Grade
Identify athlets that earn above or below the Average 2020 Pay
2. Athlete Class
Those whose Bonus> $15M and Endorsement > 15 are ELITES, others are REGULAR
3. Popularity Scale
Those whose Bonus> $15M or Endorsement > 20 are POPULAR, others are NON-POPULAR
4. Athlete Grade (based on pay)
< $20M - D
<= $40M - C
< $50M - B
>= $50M - A
(IF & AND) (IF & OR) (Nested IF) (VLOOKUP)
Athlete Class Popularity Scale Athlete Grade Althete's City Location
REGULAR POPULAR C Washington
REGULAR POPULAR C Montreal
REGULAR POPULAR C Carlifornia
REGULAR POPULAR C Abuja
REGULAR C Beijin
REGULAR D New Jersey
ELITES C London
REGULAR C New York
REGULAR C Abuja
REGULAR C Missisuaga
REGULAR C Texas
REGULAR C Washington
ELITES A London
ELITES A London
REGULAR C Baltimore
REGULAR C Zenith
REGULAR C Los Angeles
REGULAR C Lagos
REGULAR B Texas
REGULAR A Carlifornia
REGULAR D Ohio
REGULAR D New Jersey
REGULAR C Lagos
REGULAR D Sydney
