Magic Function
MANGO Market Price Qty(Kg)
Agra 150 2
Jha ns i 140 3
Noi da 170 4
Gurga on 290 5
Mumba i 320 6
Ka npur 140 7
MANGO Market Price Qty(Kg) Value1 Value2
Agra 150 2 300 300
Jha ns i 140 3 420 280
Noi da 170 4 680 340
Gurga on 290 5 1450 580
Mumba i 320 6 1920 640
Ka npur 140 7 980 280
Functions:
DATE("year","month","day")
NOW()
TODAY()
TEXT TO COLUMNS and DATA CLEANING FUNCTIONS :
TEXT TO COLUMNS and DATA CLEANING FUNCTIONS :
Name
Bäte, OliverGE
Baumgartner, ThomasGE
Bender, MichaelBT
First name Last name
Beyer v. Morgenstern, IngoGE
Beyer v. Morgenstern, IngoGE
Goland, AnthonyDC
Kanarek, LarryDC
Lal, ShyamBT
Latoff, RobertCL
Mendonca, LennySF
Orr, GordonGC
Bäte, OliverGE
Return the specified number of
characters from the start of a text string
=LEFT(text,[Num_Chars])
Bäte, OliverGE
Return the specified number of
characters from the end of a text string
=RIGHT(text,[Num_Chars])
Bäte, OliverGE
Return the characters from the middle
of a text string, given a starting position
& length.
=MID(text, start_num, [num_chars])
Bäte, OliverGE
Return the number of characters in a
text string.
=LEN(text)
Bäte, OliverGE
Convert the text string into proper case
that means, First letter is upper case &
other letters is lower case.
=proper(text)
Bäte, OliverGE
Return the characters into “UPPER
CASE” / “ lower case”
=upper(text)
=lower(text)
1) Mallika Gandhi
2) Mallika Gandhi
3) Mallika Gandhi
=TRIM(text)
Q-Please use Data cleaning functions to fill up the table below
Code Actual Name First name Last name
13217200000 Std. Cost Of Goods Sold - Bäte, OliverGE
13217200250 Cost of Goods Sold Other Material - Baumgartner, ThomasGE
13217200265 Cost of Goods Sold Laundry Overhead - Bender, MichaelBT
13217200270 Cost of Goods Sold Refrig. Mat - Beyer v. Morgenstern, IngoGE
13217000005 Gas & Elec - Beyer v. Morgenstern, IngoGE
13217000006 Purchase Discount - Goland, AnthonyDC
13217201160 Sub Advertising Maytag - Kanarek, LarryDC
13217010010 Cycle Count Adj - Lal, ShyamBT
13217010060 Scrap Steel Sales - Latoff, RobertCL
13217112000 Rate Variance - Mendonca, LennySF
13219110000 Depreciation Exp 44,870 Orr, GordonGC
13219111000 Depreciation CL . 23,875 Ostrowski, KennethAT
13218111000 Maintenance Materials 1 Pinkus, GarySF
13218112000 Maintenance P&E 83 Rall, WilhelmGE
13218223000 Cleaning And Sundries 414 Schrader, JürgenGE
13218226000 Waste Removal 3,945 Simensen, Simen VierSC
• Conditions is met or not, check it.
• Check if value is True or false.
=IF(logical test, [value_if_true], [value_if_false])
• Check if all conditions are true.
=AND(logical value1, logical value2….))
• Check if any of the conditions are
true.
=OR(logical1, logical2….))
Q)-Mr. Sherlock Holmes, a first year MBA student, has just received his results of his semester, which is
shown in the table below
Score obtained by Sherlock
Subject name Maximum score
Holmes
Module 1 70 90
Module 2 80 100
Module 3 55 80
Module 4 84 95
Module 5 79 100
Module 6 64 100
Module 7 88 90
Module 8 70 100
Module 9 91 100
Unable to decide whether his scores are good or not, he asks his senior, Mr James Bond to help him out.
Bond takes out a high-tech, fancy digital device (called the iScore) that asks a set of questions and based on the
answer,
provides a text message as shown in the table below. What would be the reponse of the iScore when Sherlock Holmes
enters his scores ?
Unable to decide whether his scores are good or not, he asks his senior, Mr James Bond to help him out.
Bond takes out a high-tech, fancy digital device (called the iScore) that asks a set of questions and based on the
answer,
provides a text message as shown in the table below. What would be the response of the iScore when Sherlock Holmes
enters his scores ?
Message displayed if criteria Message displayed if What should be the response based
Criteria
met criteria not met on Sherlock Holmes score?
Score in module 3 is greater than 75 "Well done" "NA"
Score in module 7 is greater than 80 and
"Great success!!!" "NA"
maximum score is less than 100
Score in both module 4 and module 5 is
"Super" "NA"
greater than 80
Score in either module 1 or module 2 is
"Unimpressive" "NA"
less than 75
Score in both module 5 and module 6 is
"You're doomed!!!" "NA"
less than 70
