Interesting Functions And Formulas of Microsoft Excel

     1.   Design Your Own To - Do List.

Note:-

·         Select Cell B3:B9 à Go to Format à Format Cell à Custom à h:mm

(a)  To get increments of an hour

	A	B
1	01/05/2020	To Do list
2
3		7:00
4		8:00
5		9:00
6		10:00
7		11:00
8		12:00
9		01:00

Apply the following formula in cell B4 and drag B4 up to B9

=B3+(1/24)

(b)  To get increments of half an hour

	A	B
1	01/05/2020	To Do list
2
3		6:30
4		7:00
5		7:30
6		8:00
7		8:30
8		9:00
9		9:30

Apply the following formula in cell B4 and drag B4 up to B9

=B3+(1/48)

     2.   Calculate Total With Percentage.

	A	B
1	List Price	25500
2	Tax	8%
3	Discount	10%
4
5	Total Price	24786

Apply the following formula in cell B5

=B1*(1+B2)*(1-B3)

LOGICAL FUNCTIONS

     3.   AND function to Compare two Columns.

	A	B	C
1	Value 1	Value 2	And
2	45	5	False
3	8	78	False
4	85	65	True
	25	19	False
6	44	78	True
7	21	29	True
8	57	99	True
9	23	17	False
10	3	98	False

Apply the following formula in cell C2 and drag C2 up to C10

=AND(A2>20,B2>25)

     4.   AND function to show sales for a specific Period of Time.

	A	B	C
1	DATE 1	09/13/2009
2	DATE 2	09/21/2009
3
4	DATE	SALES	AND
5	09/11/2009	16764	False
6	09/12/2009	67139	False
7	09/13/2009	95642	False
8	09/14/2009	29645	True

Apply the following formula in cell C5 and drag C4 up to C8

=AND(A5>B1,A5<=B2)

        5.   OR function to check cells for text steps.

	A	B
1	Text	Valid
2	New	True
3	New	True
4	Old	False
5	Actual	True
6	Old	False
7	New	True
8	Actual	True

Apply the following formula in cell B2 and drag B2 up to B8

=OR(A2=”New”,A2=”Actual”)

     

       

       6.   IF function to compare Columns and return a Specific Result.

	A	B	C
1	Value 1	Value 2	Remark
2	800	567	Column A is Greater or Equal
3	634	386	Column A is Greater or Equal
4	190	935	Column B is Greater
5	537	269	Column A is Greater or Equal
6	290	397	Column B is Greater
7	165	836	Column B is Greater
8	753	297	Column A is Greater or Equal

Apply the following formula in cell C2 and drag C2 up to C8

=IF(A2>=B2,”Column A is Greater or Equal”,”Column B is Greater”)

      7.   IF function to check for Larger, Equivalent, or Smaller Values.

	A	B	C
1	Value 1	Value 2	Remark
2	689	563	Column A is Larger
3	926	972	Column A is Smaller
4	260	925	Column A is Smaller
5	698	369	Column A is Larger
6	189	189	Equivalent
7	280	936	Column A is Smaller
8	158	297	Column A is Smaller

Apply the following formula in cell C2 and drag C2 up to C8

=IF(A2>B2,”Column A is Larger”, IF(A2=B2,”Equivalent”,”Column A is Smaller))

  

8.     IF function to create your own Timesheet.

	A	B	C	D	E	F
1	Date	Day	Start	End	Target	Actual
2	03/03/2020	Mon	7:00am	4:00pm	8:00	9:00
3	03/04/2020	Tues	HO	--	8:00	8:00
4	03/05/2020	Wed	IL	--	8:00	8:00
5	03/06/2020	Thu	8:00am	5:00pm	8:00	9:00
6	03/07/2020	Fri	TR	--	8:00	8:00

Note:-

·         Select Cell F2:F6 à Go to Format à Format Cell à Custom à h:mm

·         HO = On Holiday, IL = Employee is ill, TR = On Training.

Apply the following formula in cell F2 and drag F2 up to F6

=IF(OR(C2=”TR”,C2=”IL”, C2=”HO”),E2,D2-C2)

      9.  IF statement between two numbers.

	A	B
1	Data Cell	150
2	Condition 1	100
3	Condition 2	999
4	Result if True	100
5	Result if False	0
6
7	Formula	100

Apply the following formula in cell B7

=IF(AND(B1>=B2,B1<=B3),B4,B5)

TEXT FUNCTIONS

     10.  LEFT and RIGHT function to separate a text string of numbers.

	A	B	C
1	Number	First Part	Second Part
2	K345327953	K34	5327953
3	K654398761	K65	4398761
4	K560982457	K56	0982457
5	K535276206	K53	5276206

·         Select cell B2 and type the following formula then Drag Down.

=LEFT(A2,3)

·         Select cell C2 and type the following formula then Drag Down.

=RIGHT(A2,7)

11.  LEFT function to convert Invalid numbers to Valid numbers.

	A	B
1	Invalid numbers	Valid numbers
2	153 -	-153
3	65 -	-65
4	98 -	-98
5	58 -	-58

Apply the following formula in cell B2 and drag B2 up to B5

= -LEFT(A2,LEN(A2)-1)

   12.  TRIM function to remove the extra space from text.

	A	B
1	Original Data	Trimmed Space
2	Ronnie Anderson	Ronnie Anderson
3	Tom                Boone	Tom Boone
4	Sally   Brooke	Sally Brooke
5	Jeremy          Hill	Jeremy Hill
6	Robert           Furlan	Robert Furlan
7	Mattias    Waldau	Mattias Waldau
8	David  White	David White

Apply the following formula in cell B2 and drag B2 up to B8

= TRIM(A2)

       13.  SEARCH function to separate first name from last name.

	A	B
1	Name	First Name
2	Ann Smith	Ann
3	Kay Jones	Kay
4	Laura Carter	Laura
5	Belinda Brown	Belinda

Apply the following formula in cell B2 and drag B2 up to B5

=LEFT(A2,SEARCH(“ “,A2)-1)

     14.  MID function to separate last name from first name.

	A	B
1	Name	First Name
2	Ann Smith	Smith
3	Kay Jones	Jones
4	Laura Carter	Carter
5	Belinda Brown	Brown

Apply the following formula in cell B2 and drag B2 up to B5

=MID(A2,SEARCH(“ “,A2)+1,100)

     15. EXACT function to compare Two Columns.

	A	B	C	D
1	Text A	Text B	Exact	IF
2	Hello	Hello	False	True
3	this is an example	This is an example	False	True
4	bernd	Bernd	False	True
5	132	132	True	True
6	06/03/2019	Sunday, June 03, 2019	False	False
7	hi you	hi you	True	True
8	123456	1234567	False	False

·        Select cell C2 and type the following formula then Drag Down.

=EXACT(A2,B2)

·        Select cell D2 and type the following formula then Drag Down.

=IF(A2=B2,True,False)

     16.  REPLACE function to replace and calculate.

Note:-

·         Select Cell D2:D7 à Go to Format à Format Cell à Custom à h:mm

	A	B	C	D
1	DATE	START	END	TOTAL
2	06/20/2020	7.5	13.51	6:46
3	06/21/2020	6.5	8.3	1:58
4	06/22/2020	12.5	16.5	4:00
5	06/23/2020	10.21	14.33	4:12
6	06/24/2020	8.09	15.11	7:02
7	06/25/2020	7.57	17.55	9:58

Apply the following formula in cell D2 and drag D2 up to D7

=REPLACE(C2,SEARCH(“.”,C2),1,”:”)-REPLACE (B2,SEARCH(“.”,B2),1,”:”))

     17.  PROPER function to convert initial characters from lowercase to uppercase.

	A	B
1	Old Text	New Text
2	SmITh	Smith
3	JoNEs	Jones
4	lAuRa	Laura
5	beLINda	Belinda
6	ANdReA	Andrea

Apply the following formula in cell B2 and drag B2 up to B6

=PROPER(A2)

     18.  DOLLAR function to convert numbers to currency in the text format.

	A	B
1	Old Value	New Value
2	17.38	$17.38
3	188.35	$188.35
4	76.37	$76.37
5	22.93	$22.93
6	111.11	$111.11

Apply the following formula in cell B2 and drag B2 up to B6

=DOLLAR(A2)

Apply the following formula in cell B2 and drag B2 up to B6

=DOLLAR(A2)

DATE AND TIME FUNCTIONS

     
     19.  Custom formatting to display the day of the week.

Note:-

·         Select Cell B2:B6 à Go to Format à Format Cell à Custom à type dddd in the type box à OK

	A	B
1	Date	Weekday
2	07/07/2020	Tuesday
3	07/08/2020	Wednesday
4	07/09/2020	Thursday
5	07/10/2020	Friday
6	07/11/2020	Saturday

Apply the following formula in cell B2 and drag B2 up to B6

=(A2)

     20.  TODAY function to check for future dates.

	A	B	C
1	Date	Future
2	07/07/2019	No
3	07/08/1996	No
4	07/11/2025	Yes
5	07/10/2024	Yes
6	07/09/1998	No

Apply the following formula in cell C1

=TODAY()

Apply the following formula in cell B2 and drag B2 up to B6

=IF(A2<=Today(),”No”,”Yes”)

     21.  NOW function to show the current time.

Note:-

·      Select Cell A1 à Go to Format à Format Cell à Date à Select the format “3/12/14 6:40 p.m” à OK

	A
1	Current Date & Time

Apply the following formula in cell A1

=Now()

     22.  DATE function to combine columns with date parts.

	A	B	C	D
1	Year	Month	Day	Date
2	2019	03	27	03/27/2019
3	2020	05	08	05/08/2020
4	2019	08	18	08/18/2019
5	2021	02	28	02/28/2021
6	2019	04	30	04/30/2019
7	2020	03	07	03/07/2020

Apply the following formula in cell B2 and drag B2 up to B6

=DATE(A2,B2,C2)

     23.  YEAR function to extract the year part of the date.

	A	B
1	Date	Year
2	11/30/2008	2008
3	06/01/2010	2010
4	08/18/1999	1999
5	02/28/1906	1906
6	04/30/2020	2020

Apply the following formula in cell B2 and drag B2 up to B6

=YEAR(A2)

     24.    MONTH function to extract the month part of the date.

	A	B
1	Date	Month
2	11/30/2008	11
3	06/01/2010	06
4	08/18/1999	08
5	02/28/1906	02
6	04/30/2020	04

Apply the following formula in cell B2 and drag B2 up to B6

=MONTH(A2)

     25. DAY function to extract the day part of the date.

	A	B
1	Date	Day
2	11/30/2008	30
3	06/01/2010	01
4	08/18/1999	18
5	02/28/1906	28
6	04/30/2020	30

Apply the following formula in cell B2 and drag B2 up to B6

=DAY(A2)

     26.  WEEKDAY function to calculate with different hourly pay rates.

	A	B	C	D	E
1	Date	Day	Hours	Hourly Rate (Mon to Fri)	Payment
2	5/1/2020	Fri	1	$12.50	$12.5
3	5/2/2020	Sat	1.5		$27.75
4	5/3/2020	Sun	7.5	Hourly Rate (Sat to sun)	$138.75
5	5/4/2020	Mon	8	$18.50	$100
6	5/5/2020	Tues	4.3		$53.75
7	5/6/2020	Wed	9		$112.5
8	5/7/2020	Thu	2.6		$32.5

Apply the following formula in cell E2 and drag E2 up to E8

=IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),C2*$18.50,C2*$12.50)

     27.    WEEKNUM function to determine the week number.

	A	B
1	Date	Week
2	11/30/2008	49
3	06/01/2010	23
4	08/18/1999	34
5	02/28/1906	9
6	04/30/2020	18

Apply the following formula in cell B2 and drag B2 up to B6

=WEEKNUM(A2)

     28.  INT function to calculate the age of employees.

	A	B	C	D
1	Employee	Birthday	Today	Age
2	Tucker	6/23/1969	5/4/2020	51
3	Blaze	12/2/1979	5/4/2020	41
4	Julius	3/16/1996	5/4/2020	24
5	Frank	7/5/1972	5/4/2020	48
6	Jonas	8/1/1997	5/4/2020	23

Apply the following formula in cell D2 and drag D2 up to D6

=INT(C2-B2)/365

BASIC STATISTICAL FUNCTIONS

     29.   MAX function to determine the largest value in a range.

	A	B	C	D
1	Max Value	99
2
3	02	98	56	53
4	30	17	82	05
5	80	16	72	43
6	01	77	10	99

Apply the following formula in cell B1

=MAX(A3:D6)

     30.  MIN function to detect the smallest value in a column.

	A	B
1	Values	Min. Value
2	97	07
3	32
4	62
5	07
6	19

Apply the following formula in cell B1

=MIN(A2:A6)

     31.  SMALL function to find the smallest values in a list.

	A	B	C
1	765	1	110
2	987	2	373
3	474	3	474
4	373
5	765
6	963
7	110
8	732

Apply the following formula in cell C1

=SMALL(A1:A8,1)

Apply the following formula in cell C2

=SMALL(A1:A8,2)

Apply the following formula in cell C3

=SMALL(A1:A8,3)

     32.  SMALL function to compare prices and select the cheapest offer.

	A	B	C	D	E
1	Supplier	Offer	1	Comp.3	1001.86
2	Comp.1	1005.99	2	Comp.5	1002.21
3	Comp.2	1003.89	3	Comp.2	1003.89
4	Comp.3	1001.86
5	Comp.4	1092.98
6	Comp.5	1002.21

·        
     Select cell D1 and type the following formula then Drag Down.

=INDEX(A2:A6,MATCH(SMALL(B2:B6,C1),B2:B6,0))

·        Select cell E1 and type the following formula then Drag Down.

=SMALL(B2:B6,C1)

     33.  COUNT function: - Use for count rows and columns.

	A
1	75
2	97
3	29
4	20
5	10
6	53
7
8	6(rows)

Apply the following formula in cell A8

=COUNT(A1:A6)

     34.   COUNTBLANK function: - Use for count blank columns or rows.

	A
1	75
2
3	29
4
5	10
6	53
7
8	2

          

Apply the following formula in cell A8

=COUNTBLANK(A1:A6)

    
      35.  LEN function: - Use for count letters of words.

	A	B
1	Characteristically	18

Apply the following formula in cell B1

=LEN(A1)

      36.    AVERAGE function: - We can calculate Statistical mean by AVERAGE function.

	A	B
1	Base Salary	MEAN
2	8800	7875
3	7400
4	9400
5	8300
6	7600
7	6700
8	7800
9	7000

Apply the following formula in cell B2

=AVERAGE(A2:A9)

      37.  MEDIAN function: - It returns the median (middle number) in a group of supplied numbers.

	A	B	C
1	Items	Order Date	Amount
2	Cherry	20-Apr	$100
3	Banana	25-Apr	$70
4	Apple	26-Apr	$130
5	Banana	1-May	$250
6	Apple	1-May	$90
7	Cherry	3-May	$115
8	Banana	6-May	$90
9	Banana	10-May	$350
10	Apple	18-May	$210
11
12	Median Amount		$115
13	Median Date		1-May

Apply the following formula in cell C12

=MEDIAN(C2:C10)

Apply the following formula in cell C13

=MEDIAN(B2:B10)

     38.  MODE function: - It returns the most frequently occurring number in a numeric data set.

	A	B	C	D	E
1	56	98	78	45	18
2	15	62	89	08	80
3	67	45	65	27	28
4	26	62	81	67	28
5	18	91	27	36	72
6	91	18	28	94	39
7	29	20	37	32	37
8
9	Mode	18	=MODE(A1:E7)

       

     39.    CONCATENATE Function: - Use to combine two or more cells.

	A	B	C	D
1	Source Data		Result	Formula
2	Project	1	Project1	=CONCATENATE(A2,B2)

LOOKUP & REFERENCE FUNCTIONS

     40.    CHOOSE function: - It returns a value from a list of values based on a given position.

	A	B
1	Websites	Result
2	ArtEducation.com	Yahoo.com
3	TechEducation.com
4	NetEducation.com
5	EducationArt.com
6	GetEducation.com
7	Yahoo.com
8	EducationTech.com

Apply the following formula in cell B2

=CHOOSE(6,A2,A3,A4,A5,A6,A7,A8)

     41.                

	A	B
1	Name	Proper Name
2	Ronnie Andreson	Mr. Ronnie Andreson
3	Tom Bonne	Mr. Tom Bonne
4	Sally Brooke	Mr. Sally Brooke
5	Jeremy Hill	Mr. Jeremy Hill
6	David White	Mr. David White
7	Mattias Waldau	Mr. Mattias Waldau
8	Robert Furhan	Mr. Robert Furhan

Apply the following formula in cell B2 and drag B2 up to B8

=”Mr. “&A2

Writer : Mr. Krishan Kumar Saini

Today we have learnt some interesting  Functions And Formulas of Microsoft Excel. Hope this lesson will be helpful for you.