Scribd
Upload
89 views17 pages

Practical 3

Uploaded by

Chirag Takkar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
89 views17 pages

Practical 3

Uploaded by

Chirag Takkar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd

What is the income of all rappers who like the yellow color?

Rapper Favorite color Income (millions)


Biz Khalifa Black 10 No
Ice Cone Yellow 20 Yes
Snoop Catt Black 15 No
Cardi C Yellow 17 Yes
Ray Z Black 44 Yes
3Pac Yellow 90 Yes
Sum: 127 < - Insert formula here!

196

127
How many rappers like the color yellow?
Rapper Favorite color
Biz Khalifa Black
Ice Cone Yellow
Snoop Catt Black
Cardi C Yellow
Ray Z Black
3Pac Yellow
Count: 3 < - Insert formula here!

3
What is the average yearly income in Ohio?
State Employee Name Income (thousands)
Minnesota John 77
Ohio Jake 71
New York Jill 75
Minnesota Dilip 67
Ohio Dana 89
Texas Kumar 87
California Jose 77
Minnesota Muhammad 60
Ohio Elise 83
Average income:
Ohio 81 <= Insert formula here

81
The following table details the revenue by bank account number
Calculate the total revenue from "Gold" accounts in the State of NY
Account # Type State Revenue
1 Gold NY 492
2 Silver PA 124
3 Gold NJ 555
4 Gold NY 100
5 Bronze NY 8
6 Bronze MA 201
7 Gold NY 20
8 Silver PA 43
9 Gold PA 108
10 Bronze NJ 172

Answer: 612 <= Insert formula here

612
0
Use COUNTIFS to calculate the number of boys over the age of 13
Name Boy/Girl Age
Alex Boy 12 No
Danny Boy 14 Yes
Gabby Girl 24 Yes
Kris Girl 32 Yes
Taylor Boy 12 No
Vic Boy 17 Yes

Answer: 2 <- Insert formula here!

2
Remove the duplicate values from the following column:

Name
Harry #NAME?
William #NAME?
Sean #NAME?
Sarah #NAME?
Tom #NAME?
Thomas #NAME?
Tim #NAME?
Sammy #NAME?
Tina #NAME?
Jack #NAME?
#NAME?

Now, repeat the same action, but remove entire row based on the name duplicates!

Name State
Harry Arizona
William Texas
Sean New York
Sarah Washington
Tom Georgia
Thomas California
Tim North Carolina
Sammy Oregon
Tina Washington
Jack Arkansas

Finally, remove duplicates only for rows where the name and the car model are the same!

Name State Car Model


Harry Arizona Toyota
William Texas Dodge
Sean New York Honda
Sarah Washington BMW
Tom Georgia Ford
Thomas California Lexus
Tim North Carolina Seat
Sammy Oregon VW
Harry New York Toyota
Tina Washington Audi
Sean Delaware Mercedes
Jack Arkansas Mini
Jack Florida Porsche
1. Split the following column to columns:

Name Age Favorite Color


Dan 33 Yellow
Gil 27 Red
Tom 33 Blue
Sammy 28 Black
Sharon 22 Pink

2. Convert the American Dates to European Dates:

Date Date
5/20/2015 5/20/2015
3/15/2017 3/15/2017
4/16/2016 4/16/2016
9/30/2015 9/30/2015
4/5/2017 5/4/2017

3. Convert the following values from numbers to text:

ID Is it text?
3435 FALSE
3245 FALSE
2346 FALSE
5635 FALSE

3435 TRUE
3245 TRUE
2346 TRUE
5635 TRUE
Filename Example # Starting Page Last Page File type
Example1_pg22-[Link] 1 22 33 pdf
Example2_pg34-[Link] 2 34 38 docx
Example5_pg66-[Link] 5 66 78 xlsx
Example7_pg88-[Link] 7 88 95 pdf

Name First Name Uniq Regd [Link] Domain


anurag4755.bbad24@[Link] anurag 4755 bbad [Link]
anush4756.bbad23@[Link] anush 4756 bbad [Link]
arham4757.bbad21@[Link] arham 4757 bbad [Link]
bhavjot4758.bbad23@[Link] bhavjot 4758 bbad [Link]
bhawna4759.bbad23@[Link] bhawna 4759 bbad [Link]
deepinder4760.bbad20@[Link] deepinder 4760 bbad [Link]
dev4761.bbad23@[Link] dev 4761 bbad [Link]
devander4762.bbad22@[Link] devander 4762 bbad [Link]
This
is
my
pen

This is my pen

This is my pen
1 Highlight all the dates after October 06, 2018:

Date
11/3/2018
5/11/2019 10/6/2018
12/3/2018
9/26/2019
4/12/2018
1/3/2019
5/6/2018
6/21/2019
7/24/2019
4/22/2018
6/8/2019
10/17/2017
1/28/2018
3/19/2019
8/18/2019
10/19/2017

2 Highlight all grades above average:

Grades
59
56
75
99
86
72
89
67
76
80
71
56
63
100
100

3 Highlight all the duplicate names:

John
Danny
Dean
Donny
Jake
Jill
Hannah
Jake
John
Danny
Dean

4 Highlight all cells greater than the MEDIAN of the range:

79 78
59
60
78
87
88
71
78
99
54
57
95
55
91
76
79

5 Identify top 20 % candidates as per CGPA

Student Name CGPA


Matt 6.45
Manoj 6.8
Ansh 8.45
Sam 4.6
Bhavjot 7.2
Abhilasha 6.35
Kiran 7.25
Hritika 8.85
Rohit 5.2
Shivam 5
Use UNIQUE to extract only unique
values from list:
Numbers
999
1,111
2
2
999
1,111
999
4
4

#NAME? <= Insert formula here #NAME?


#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
If all numbers are greater than 30, return "Good", otherwise return "Bad"

Number 1 Number 2
40 25 Bad <= Insert formula here

If at least one number is greater than 30, return "Good", otherwise return "Bad"

Number 1 Number 2
40 25 Good <= Insert formula here

let’s assume that you have an insurance company that provides an insurance discount to drivers over the age of 50 or those w
A healthy customer is a customer who doesn’t smoke, doesn’t drink and isn’t overweight
Identify which customers will be entitled to an insurance discount?

Name Age Smoking Drinking Overweig Healthy Insurance healthy Insurance


ht discount

Jane 52 Yes No Yes No Yes No Yes


Jill 56 No No No No Yes Yes Yes
Ralph 57 Yes Yes Yes No Yes No Yes
David 68 No No No No Yes Yes Yes
Rita 20 No No Yes No No No No
Tina 26 Yes Yes Yes No No No No
Joseph 57 Yes No No No Yes No Yes
Marc 55 Yes No Yes No Yes No Yes
Zack 20 No Yes Yes No No No No
over the age of 50 or those who lead a healthy lifestyle
ID Number Name Age
15335 Yoav 40
57564 Danny 50 Syntax
=VLOOKUP(loo
73546 Guy 61
66475 Rafi 23 lookup_value –
54746 Lev 30
table_array – th
note that the ra
column in whic
What is the age of ID 57564?
col_index_num
number should
ID Number 66475
Age 23 <- Please enter formu [range_lookup]
always type 0 (
looking for. 1 st

Use the VLOOKUP function to find where Tina and Dani work:

Name Workplace
Dani Netflix
Tina Disney

List of employees:

Name Age Company


Sean 35 Amazon
Sarah 25 Google
Yoav 48 Microsoft
Joe 37 Apple
Dani 52 Netflix
Tina 32 Disney
Sharon 33 Tesla
Gil 45 Facebook
Syntax
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]

lookup_value – what we are looking for – this could be a text, number, or a single cell reference

table_array – the range in which we will lookup for our value and its corresponding result. Please
note that the range must start from the column which contains the value, and should contain the
column in which we have our result.

col_index_num – What is the column number from which we want to return the result? The
number should be relative to the first column in the selected range in table_array.

[range_lookup] – Which range lookup method should be used. 0 is the default, so you should
always type 0 (or FALSE), which means “Exact Match” – Go to the exact match to the value I’m
looking for. 1 stands for “Approximate match”, and it should not be used on most cases.

Common questions

Powered by AI

Three out of five accounts from NY are classified as 'Gold,' representing a proportion of 60% . The total revenue from these 'Gold' accounts is $612, consisting of revenues $492, $100, and $20 from the respective accounts .

The UNIQUE function effectively filters out duplicate numerical values, returning a list only with unique numbers. This function is applied to extract unique values even when duplicates are initially present .

The top 20% of students by CGPA includes those above 7.8, calculated from the dataset. Hritika (8.85) and Ansh (8.45) fall into this top percentile, indicating they have higher comparative academic performances .

There are two boys over the age of 13: Danny aged 14 and Vic aged 17 . This count is determined using a logical COUNTIFS formula evaluating 'Boy' and age '>13' .

Setting the [range_lookup] argument to 0 in a VLOOKUP function means an exact match is required; this is typically used when precise identification of a value is needed . An approximate match, denoted by using 1 instead, might be necessary when the data is in a sorted range and an approximate estimation is needed, such as in tax bracket computations .

The total income of rappers who like the color yellow is $127 million, derived from adding the incomes of Ice Cone ($20 million), Cardi C ($17 million), and 3Pac ($90 million). Comparing this to the sum of all listed incomes, which totals $196 million, the yellow-preferring rappers earn a significant portion of the total income .

'Bronze' accounts from NY contribute a total of $8 in revenue . Compared to 'Gold' accounts, which generate $612, this indicates that 'Bronze' accounts are significantly less lucrative, highlighting their lower performance in revenue generation .

To highlight dates after a specific date using conditional formatting, set a rule that compares each cell to the specified date. For instance, dates after October 6, 2018, such as November 3, 2018, and December 3, 2018, would be highlighted in the provided dataset .

A healthy customer eligible for an insurance discount should be over the age of 50 or lead a healthy lifestyle by not smoking, not drinking, and not being overweight . Ralph, who is 57, meets these criteria as he is a non-smoker, non-drinker, and is not overweight, making him eligible for an insurance discount .

The average income for Ohio employees is $81,000 . When compared with other states, Ohio's average is higher than the average of Minnesota's employees who earn $68,000 (the average of John, Dilip, and Muhammad's incomes: ($77,000 + $67,000 + $60,000)/3), and it is comparable to incomes from New York and Texas .

You might also like