Softlineinstitute

Microsoft Office | DTP | DCA | Tally.ERP9 | DSE | ADTP | ADCA | Multimedia | Graphic Design | Web Design | Web Development | Programming | Hardware | Networking | DIPWD | DCAA | DIT | DCH | NTT | PTT | NPTT | AW | ECCE | PETT | PPTT | PHTT | 10TH | 12TH | BA | MA | BCA | MCA | BBA | MBA | B.COM | M.COM | B.TECH | M.TECH | B.SC(CS) | M.SC(IT) | MS Word | MS Excel | Advance Excel | MS Power Point | MS Outlook | Computer/IP | Corel Draw | Photoshop | Page Maker | Flash 2D | Internet | HTML | Hardware | Accounting | “C” | “C++” | VB | SL | VB. Net, ADO | ASP. Net, ADO | Net Bean Java | Illustrator | In Design | Hindi Typing | Java (Core ) | Java Advance | Autocad | English Typing | Front Page | MS Access | Dreamweaver | 3D Max

Wednesday, July 1, 2020

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
Yahoo.com
3

4
5
6
7
Yahoo.com
8


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.


No comments:

Post a Comment