ASSIGNMENT-1

RANDBETWEEN: This function generates a random integer

between two specified number.
Question 1: Find the Net Income.

1. Open Excel and prepare the following heads.

2. Enter the Months under the month head in periodic

order.
 Enter Values under Total Revenue and
Total Expenditure using RANDBETWEEN
function.

1
NISCHHAL SINHA
01761188823(BCOM(H))
 3. To Find the income subtract the total expenditure
from total income.
 Select the cell containing total income and
then put a minus sign and then select the cell
containing total expenditure.
 After obtaining the first value select the cell
and drag the cursor to apply the same
function in the remaining cell.

2
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMENT-2
Absolute Reference ($A$1)
 The dollar sign before both the column letter and the row
number lock both the column and the row. If you copy the
formula from one cell to another, the reference will remain
exactly the same (e.g., it will always refer to cell A1).
Mixed Reference ($A1 or A$1)
 $A1: The dollar sign before the column locks the column
(A), but the row (1) will change if the formula is copied
across rows.
3
NISCHHAL SINHA
01761188823(BCOM(H))
  A$1: The dollar sign before the row locks the row (1), but
the column (A) will change if the formula is copied across
columns.
Relative Reference (A1)
 No dollar sign means the reference is relative. When you
copy the formula, both the column and row will adjust
based on the new location.
Q1: Find the percentage obtain by student in class.

1. Open Excel and enter the following data.

2. To Calculate the percentage, we will use the

method of Absolute reference. In a cell type “=”.
 Select the cell containing the total marks
obtained by Arun i.e. “B6”.
 Now Divide the cell by the cell containing the
grand total i.e. “B4”.
 To keep the refence same for all the cell insert
dollar sign Before and after B i.e. “$B$4”.
 Now Multiple the whole formula by [Link]
output will be achieved.

4
NISCHHAL SINHA
01761188823(BCOM(H))
  Select the cell containing the output and drag
the cursor down in order to apply the formula
in the remaining cells.

ASSIGNMENT-3
Question1: Calculate the total cost of the menu items, also
compute the Sales Tax.
1. Open Excel and enter the following values.

5
NISCHHAL SINHA
01761188823(BCOM(H))
 2. To Calculate the total cost of the Menu Items, Type
“=” in a cell.
 Select the cell containing the Unit price for
the menu item A.
 Put the Sign of Multiple “*” and then select
the cell containing the qty of menu items A.
 The total cost of item A will be displayed in
the cell.
 Select the cell containing the output and
drag the cursor down in order to apply the
formula in the remaining cell.

6
NISCHHAL SINHA
01761188823(BCOM(H))
 3. Calculate the total of the cost by typing “=SUM”
and then select the range. In the above case the
range will be from “D5:D14”.

4. Type the rate of sales Tax in one of the cell.

7
NISCHHAL SINHA
01761188823(BCOM(H))
 5. Now to calculate Sales Tax type “=” in a cell and
then select the cell containing the total cost of
menu item A.
 Then put the sign of multiply “*” and select
the cell containing the tax rate i.e. “I2”.
 To keep the refence same for all the cell insert
dollar sign Before and after I i.e. “$I$2”.
 The Sales tax for the item A would be
displayed in the cell.
 Select the cell containing the output and drag
the cursor down in order to apply the same
formula in the remaining cell.

Question 2: Calculate 18% of the food and beverage and also

the total cost of the Menu Items.
6. Enter the following Data in the Excel sheet.

8
NISCHHAL SINHA
01761188823(BCOM(H))
 7. To Calculate 18% of the food and beverage put “=”
sign in the cell.
 Select the cell containing the value of food
and beverage.
 Now put the multiple sign “*” and type 18%
and press enter.

8. To calculate the total cost put the “= SUM” and

select the range.
 In the above case the range would be
“D21:D24” and press enter.

9
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMENT-4
Question: Highlight the column that are greater than 50 under
the number head.
1. Enter the following data in excel.

2. To highlight the column greater than 50, Select the

number column and go to conditional formatting
located in the Home Tab under styles.
 A dialogue box will appear. Select the option
“Highlight Cell Rules” and then select
“Greater Than”.

3. Another dialogue box will appear. Enter the

number “50” and select “Custom Format”. Chose
the color you want your cells to be highlighted with
and the press enter.

10
NISCHHAL SINHA
01761188823(BCOM(H))
 4. The cells containing number more than 50 will be
highlighted.

Question: Highlight the text that contains Monday.

1. Enter the following data in Excel.

2. To highlight Monday, Select the text column and

then go to conditional formatting located in the
home tab under style.
 A dialogue box will appear, select the option
“Highlight cell rules” and then select “Text
that contains”.

11
NISCHHAL SINHA
01761188823(BCOM(H))
 3. Another dialogue box will appear. Type “Monday”
and then select “Custom Format”. Select the color
you want and press “ok”.

4. The text containing Monday will be highlighted.

Question: Perform all Conditional formatting under the date

header provided in Excel.
1. Enter the following data in excel.

12
NISCHHAL SINHA
01761188823(BCOM(H))
  DATE OCCURING THIS WEEK
1. To Highlight the cell that contains a Date Occurring
this week, Select the date column and then go to
conditional formatting.
 Go to “highlight cell rules” and select the
option “A Date Occurring”.
 A dialogue box will appear, Select the option
“This Week” and go to “Custom Format”.
Select the color and press “ok”.
2. The cell containing Date occurring this week will
be selected.

 DATE OCCURRING LAST MONTH

1. To Highlight the cell that contains a Date Occurring
Last month, Select the date column and then go to
conditional formatting.
 Go to “highlight cell rules” and select the
option “A Date Occurring”.
 A dialogue box will appear, Select the option
“Last Month” and go to “Custom Format”.
Select the color and press “ok”.
2. The Cell containing Date Occurring last month will
be selected.

13
NISCHHAL SINHA
01761188823(BCOM(H))
  DATE OCCURRING THIS MONTH
1. To Highlight the cell that contains a Date Occurring
This month, Select the date column and then go to
conditional formatting.
 Go to “highlight cell rules” and select the
option “A Date Occurring”.
 A dialogue box will appear, Select the option
“This Month” and go to “Custom Format”.
Select the color and press “ok”.
2. The Cell containing Date Occurring This month will
be selected.

Question: Highlight the cell under the condition that they are
unique or duplicate.
1. Enter the following data in excel.

 UNIQUE VALUE

14
NISCHHAL SINHA
01761188823(BCOM(H))
 1. To Highlight the cell that contains a Unique value,
Select the Text column and then go to conditional
formatting.
 Go to “highlight cell rules” and select the
option “Duplicate Value”.
 A dialogue box will appear, Select the option
“Unique” and go to “Custom Format”. Select
the color and press “ok”.
2. The cell containing a Unique Value will be selected.

 DULPICATE VALUE
1. To Highlight the cell that contains a duplicate
value, Select the Text column and then go to
conditional formatting.
 Go to “highlight cell rules” and select the
option “Duplicate Value”.
 A dialogue box will appear, Select the option
“Duplicate” and go to “Custom Format”.
Select the color and press “ok”.
2. The cell containing a duplicate Value will be
selected.

15
NISCHHAL SINHA
01761188823(BCOM(H))
Question: Highlight the lowest three items under the
number header.

1. Enter the following data in excel.

2. To Highlight the cell that contains the lowest three

item, Select the number column and then go to
conditional formatting.
 Go to “Top/Bottom rules” and select the
option “Bottom 10 items”.
 A dialogue box will appear, Select the option
“3” and go to “Custom Format”. Select the
color and press “ok”.

16
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMNET-5
FILTER IN EXCEL
Filtering in Excel allows you to display only the rows that
meet specific criteria while hiding the others.
Question: On the following database use filters in excel.
1. Enter the following data in excel

Question 1(a): On region header, use the filter to find the

region starting with ‘N’.
17
NISCHHAL SINHA
01761188823(BCOM(H))
 1. Select the region column and go to “Sort and
Filter” located in the editing head located in home
tab.
 A dropdown menu will appear and select the
option of Filter.

2. Click on the arrow, located at the site of North. A

dropdown menu will appear, Select “Text Filters”.
 Another dropdown menu will appear select
the option “Begins with”
 A dialogue box will appear enter “N” and
press ok.

18
NISCHHAL SINHA
01761188823(BCOM(H))
 3. The following output will be received.

ASSIGNMNET-6
 Functions In Excel

1. SUMPRODUCT METHOD: This Function returns the sum of

the products of corresponding ranges or array. The
default function is Multiplication but addition, subtraction
and division are also possible

Question: Use Sum product Function in the table given below.

 In the Adjacent cell, type the following function “=

SUMPRODUCT (Array1, Array2) i.e. for the
following question is A8 AND B8. A number will be

19
NISCHHAL SINHA
01761188823(BCOM(H))
 obtained, now drag the cursor down so that the
formula can be applied the remining cell.

 Now to obtain the Sum of the outputs in a cell

type “= SUM (C8, C9, C10)”, these are the
cell that contain the output. Press Enter and the
following result will be Obtained.

2. MROUND FUNCTION: This Function returns a number to a

multiple given by users. Mround will always Round up
away returns a number rounded to the desired multiple.
Question: Use Mround Function on the data given below.

 In a Cell adjacent to the number 4.7 type

“=MROUND(B16,5)”. Here B16 is the cell
containing the no 4.7 and 5 is the multiple
to which it should be rounded up. Press
enter and an output will be obtained, now
select the cell containing the output and
drag the cursor down. The following data
will be achieved.

20
NISCHHAL SINHA
01761188823(BCOM(H))
 3. FLOOR FUNCTION: This Function is used to round a given
number down to the nearest multiple of a specified
number.

Question: Round a Number down, towards zero, to the nearest

multiple of significance in accordance with the data given
below.

 In an adjacent cell type, the following

“= FLOOR(B27,2)”. Here B27 is the cell
containing the number and 2 is the significance.
Similarly, the significance for 16.4 and 4.4 will be
10 and 2 respectively. Now press enter and the
following output would be received.

4. CELLING FUNCTION: This Function rounds a given

number to the nearest specified multiple. Ceiling works
like MROUND.

21
NISCHHAL SINHA
01761188823(BCOM(H))
Question: Use Ceiling function on the data given below.

 In an adjacent cell type “=CEILING(B35,20)”. Here

B35 is the cell containing the number and 20 is the
significance. Similarly, the significance of the
number 7.8 is 1. Now press enter , the following
output would be received.

ASSIGNMENT-7
STRINGS FUNCTION IN EXCEL

 LEFT AND RIGHT FUNCTION

 The Left Function is used to retrieve a chosen
number of characters, counting from the left side of
an excel cell.
 The chosen number has to be greater than 0 and is
set to 1 by default.
 The Right Function is a test string function of
characters from the right side of the string.
 It helps extract characters beginning from rightmost
side to the left

22
NISCHHAL SINHA
01761188823(BCOM(H))
Question: On “ABC” use the LEFT and RIGHT string function to
see what will be the output.
 The Syntax of Left function: “=LEFT(TEXT[NUM_CHARS])”.
 The Syntax of Right Function: “=
RIGHT(TEXT[NUM_CHARS])”.

 MID FUNCTION
 This Function is designed to pull a substring from
the middle of the original text string.
 The Mid function returns the specified number of
characters starting at the position you specify.

Question: On “ABC” Use MID function to see what will be the

output.

 The Syntax of Mid Function: “=MID (TEXT, START_NUM,

NUM_CHARS)

 UPPER AND LOWER FUNCTION

 The Upper function is a text function, that converts
text to all capital letters. (UPPER CASE)

23
NISCHHAL SINHA
01761188823(BCOM(H))
  The lower function returns the lower-case version of
string given.

Question: Use Upper and lower function on your names

 The syntax of upper function: “=Upper (Text)”
 The syntax of lower function: “=Lower Text)”

 LENGTH FUNCTION
 This function returns the length of a given text
string.

Question: Use Length function on your name.

 The Syntax of length function: “=LEN(TEXT)”.

 TRIM FUNCTION
 This Function is used to remove irregular text
spacing and keep single spaces between the words.

Question: Use TRIM function on the following sentence.

 The syntax of Trim function: “=TRIM(TEXT)”

24
NISCHHAL SINHA
01761188823(BCOM(H))
  CONCATENATE FUNCTION
 This function joins two or more Text strings in one
string.

Question: Use Concatenate Function on the following sentence.

 The Syntax of Concatenate Function: “=CONCATENATE

(text1, text2,…)”

 SUBSITUTE FUNCTION
 This Function one or more text strings with another
text string.
 this function is useful when we wish to substitute
old text in a string with a new string.

Question: Use Substitute function on the following sentence.

25
NISCHHAL SINHA
01761188823(BCOM(H))
  The Syntax of Substitute Function: “=SUBSITUTE (text,
old_ text, new _text, [instance _Num])”.

 REPLACE FUNCTION
 This function replaces part of a text string, based on
the number of characters you specify, with a
different text string.

Question: Replace Hello with Hi in the given sentence using

Replace Function.

 The Syntax of replace function:

“=REPLACE(old_text,start_num,num_chars,new_text)”.

ASSIGNMENT-8
 IF FUNCTION
 IF function is a logical function.
 It allows you to make decisions based on a condition.
 The Syntax of IF Function: “=IF (logical_test,
value_if_true, value_if_false)”.
 logical test: The condition you want to check (e.g., A1 > 10)
 value_if_true: The result if the condition is true
 value_if_false: The result if the condition is false

 IF & AND FUNCTION

26
NISCHHAL SINHA
01761188823(BCOM(H))
  This combination lets you check multiple conditions
at once, and return results based on whether all of
them are true.
 The Syntax of IF&AND Function: “=IF (AND
(condition1, condition2, ...), value_if_true,
value_if_false)”.
 AND (condition1, condition2, ...): Checks if all conditions are
TRUE.
 IF(...): Uses the result of the AND function to decide what to
return.

Question: Determine Whether the sales Target have been met

or not. In Accordance with the data given below.

27
NISCHHAL SINHA
01761188823(BCOM(H))
Question: Use IF function to determine whether the students
have passed or fail in the SUB-1 Subject.

28
NISCHHAL SINHA
01761188823(BCOM(H))
Question: Use IF & AND function in the given database to
determine the Division of the student based on their
percentage and marks.

29
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMENT-9
 COUNT FUNCTION
 The COUNT function is used to count the number of numeric
values in a range of cells.
 The syntax of count function: “=COUNT (value1,
[value2], ...)”.
 It counts only numbers (not text, blank cells, or error values).
 You can enter individual values or cell ranges.
30
NISCHHAL SINHA
01761188823(BCOM(H))
  COUNTA FUNCTION
 The COUNTA function counts the number of non-
empty cells in a range — including numbers, text,
dates, logical values (TRUE/FALSE), and even
errors.
 The Syntax of Counta Function: “=COUNTA
(value1, [value2], ...)”.
 It counts all cells that are not empty, no matter what type of
data they contain.
 Useful when you want to know how many cells have
something in them, not just numbers.

Question: Use Count Function to determine the count of

enrolment number of students given in the database.

Question: Use CONTA Function to determine the count of cells

under the total marks section.

31
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMENT-10

 SUM IF FUNCTION
 The SUMIF function adds up the values in a range only if they
meet a specified condition.
 The Syntax of SUMIF Function: “=SUMIF (range,
criteria, [sum_range])”.
 range: The range of cells to evaluate with the condition.
 criteria: The condition that determines which cells to sum.
 sum_range: The actual cells to sum — if different from the
range.

32
NISCHHAL SINHA
01761188823(BCOM(H))
  COUNT IF FUNCTION
 The COUNTIF function counts the number of cells
that meet a specific condition.
 The Syntax of COUNT IF Function: "=COUNTIF
(range, criteria)”.
 range: The group of cells you want to apply the condition to.
 criteria: The condition (like ">50", "Apples", or "=Yes") that
determines which cells to count.

 SUM IFS FUNCTION

 The SUMIFS function adds up values in a range
only if they meet all the specified conditions.
 The Syntax of Sum ifs Function: “=SUMIFS
(sum_range, criteria_range1, criteria1,
[criteria_range2, criteria2], ...)”
 sum_range – The cells you want to total.
 criteria_range1 – The range you want to apply the first
condition to.
 criteria1 – The first condition.
 You can keep adding more criteria range + criteria pairs for
more conditions.

Question: Solve the Following questions Using SUMIF AND CONT

IF Functions on the given database.

1. Calculate Total Sales.

33
NISCHHAL SINHA
01761188823(BCOM(H))
 [Link] Total sales for north region.

3. Find the total commission for red house.

4. Find the total commission for house red where the

count of new customer is less than 10.

34
NISCHHAL SINHA
01761188823(BCOM(H))
 5. Count the new customer where the number of
customer is more than 10.

35
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMENT 11

AVERAGE IF & AVERAGE IF’s FUNCTION

Understanding the difference between AVERAGEIF and AVERAGEIFS is

crucial for effective data analysis in Excel. Here's a breakdown:
AVERAGEIF:
 Purpose:
o Calculates the average of cells within a range that meet a single
specified criterion.1
 Syntax:
o AVERAGEIF (range, criteria, [average_range])
 range: The range of cells to evaluate.
 criteria: The condition to be met.
 average_range: (Optional) The cells to average. If omitted,
the range is averaged.
36
NISCHHAL SINHA
01761188823(BCOM(H))
  Key Feature:
o Deals with one condition.
AVERAGEIFS:
 Purpose:
o Calculates the average of cells that meet multiple specified
criteria.2
 Syntax:
o AVERAGEIFS (average_range, criteria_range1, criteria1,
[criteria_range2, criteria2], ...)
 average_range: The cells to average.
 criteria_range1: The first range to evaluate.
 criteria1: The first condition.
 criteria_range2, criteria2, ...: Additional ranges and
conditions.
 Key Feature:
o Handles multiple conditions.
o It uses "AND" logic, meaning that all criteria must be met for a cell
to be included in the average.
o The order of the arguments is different than Averageif. The Average
range is the first argument.
In simpler terms:
 Use AVERAGEIF when you need to find the average based on one
condition.
 Use AVERAGEIFS when you need to find the average based on two or
more conditions.
Essentially, AVERAGEIFS is a more powerful and versatile version of
AVERAGEIF, allowing for more complex data analysis.

37
NISCHHAL SINHA
01761188823(BCOM(H))
 QUES: Average- IF & Average Ifs Function on the given below database:

Name Enrollment NO. College Grade Level Test Score

Anuj 1125 MAIMS 12 70

Aryan 1126 MAIMS 9 69

Gaurav 1127 MAIT 13 76

Ishaan 1128 MAIMS 7 73

Suyog 1129 AMITY 12 81

Vaibhav 1130 AMITY 11 76

Vishal 1131 MAIT 10 78

1) Calculate AVG. scores for grades- 9, 10, 11?

2) a.) Calculate AVG. Score for student whose grade is 9 and belongs to
MAIMS
b.) Calculate AVG. Score for students whose Grade is 10 and belongs to
Mait

38
NISCHHAL SINHA
01761188823(BCOM(H))
 39
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMENT 12
NOT & ISBLANK FUNCTION

In Excel, the NOT and ISBLANK functions serve distinct logical

purposes. Here's a breakdown:
1. ISBLANK Function:
 Purpose:
o The ISBLANK function checks whether a cell is completely
empty.
o It returns TRUE if the cell is blank, and FALSE if the cell contains
any data, including spaces or formulas that result in an empty
string ("").
 Syntax:
o ISBLANK (value)
o Where "value" is the cell, you want to test.
 Key Points:
o A truly blank cell will return TRUE.
o A cell containing a space or a formula returning "" will return
FALSE.

2. NOT Function:
 Purpose:
o The NOT function reverses the logical value of its argument.
o If the argument is TRUE, NOT returns FALSE, and vice versa.
 Syntax:
o NOT (logical)
o Where "logical" is a value or expression that can be evaluated as
TRUE or FALSE.

 Key Points:
o It's often used to invert the result of another logical function.
o It's very common to see it used in conjunction with the ISBLANK
function.
Combining NOT and ISBLANK:
 When you combine NOT and ISBLANK (NOT (ISBLANK (cell))), you
effectively check if a cell is not blank.
40
NISCHHAL SINHA
01761188823(BCOM(H))
  This is very useful in IF statements when you want to perform an action
only if a cell contains data.
 Example:
o =IF(NOT(ISBLANK(A1)), "Cell A1 is not empty", "Cell A1 is
empty")
o This formula will display "Cell A1 is not empty" if cell A1 has any
content, and "Cell A1 is empty" if it is truly blank.
In summary, ISBLANK tells you if a cell is empty, and NOT reverses that
result, allowing you to check if a cell is not empty.
QUES check if employees have generated Extra Sales or not in the given
database below if the did, provide 25% of extra sales as commission and if they
didn’t, display NO BONUS for them

41
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMENT 13
SUM-IF FUNCTIONS

The SUMIF function in Excel is a powerful tool for conditionally

summing values. Here's a breakdown of its purpose and how it works:

Purpose:
 The SUMIF function allows you to sum values within a range that meet a
specific criterion. This means you can add up only the numbers that
match a certain condition.
Syntax:
 SUMIF(range, criteria, [sum_range])

Arguments:
 range (Required):
o This is the range of cells that you want to evaluate against the
criteria.
 criteria (Required):
o This defines the condition that determines which cells will be
summed. It can be:
 A number (e.g., 100)
 An expression (e.g., ">50", "<=20")
 A cell reference (e.g., A1)
 Text (e.g., "apples")
 sum_range (Optional):
o This is the actual range of cells that will be summed.
o If omitted, the range argument is used for both evaluating the
criteria and calculating the sum.

Key Points:
 Conditional Summing: It sums values based on whether they meet a
specified condition.
 Flexibility: The criteria can be numbers, text, or expressions.
 Optional Sum Range: If the range to be summed is the same as the range
being evaluated, the sum_range argument can be omitted.
 Wildcards: You can use wildcard characters like the asterisk (*) and
question mark (?) in the criteria argument.
42
NISCHHAL SINHA
01761188823(BCOM(H))
QUES Perform the following conditions on the dataset given below:

[Link] ITEM SALES SHIPPING

1 MOUSE 15000 5
2 KEYBOARD 12000 5
3 CPU(i5) 7500 10
4 CPU(i7) 8300 10
5 MONITOR 10200 7

43
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMENT 14
WLD CARD CHARACTERS

In Excel, wildcard characters are special symbols that you can use within text
strings in functions like SUMIF, COUNTIF, AVERAGEIF, VLOOKUP, MATCH,
and others, to match patterns of text. They provide flexibility when you don't
know the exact text you're looking for or when you want to match variations of a
text string.

Counta: - The COUNTA function in Excel is used to count the number of cells in
a range that are not empty.

QUES Calculate the following on the dataset given below:

NAME ENROLLMENT NO
ANUJ 1125
ARYAN 1041
GAURAV 1121
ISHAAN 1731
SUYOG 1098
VAIBHAV 1888
VISHAL 1034

1.) Calculate no of cells with text values only in student’s column

2.) Calculate no of cells with numeric values only in enrollemnt column

44
NISCHHAL SINHA
01761188823(BCOM(H))
 45
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMENT 15
LOOKUP FUNCTION

LOOKUP FUNCTION: This function searches for a value in a vector or array

and return a value from the same position in a secont vector or array

The LOOKUP function has 2 syntax:

LOOKUP(lookup_value, lookup_vector,[result_vector])
LOOKUP(lookup_value, array)

Ques1 Calculate the following on the database given below

NAME ENROLLMENT NO TOTAL MARKS

ANUJ 1125 450
ARYAN 1041 440
GAURAV 1121 396
ISHAAN 1731 465
SUYOF 1098 358
VAIBHAV 1888 404
VISHAL 1034 391

A.) Find the enrollment no of students using their names

B.) Find the total marks of the students using their enrollment no

46
NISCHHAL SINHA
01761188823(BCOM(H))
 47
NISCHHAL SINHA
01761188823(BCOM(H))
QUES 2

EMPLOYEE ID NAME LOCATION SALARY AGE

56815 Garry Manship Hong Kong 13836 25
51186 William Johnson Berlin 11771 32
51511 Bettle Bangkok 13046 35
50890 Ian Nash Cairo 18276 32
53700 Margaret Turley Shanghai 19327 26
55879 Michael Kaye Capetown 18996 35
59848 Paul Bell Bangkok 10387 25
58369 Ian Davies Capetown 12566 37
50217 Eric Green Warsaw 16406 42
50695 Williamr Black Cairo 15784 43
59673 Estelle Cornack Hong Kong 10959 30
52130 Christopher Fallon Delhi 14562 32

a) What is the name of employee id 58369

48
NISCHHAL SINHA
01761188823(BCOM(H))
b) What's the age of estelle Cormack

c) Return the location of the following employees

49
NISCHHAL SINHA
01761188823(BCOM(H))
d) Find the Salary of the following employee

50
NISCHHAL SINHA
01761188823(BCOM(H))
 51
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMENT 16
V-LOOKUP & H-LOOKUP

In Microsoft Excel, both VLOOKUP and HLOOKUP are lookup functions

used to search for data in a table and extract related information.

VLOOKUP (Vertical Lookup):

 Searches: Vertically down the first column of a specified range (table
array).
 Looks for: A specific value (lookup value) in that first column.
 Returns: A value from a specified column in the same row where the
match is found.
 Best used when: Your data has the lookup values in the leftmost column
and related information in columns to the right. Think of a list where
you're looking up an ID or a name and want to return other details
associated with it.

Syntax of VLOOKUP
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
 lookup_value: The value you want to search for in the first column of the
table_array.
 table_array: The range of cells that contains the data you want to search
in. The first column of this range is where the lookup_value is searched.
 col_index_num: The column number within the table_array that contains
the value you want to return. The first column in the table_array is 1.
 [range_lookup]: An optional argument that specifies whether you want
an exact or approximate match:
o TRUE or omitted: Approximate match. If an exact match is not
found, it returns the next largest value that is less than the
lookup_value. The first column of the table_array should be sorted
in ascending order for this to work correctly.
o FALSE: Exact match. It will only return a value if an exact match
for the lookup_value is found in the first column. If no exact match
is found, it returns the #N/A error.

HLOOKUP (Horizontal Lookup):

52
NISCHHAL SINHA
01761188823(BCOM(H))
  Searches: Horizontally across the first row of a specified range (table
array).
 Looks for: A specific value (lookup value) in that first row.
 Returns: A value from a specified row in the same column where the
match is found.
 Best used when: Your data has the lookup values in the top row and
related information in rows below. Think of a table where the headers in
the first row are what you want to look up.

Syntax of HLOOKUP:
=HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])
 lookup_value: The value you want to search for in the first row of the
table_array.
 table_array: The range of cells that contains the data you want to search
in. The first row of this range is where the lookup_value is searched.
 row_index_num: The row number within the table_array that contains
the value you want to return. The first row in the table_array is 1.
 [range_lookup]: An optional argument that specifies whether you want
an exact or approximate match (same as in VLOOKUP):
o TRUE or omitted: Approximate match. The first row of the
table_array should be sorted in ascending order.
o FALSE: Exact match.
o
In summary, the main difference is the direction of the lookup:
 VLOOKUP: Looks up vertically in the first column.
 HLOOKUP: Looks up horizontally in the first row.
The choice between VLOOKUP and HLOOKUP depends entirely on how your
data is organized in your Excel sheet.

Ques Find out the marks of vaibhav in all the subjects from the database given
below:-
NAME ARYAN ISHAAN SUYOG VAIBHAV
ITL 72 85 70 77
MA 79 79 75 67
CF 84 76 81 78
DA-LAB 83 72 71 78
BE-CSR 76 76 71 83

53
NISCHHAL SINHA
01761188823(BCOM(H))
 54
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMENT 17
IF With V LOOKUP FUNCTION

Combining the IF function with VLOOKUP or HLOOKUP in Excel

enhances the power of lookup formulas by allowing for conditional logic.
Key uses include:
 Error Handling: Using IF(ISNA(VLOOKUP(...)), "Not Found",
VLOOKUP(...)) to display a custom message instead of #N/A when no
match is found.
 Conditional Lookups: Performing different lookups based on a specific
condition in another cell (e.g., looking up data from different tables based
on a product type).
 Conditional Returns: Evaluating the result of a VLOOKUP or
HLOOKUP and returning different values based on whether the found
value meets certain criteria (e.g., returning "High Value" if the looked-up
price is above a threshold).
In essence, nesting VLOOKUP or HLOOKUP within an IF function allows you
to create more flexible and intelligent spreadsheets that can handle various
scenarios and provide more user-friendly outputs.

SALES
PRODUCT SALES TARGET
ACHIEVED
APPLE 120 100
GRAPE 250 230
BLUE BERRY 170 180
STRAWBERRY 120 120
ORANGE 135 110
MANGO 205 220
PEACH 200 175
GUAVA 230 230
KIWI 100 90
PAPAYA 200 240
55
NISCHHALWATERMELON
SINHA 90 21
01761188823(BCOM(H))
LICHI 10 21
 IF with VlookUp Function
[Link] PRODUCT NAME AVAILABLE QTY
1 AAA 1000
2 BBB 500
3 CCC 0
4 DDD 65
5 EEE 250
6 FFF 600
7 GGG 0
8 HHH 900
9 III 1400
10 JJJ 0
11 KKK 800
12 LLL 340
13 MMM 320

56
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMENT 18
VlOOKUP With MATCH FUNCTION

MATCH Function (Brief):

Finds the position of a specific value within a single row or column. Returns
the numerical position (1st, 2nd, etc.). Useful for finding where something is in
a list. Often combined with INDEX for more flexible lookups than
VLOOKUP/HLOOKUP.
Syntax of the MATCH Function:
=MATCH(lookup_value, lookup_array, [match_type])
The standard VLOOKUP function requires you to manually specify the column
index number from which you want to return a value. This becomes
problematic if:
 You insert or delete columns in your lookup table, as the hardcoded
column index will likely return the wrong data.
 The column you need to retrieve data from might change, and you want a
more dynamic way to specify it.
How MATCH Solves This:
57
NISCHHAL SINHA
01761188823(BCOM(H))
The MATCH function returns the relative position of a lookup value within a
specified row or column. By using MATCH as the col_index_num argument in
VLOOKUP, you can dynamically determine the correct column number based
on a header row.
Syntax:
=VLOOKUP(lookup_value, table_array, MATCH(lookup_column_header,
header_row, 0), [range_lookup])
Breakdown:
 lookup_value: The value you want to search for in the first column of
your table_array (same as in regular VLOOKUP).
 table_array: The range containing your lookup data (same as in regular
VLOOKUP).
 MATCH(lookup_column_header, header_row, 0): This is where the
magic happens:
o lookup_column_header: The text in the header row that matches
the column you want to return data from (e.g., "Price", "Name",
"Date").
o header_row: The row containing the column headers of your
table_array.
o 0: Specifies an exact match for the lookup_column_header within
the header_row.
o The MATCH function will return the column number where
the lookup_column_header is found within the header_row. This
number is then used as the col_index_num for the VLOOKUP
function.
 [range_lookup]: (Optional) Specifies whether to find an exact or
approximate match (same as in regular VLOOKUP). FALSE (or 0) for
exact match is generally recommended.
Benefits of Using VLOOKUP with MATCH:

58
NISCHHAL SINHA
01761188823(BCOM(H))
  Dynamic Column Selection: You can change the column from which
VLOOKUP returns data simply by changing the lookup_column_header.
The formula will automatically adjust.
 More Robust to Column Changes: If you insert or delete columns in
your table_array, as long as the header_row and the relative position of
your lookup column remain consistent, the formula will still work
correctly. You don't need to manually update the column index number.
 Improved Readability: The formula becomes more self-explanatory as it
directly references the column header you're looking for.

TIME
TABLE
12.0
9.00 10.00 11.00 1.00 2.00 3.00
0
AM AM AM PM PM PM
PM
LU
MOND CHEMI FRENC GERM PHY ENGLI
MATHS NC
AY STERY H AN SICS SH
H
LU
TUESD GEOGR COMP SOCCE FRENC GER SPANI
NC
AY APHY UTERS R H MAN SH
H
RELIGI
LU
WEDN GERM MATH HISTO OUS ENG ENGLI
NC
ESDAY AN S RY STUDI LISH SH
H
ES
FRENC
LU
THURS HISTO MATH CHEMI H MAT COMP
NC
DAY RY S STERY LITER HS UTERS
H
ATURE
LU
FRIDA COMPU COMP GERM GERM SOC SOCCE
NC
Y TERS UTERS AN AN CER R
H
QUES 2
EMPLOYEE ID NAME LOCATION SALARY AGE
56815 Garry Manship Hong Kong 13836 25
51186 William Johnson Berlin 11771 32
51511 Bettle Bangkok 13046 35
50890 Ian Nash Cairo 18276 32
53700 Margaret Turley Shanghai 19327 26
55879 Michael Kaye Capetown 18996 35
59848 Paul Bell Bangkok 10387 25
59
NISCHHAL SINHA
01761188823(BCOM(H))
 58369 Ian Davies Capetown 12566 37
50217 Eric Green Warsaw 16406 42
50695 Williamr Black Cairo 15784 43
59673 Estelle Cornack Hong Kong 10959 30
52130 Christopher Fallon Delhi 14562 32

1) What is the name of employee ID 58369?

2) What's the age of Estelle Cornack

60
NISCHHAL SINHA
01761188823(BCOM(H))
3) Return the location of the following EE

4) Find the salary of the following employees:

61
NISCHHAL SINHA
01761188823(BCOM(H))
 62
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMENT 19
HLOOKUP With MATCH FUNCTION

The standard HLOOKUP function requires you to manually specify the row
index number from which you want to return a value. This can cause issues if:
 You insert or delete rows in your lookup table, as the hardcoded row
index will likely return the wrong data.
 The row you need to retrieve data from might change, and you want a
more flexible way to specify it.
How MATCH Solves This for HLOOKUP:
Just like with VLOOKUP, you can use the MATCH function to dynamically
determine the correct row index number for the HLOOKUP function based on
a header column (appearing to the left of your data table).
Syntax:
=HLOOKUP(lookup_value, table_array, MATCH(lookup_row_header,
header_column, 0), [range_lookup])
Breakdown:
 lookup_value: The value you want to search for in the first row of your
table_array (same as in regular HLOOKUP).
 table_array: The range containing your lookup data (same as in regular
HLOOKUP).
 MATCH(lookup_row_header, header_column, 0): This is the key part:
o lookup_row_header: The text in the header column that matches
the row you want to return data from (e.g., "Price", "Quantity",
"Sales").
o header_column: The column containing the row headers of your
table_array.
o 0: Specifies an exact match for the lookup_row_header within the
header_column.

63
NISCHHAL SINHA
01761188823(BCOM(H))
 o The MATCH function will return the row number where the
lookup_row_header is found within the header_column. This
number is then used as the row_index_num for the HLOOKUP
function.
 [range_lookup]: (Optional) Specifies whether to find an exact or
approximate match (same as in regular HLOOKUP). FALSE (or 0) for
exact match is generally recommended.
Benefits of Using HLOOKUP with MATCH:
 Dynamic Row Selection: You can easily change the row from which
HLOOKUP returns data by changing the lookup_row_header. The
formula will update automatically.
 More Robust to Row Changes: If you insert or delete rows in your
table_array, as long as the header_column and the relative position of
your lookup row remain consistent, the formula will still function
correctly. You won't need to manually adjust the row index number.
 Improved Clarity: The formula becomes more understandable as it
directly refers to the row header you are trying to retrieve data from.
EMPLOY
101 102 103 104 105 106 107 108 109 110
EE ID
Davi
Bob Tom Jessic
EMPLOY John Jane Sarah Emily Mich d Rachel
Johns Dav a
EE NAME Doe Smith Lee Brown ael Mart Green
on is Davis
in
Departme Marketi Finan Operati Finan Marketi
HR IT OB IT HR
nt ng ce ons ce ng
500 6500 700 8500 9000
Salary 55000 60000 75000 80000 95000
00 0 00 0 0
200 400
Bonus 2500 3000 3500 4500 5000 5500 6000 6500
0 0
520 6850 740 9050 9600
Total Pay 57500 63000 79500 85000 101500
00 0 00 0 0

QUES 1

64
NISCHHAL SINHA
01761188823(BCOM(H))
QUES2

QUES 3

65
NISCHHAL SINHA
01761188823(BCOM(H))
 66
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMENT 20
INDEX & INDEX With MATCH FUNCTION

The INDEX function in Excel returns a value or the reference to a value within
a range or array. You can use INDEX to retrieve a specific value based on its
row and column number within that range.
There are two forms of the INDEX function:
1. Array Form:
 Syntax: =INDEX(array, row_num, [column_num])
 array (Required): The range of cells or an array constant.
 row_num (Required): The row number in the array from which to return a
value.
 column_num (Optional): The column number in the array from which to
return a value. If omitted, it's assumed to be 1 if the array is a single
column.

The combination of the INDEX and MATCH functions in Excel is a powerful

and flexible alternative to functions like VLOOKUP and HLOOKUP for
performing lookups. It overcomes some of the limitations of those functions.
How it Works (Brief):
Instead of relying on the lookup value being in the first column (VLOOKUP) or
first row (HLOOKUP) and specifying a fixed column or row index, INDEX and
MATCH work independently to find the exact location of the desired value.
1. MATCH:
o Finds the position (row or column number) of a specific
lookup_value within a lookup_array.
o Returns the numerical position.
2. INDEX:

67
NISCHHAL SINHA
01761188823(BCOM(H))
 o Returns the value in a table or range based on a specified row and
column number.
Combining Them:
You nest the MATCH function inside the INDEX function to dynamically
determine the row and/or column number for INDEX.

Why Use INDEX and MATCH?

 Flexibility: You can look up values to the left (or above) the lookup
column/row, unlike VLOOKUP/HLOOKUP.
 Robustness: Formulas are less likely to break if you insert or delete
columns/rows, as you specify the lookup and return ranges separately.
 Two-Way Lookups: Easily perform lookups based on both row and
column criteria.
 Performance: Can be more efficient for large datasets as it only looks at
the necessary columns/rows

Employee ID EMP name Sales 2019 Sales 2020 Sales 2021

5001 Suraj 192000 555000 950000

5002 Poonam 183000 325000 874000

5003 Gopal 210000 650000 880000

5004 Rohan 369000 875000 894000

5005 Vidya 250000 380000 895000

5006 Sohan 198000 555000 694000

5007 Mohit 168000 325000 895000

68
NISCHHAL SINHA
01761188823(BCOM(H))
 5008 Rupak 215000 745000 650000

5009 Siya 365000 635000 875000

5010 Poooja 260000 759000 780000

Ques tell the sales for the year 2020 for rupak , kunal and Poooja

69
NISCHHAL SINHA
01761188823(BCOM(H))
 ASSIGNMENT 21
CONDITIONAL FORMATING

Conditional formatting is a powerful feature in Excel that allows you to

automatically apply formatting (such as color, font, borders, etc.) to cells based
on specific rules or conditions. This helps you visually highlight important data,
identify trends and patterns, and make your spreadsheets easier to understand.
In short words
Conditional formatting lets you change the appearance of cells based on their
values or the results of formulas.

 Conditional Formatting in Excel Steps

1. Pick your cells: Select the data you want to format.
2. Go to "Conditional Formatting": Find it under "Home" tab, "Styles"
group.
3. Choose a rule: Pick a pre-set rule (like "Highlight Cells") or create your
own ("New Rule").
4. Set the condition: Tell Excel when to apply formatting (e.g., "if the
number is greater than 10").
70
NISCHHAL SINHA
01761188823(BCOM(H))
 5. Pick a style: Choose how you want the cells to look (color, font, etc.).
6. Click "OK": The formatting will automatically apply.

 To change or remove formatting later:

1. Select the cells.
2. Go to "Conditional Formatting" > "Manage Rules...".
3. Edit or delete rules as needed.

Item Quantity Rate Amount

LED 74 9800 725200
RAM 22 1250 27500
DVD 12 1450 17400
LCD 10 7800 78000
RAM 12 1690 20280
KEYBOARD 12 520 6240
MOUSE 14 230 2940
LED 33 11200 369600
HDD 24 3300 79200
LCD 36 12200 439200
HDD 28 2950 82600
RAM 18 1490 26820
CABINET 20 1850 37000
UPS 13 1450 18850
HDD 8 3340 26720
UPS 10 1690 16900
RAM 20 1050 21000
KEYBOARD 62 490 30380

71
NISCHHAL SINHA
01761188823(BCOM(H))
 LCD 14 4200 58800
MOUSE 22 310 6820

Ques1 Highlight cells with item "KEYBOARD"

Ques2 Highlight cells where the rate of an item is below rs 2000

72
NISCHHAL SINHA
01761188823(BCOM(H))
Ques3 Highlight cells where the quantity of an item is equal to 10

73
NISCHHAL SINHA
01761188823(BCOM(H))
ASSIGNMENT 22

74
NISCHHAL SINHA
01761188823(BCOM(H))