SWTITCH FORMULA
SWITCH(RANGE,FIRST_CONDITION,OUTPUT W.R.T. IT, SECOND_CONDITION, OUTPUT W.R.T. IT, AND SO ON…)
SUMIF FORMULA (ONLY ONE IF CONDITION)
SUMIF(RANGE FOR CONDITION, CONDITION, RANGE FOR SUM)
AVERAGEIF FORMULA
AVERAGEIF(RANGE FOR CONDITION,CONDITION, RANGE FOR AVERAGE)
COUNTIF FUNCTION
COUNTIF(RANGE FOR CONDITION, CONDITION, RANGE FOR AVERAGE)
SUMIFS FORMULA (MULTIPLE IF CONDITION)
SUMIFS(SUM_RANGE, CRITERIA_RANGE_1, CRITERIA_1, CRITERIA_RANGE_2, CRITERIA_2, AND SO ON)
AVERAGEIFS FORMULA
AVERAGEIFS(AVERAGE_RANGE,CRITERIA_RANGE_1,CRITERIA_1,CRITERIA_RANGE_2,CRITERIA_2, AND SO ON)
COUNTIFS FORMULA
COUNTIFS(CRITERIA_RANGE_1, CRITERIA_1, CRITERIA_RANGE_2, CRITERIA_2, AND SO ON..)
MAXIFS FORMULA
MAXIFS(MAX_RANGE, CRITERIA_RANGE_1, CRITERIA_1, CRITERIA_RANGE_2, CRITERIA_2, AND SO ON…)
MINIFS FORMULA
MINIFS(MIN_RANGE, CRITERIA_RANGE_1, CRITERIA_1, CRITERIA_RANGE_2, CRITERIA_2, AND SO ON..)
AND FORMULA
AND(LOGIC_1, LOGIC_2, AND SO ON…)
CURRENT DATE AND TIME CAN BE CALCULATED USING NOW FUNCTION
NOW()
7/3/2025 16:49
TODAY DATE USING TODAY FUNCTION
TODAY()
7/3/2025
TO FIND DAY OF WEEK USING MONTH AND YEAR AND DATE= WEEKDAY()
WEEKDAY(MONTHDAYYEAR)
7
END OF MONTH (EMONTH) IF MONTH=0, END DATE OF CURRENT MONTH, IF 1 END DATE OF NEXT MONTH, IF -1,
END DATE OF PREVIOUS MONTH
EMONTH(START_DATE,MONTH)
Saturday, May 31, 2025
TO FIND NUMBER OF WORKING DAYS IN A MONTH (NETWORKDAYS)
22
(EQUAL TO WILL COME BEFORE FORMULA)
IF CONDITION IS STRING PUT "STRING" , IF
NUMBER CONDION PUT "CONDITION
OPERATOR (=,>,<.>=,<=)"
Problem Statement:
Q. You are a financial analyst working in a bank that offers vario
the investment products that have performed well in terms of r
period of time.
1. Assign a risk category (High = "Equity Shares", Medium = "Bon
2. Calculate the total investment amount for all investments in b
3. Calculate the average returns generated by all mutual fund in
4. Count the number of equity share investments with an investm
5. Calculate the total investment amount for all investments in m
6. Calculate the average returns generated by all fixed deposit in
7. Count the number of bond investments with a duration of 10
8. Find the maximum returns generated by any investment using
9. Find the minimum investment amount for all equity share inve
10. Check whether an investment in bonds has a duration of 5 ye
11. Check whether an investment in equity shares has an investm
12. Check whether an investment in mutual funds does NOT hav
Stock ID Stock Name
1 Parag Parikh Tax Saver
2 Tesla Inc.
3 Microsoft
4 Bank of Baroda Bonds
5 Alphabet
6 Nippon Small Cap Mutual Fund
7 Amazon.com
8 AU Small Finance Bank Bonds
9 Apple Inc.
10 Adani Ports Pvt Ltd
2. Total Investment Amount invested in Bonds
3. Average returns generated by all mutual fund that have a
duration of 5 years
3. Average returns generated by all mutual fund that have a
duration of 5 years
4. Number of equity share investments with an investment
amount greater than 15,000
5. Total Investment Amount invested in Mutual Funds with
duration of 5 years or more
6. Average returns generated by all Fixed Deosit that have a
investment > 75000 duration of 2 years or less
7. Number of Bond Investment with return > 20000 and
duration of 10 years or more
Problem Statement:
Q. A company wants to track the attendance and working hour
working hours each employee has completed in a given month.
1. How many working days did John and Alice work in January 20
2. What was the average number of working hours per day for Jo
3. How many working days were there in January 2022?
4. How many working days are left for John and Alice in January
5. What is the current date and time using the NOW() function?
6. What is the current date using the TODAY() function?
7. What is the day of the week for January 1, 2022 using the WEE
8. What is the last working day of January 2022 for John and Alic
Employee
ID Employee Name
2726 Alice Cooper
2741 John Hopkins
2726 Alice Cooper
2726 Alice Cooper
2726 Alice Cooper
2726 Alice Cooper
2726 Alice Cooper
2741 John Hopkins
2726 Alice Cooper
2726 Alice Cooper
2741 John Hopkins
2726 Alice Cooper
2741 John Hopkins
2741 John Hopkins
2741 John Hopkins
2726 Alice Cooper
2741 John Hopkins
2726 Alice Cooper
2741 John Hopkins
2726 Alice Cooper
1. Working Days in January 2022
2. Average number of Working hours per
day
3. Total number of Working days for the month of January 2022
4. How many working days are left for the
month of January 2022
Structure and Different Usage of Fo
orking in a bank that offers various investment products to its customers. Your manager has asked you to analyze the investment data o
ve performed well in terms of returns. You have been given a dataset that contains information about the returns of different investme
Equity Shares", Medium = "Bonds", or Low = "Mutual Funds") to each investment product based on the Investment Type using the SWITC
amount for all investments in bonds using the SUMIF() function.
generated by all mutual fund investments that have a duration of 5 years or more using the AVERAGEIF() function.
are investments with an investment amount greater than 15,000 using the COUNTIF() function.
amount for all investments in mutual funds with a duration of 5 years or more using the SUMIFS() function.
generated by all fixed deposit investments with an investment amount greater than 75,000 and a duration of 2 years or less using the AVE
estments with a duration of 10 years or more and returns greater than 20,000 using the COUNTIFS() function.
nerated by any investment using the MAXIFS() function.
amount for all equity share investments with a duration of 2 years or less using the MINIFS() function.
t in bonds has a duration of 5 years or more and returns greater than 10,000 using the AND() function.
t in equity shares has an investment amount of 15,000 or more OR a duration of 4 years or more using the OR() function.
t in mutual funds does NOT have a duration of 3 years using the NOT() function.
Stock Name Investment Type Investment Amount Investment Duration (in yrs)
Parikh Tax Saver Mutual Funds ₹ 50,000.00 5
Tesla Inc. Equity Shares ₹ 15,000.00 3
Microsoft Equity Shares ₹ 17,000.00 2
of Baroda Bonds Bonds ₹ 500,000.00 10
Alphabet Equity Shares ₹ 21,000.00 4
mall Cap Mutual Fund Mutual Funds ₹ 55,000.00 5
mazon.com Equity Shares ₹ 17,000.00 7
Finance Bank Bonds Bonds ₹ 750,000.00 10
Apple Inc. Equity Shares ₹ 33,000.00 5
ni Ports Pvt Ltd Equity Shares ₹ 10,000.00 1
ested in Bonds 8. Maximum returns generated by any investment
all mutual fund that have a 9. Minimum investment amount for all equity share
investments with a duration of 2 years or less
all mutual fund that have a 9. Minimum investment amount for all equity share
investments with a duration of 2 years or less
tments with an investment 10. Check whether an investment in bonds has
duration >= 5 years and returns > 10,000
11. Check whether in equity shares has an
ested in Mutual Funds with
investment amount >= 15000 OR a duration >= 4
years
all Fixed Deosit that have a 12. Check whether an investment in mutual funds
2 years or less does NOT have a duration of 3 years
with return > 20000 and
e attendance and working hours of its employees. The HR department wants to create a spreadsheet to track attendance and calculate t
as completed in a given month.
hn and Alice work in January 2022?
r of working hours per day for John and Alice in January 2022?
there in January 2022?
ft for John and Alice in January 2022
time using the NOW() function?
the TODAY() function?
r January 1, 2022 using the WEEKDAY() function?
f January 2022 for John and Alice?
ployee Name Date of Hire Date Time IN Time OUT
1/1/2022 2/1/2022 8:12:03 18:37:00
3/1/2022 3/1/2022 8:38:41 18:15:24
1/1/2022 3/1/2022 8:40:51 18:41:37
1/1/2022 4/1/2022 8:14:30 18:35:34
1/1/2022 5/1/2022 8:04:25 18:14:07
1/1/2022 6/1/2022 8:44:27 18:17:00
1/1/2022 7/1/2022 8:50:47 18:59:37
3/1/2022 7/1/2022 8:02:50 18:43:21
1/1/2022 8/1/2022 8:04:42 18:30:14
1/1/2022 9/1/2022 8:07:26 18:50:50
3/1/2022 9/1/2022 8:36:32 18:12:14
1/1/2022 10/1/2022 8:42:09 18:49:41
3/1/2022 10/1/2022 8:47:20 18:28:48
3/1/2022 11/1/2022 8:29:11 18:22:11
3/1/2022 12/1/2022 8:07:44 18:44:56
1/1/2022 1/13/2022 8:54:14 18:24:55
3/1/2022 1/13/2022 8:18:14 18:32:07
1/1/2022 1/14/2022 8:02:33 18:41:20
3/1/2022 1/14/2022 8:28:36 18:35:08
1/1/2022 1/15/2022 8:54:06 18:18:43
Alice
5. Current Date
John
Alice
6. Current Date with Time
John
s for the month of January 2022 7. What is the day of week for January 1,2022
Alice 8. What is the Last Working Alice
John days for January 2022 John
fferent Usage of Formulas
nalyze the investment data of the bank and identify
returns of different investment products over a
estment Type using the SWITCH() function.
nction.
.
f 2 years or less using the AVERAGEIFS() function.
on.
OR() function.
Returns Risk Category
12% Low
15% High
12% High
11% Medium
11% High
14% Low
18% High
17% Medium
17% High
17% High
d by any investment
nt for all equity share
2 years or less
nt for all equity share
2 years or less
ent in bonds has
s > 10,000
ares has an
OR a duration >= 4
ent in mutual funds
years
ack attendance and calculate the total number of
Mode of Punch IN
Face ID
Fingerprint ID
Face ID
Face ID
Fingerprint ID
ID Card
Face ID
ID Card
Fingerprint ID
ID Card
ID Card
Face ID
Fingerprint ID
ID Card
ID Card
Face ID
ID Card
Fingerprint ID
Face ID
Face ID
January 1,2022
Alice
John
