Skip to main content

Advanced MS Excel formula logic

Advanced MS Excel formula logic, lookup functions, conditional calculations, references, errors, arrays, and practical formula-based questions.

Advanced MS Excel Formula MCQs

1. What will =IF(A1>=60,"Pass","Fail") return if A1 contains 75?

A. 60
B. TRUE
C. Pass
D. Fail

Answer: C. Pass

Explanation: Since 75 ≥ 60 is TRUE, the IF() function returns "Pass".

2. What is the result of =SUM(A1:A5)?

A. Adds only A1 and A5
B. Adds values from A1 through A5
C. Counts cells A1 through A5
D. Finds the average

Answer: B. Adds values from A1 through A5

Explanation: The colon : specifies a continuous range from A1 to A5.

3. Which formula correctly calculates the average only for values greater than 50 in A1:A20?

A. =AVERAGEIF(A1:A20,">50")
B. =AVERAGE(A1:A20,">50")
C. =IFAVERAGE(A1:A20,50)
D. =AVGIF(A1:A20,50)

Answer: A. =AVERAGEIF(A1:A20,">50")

Explanation: AVERAGEIF() calculates the average of cells satisfying a specified criterion.

4. Which function can sum values based on multiple conditions?

A. SUMIF
B. SUMIFS
C. COUNTIFS
D. DSUM

Answer: B. SUMIFS

Explanation: SUMIFS() adds values that satisfy multiple criteria.

5. What does the formula =COUNTIFS(A1:A100,">50",B1:B100,"Pass") do?

A. Counts values greater than 50 only
B. Counts rows where A > 50 AND B = "Pass"
C. Adds values greater than 50
D. Counts all "Pass" values

Answer: B. Counts rows where A > 50 AND B = "Pass"

6. Which function is traditionally used to search for a value vertically in the first column of a table?

A. HLOOKUP
B. VLOOKUP
C. LOOKUPH
D. SEARCH

Answer: B. VLOOKUP

Explanation: VLOOKUP() performs a vertical lookup and returns a value from a specified column.

7. In =VLOOKUP(E2,A2:D100,4,FALSE), what does 4 represent?

A. Row number
B. Search value
C. Table size
D. Column number

Answer: D. Column number

Explanation: 4 tells Excel to return the value from the fourth column of the specified table.

8. What does FALSE mean in a VLOOKUP formula?

A. Ignore errors
B. Approximate match
C. Exact match
D. Reverse lookup

Answer: C. Exact match

9. Which function is generally more flexible than VLOOKUP for modern Excel lookup operations?

A. SUM
B. XLOOKUP
C. ROUND
D. CONCAT

Answer: B. XLOOKUP

Explanation: XLOOKUP() can search in either direction and doesn't require the lookup column to be the first column of the return array.

10. Which formula returns "Yes" if either A1 or B1 is greater than 100?

A. =IF(AND(A1>100,B1>100),"Yes","No")
B. =IF(OR(A1>100,B1>100),"Yes","No")
C. =IF(A1>100,B1>100,"Yes")
D. =OR(IF(A1>100,B1>100))

Answer: B. =IF(OR(A1>100,B1>100),"Yes","No")

Explanation: OR() returns TRUE when at least one condition is TRUE.

11. Which function requires all specified conditions to be TRUE?

A. OR
B. IF
C. AND
D. NOT

Answer: C. AND

12. What does $A$1 represent?

A. Relative reference
B. Mixed reference
C. Absolute reference
D. 3-D reference

Answer: C. Absolute reference

Explanation: Both the column and row are locked.

13. What does $A1 represent?

A. Absolute reference
B. Mixed reference
C. Relative reference
D. External reference

Answer: B. Mixed reference

Explanation: The column A is fixed, but the row can change.

14. What does A$1 represent?

A. Absolute reference
B. Mixed reference
C. Relative reference
D. Circular reference

Answer: B. Mixed reference

Explanation: The row 1 is fixed, while the column can change.

15. Which formula can be used to handle an error and return "Not Found"?

A. =ERROR(A1,"Not Found")
B. =IFERROR(A1,"Not Found")
C. =IF(A1ERROR,"Not Found")
D. =ERRORIF(A1,"Not Found")

Answer: B. =IFERROR(A1,"Not Found")

16. What does =ROUND(15.678,2) return?

A. 15.67
B. 15.68
C. 15.7
D. 16

Answer: B. 15.68

Explanation: ROUND(number, num_digits) rounds the number to the specified number of decimal places.

17. Which function returns the remainder after division?

A. MOD
B. REMAINDER
C. DIV
D. QUOTIENT

Answer: A. MOD

Explanation: =MOD(17,5) returns 2.

18. Which function returns only the integer portion of a division?

A. MOD
B. QUOTIENT
C. INTDIV
D. DIVIDE

Answer: B. QUOTIENT

Explanation: =QUOTIENT(17,5) returns 3.

19. Which function can extract characters from the beginning of a text string?

A. LEFT
B. RIGHT
C. MID
D. START

Answer: A. LEFT

20. What does =MID("COMPUTER",3,4) return?

A. COMP
B. MPUT
C. OMPU
D. PUTER

Answer: C. OMPU

Explanation: Starting at character 3, Excel extracts 4 characters: O-M-P-U.

21. Which function removes extra spaces from text?

A. CLEAN
B. SPACE
C. TRIM
D. REMOVE

Answer: C. TRIM

Explanation: TRIM() removes extra spaces between words and leading/trailing spaces.

22. Which function can combine text from multiple cells using a delimiter?

A. TEXTJOIN
B. TEXTMERGE
C. JOINIF
D. CONCATIF

Answer: A. TEXTJOIN

23. What does =LEN("Excel") return?

A. 4
B. 5
C. 6
D. 0

Answer: B. 5

24. Which function returns the position of a value within a range?

A. FIND
B. MATCH
C. POSITION
D. LOCATE

Answer: B. MATCH

Explanation: MATCH() returns the relative position of an item within a range.

25. Which combination was traditionally used to perform a flexible lookup by combining position finding and value retrieval?

A. SUM + COUNT
B. INDEX + MATCH
C. IF + OR
D. ROUND + INT

Answer: B. INDEX + MATCH

26. What does =INDEX(A1:C10,3,2) return?

A. Value from row 2, column 3
B. Value from row 3, column 2
C. Value from cell C3
D. Value from A3

Answer: B. Value from row 3, column 2

27. Which function can return a filtered array of data in modern Excel?

A. FILTER
B. FILTERDATA
C. ARRAYFILTER
D. SELECT

Answer: A. FILTER

Explanation: Dynamic-array FILTER() can return records that meet specified conditions.

28. Which of the following is a dynamic-array function in modern Excel?

A. FILTER
B. VLOOKUP
C. SUM
D. ROUND

Answer: A. FILTER

29. What happens when a formula directly or indirectly refers to its own cell?

A. Absolute reference
B. Circular reference
C. Mixed reference
D. External reference

Answer: B. Circular reference

30. Which Excel feature is particularly useful for summarizing and analyzing large datasets without writing complex formulas?

A. WordArt
B. PivotTable
C. Page Break
D. Format Painter

Answer: B. PivotTable

Explanation: PivotTables allow users to quickly summarize, group, filter, and analyze large datasets.

31. Which formula counts cells containing the text "India" in A1:A100?

A. =COUNT(A1:A100,"India")
B. =COUNTIF(A1:A100,"India")
C. =COUNTA(A1:A100,"India")
D. =TEXTCOUNT(A1:A100,"India")

Answer: B. =COUNTIF(A1:A100,"India")

32. Which formula sums B1:B100 when the corresponding cells in A1:A100 contain "Sales"?

A. =SUMIF(A1:A100,"Sales",B1:B100)
B. =SUM(B1:B100,"Sales")
C. =SUMIFS(A1:A100,B1:B100,"Sales")
D. =COUNTIF(A1:A100,"Sales",B1:B100)

Answer: A. =SUMIF(A1:A100,"Sales",B1:B100)

33. Which operator is used for concatenating text in Excel?

A. +
B. &
C. *
D. #

Answer: B. &

Explanation: ="Hello "&"World" produces Hello World.

34. What is the result of =5^2?

A. 7
B. 10
C. 25
D. 32

Answer: C. 25

Explanation: ^ is the exponentiation operator. 5² = 25.

35. Which formula calculates the percentage represented by B2 out of A2?

A. =B2+A2*100
B. =B2/A2*100
C. =A2/B2*100
D. =B2-A2*100

Answer: B. =B2/A2*100

Explanation: Part ÷ Total × 100 gives the percentage.

36. Which function returns the current date?

A. NOW()
B. TODAY()
C. DATE()
D. CURRENTDATE()

Answer: B. TODAY()

Explanation: TODAY() returns the current date, while NOW() returns the current date and time.

37. Which function returns both the current date and current time?

A. DATE()
B. TIME()
C. NOW()
D. TODAYTIME()

Answer: C. NOW()

38. If A1 contains 10 and B1 contains 20, what is the result of =$A$1+B1 copied from C1 to C2?

A. 30
B. Depends on B2
C. Depends on A2
D. Error

Answer: B. Depends on B2

Explanation: $A$1 remains fixed at A1, while B1 changes to B2 when copied down.

39. Which formula is best suited to return the maximum sales value for a particular department when multiple criteria are involved?

A. MAX
B. MAXIFS
C. MAXIF
D. HIGHESTIFS

Answer: B. MAXIFS

Explanation: MAXIFS() returns the maximum value among cells satisfying one or more criteria.

40. Which of the following is the most appropriate modern Excel approach for returning a value from a table based on a lookup key?

A. XLOOKUP
B. SUM
C. ROUND
D. COUNT

Answer: A. XLOOKUP

Exam Tip: For advanced Excel questions, pay special attention to XLOOKUP, INDEX-MATCH, SUMIFS, COUNTIFS, AVERAGEIFS, IF/IFS, IFERROR, FILTER, SORT, absolute/mixed references, nested formulas, and error values. 

Labels:

Comments

Post a Comment

Popular posts from this blog

Top 5 Safe Chat Apps for Kids with Parental Controls

Now a days kids are growing up with technology at their fingertips. While communication apps help children stay connected with family and friends, safety remains a top concern for parents. Unregulated platforms can expose children to inappropriate content, online predators, and cyberbullying. That’s why choosing a kid-friendly chat app with parental controls is essential. To help parents find the best options, we’ve compiled a list of the top five safest chat apps for kids that offer safe and controlled environment. 1. Messenger Kids (By Meta) Best for: Safe video calls and text messaging with parental supervision Features: Parents approve all contacts No ads or in-app purchases Fun filters, stickers, and games Monitored messaging and activity reports Why It’s Safe: Messenger Kids is designed for children under 13 and requires parental approval for every contact. Parents can monitor chats and control screen time through the Parent Dashboard. Download Messenger Kids 2...
Post a Comment
Read more

10 Distance Vector and Link-State Routing MCQ

  10 multiple-choice questions  on  Distance Vector and Link-State Routing with explanations, useful for  UGC NET, SET, GATE, ISRO, and other competitive exams . Question 1: Basic Concept of Distance Vector Routing Q1: In a distance vector routing protocol, how do routers share routing information? A) By flooding the entire network with routing updates B) By periodically exchanging routing tables with their immediate neighbors C) By using a link-state database to determine the shortest path D) By dynamically updating paths based on link failure reports Answer: B) By periodically exchanging routing tables with their immediate neighbors Explanation: In Distance Vector Routing, routers share their routing tables with neighbors at regular intervals, leading to a simple but slow convergence mechanism. Question 2: Example of Distance Vector Routing Protocol Q2: Which of the following is an example of a Distance Vector routing protocol? A) OSPF ...
Post a Comment
Read more

10 Routing Protocol MCQ

10 multiple-choice questions on Routing protocol s  with explanations, useful for UGC NET, SET, GATE, ISRO, and other competitive exams . Question 1: Purpose of Routing Protocols Q1: What is the primary purpose of a routing protocol? A) To assign IP addresses to devices B) To determine the best path for data packets to reach their destination C) To encrypt data during transmission D) To physically connect different networks Answer: B) To determine the best path for data packets to reach their destination Explanation: Routing protocols help routers determine the most efficient path for data transmission across a network, ensuring fast and reliable communication. Question 2: Classification of Routing Protocols Q2: Routing protocols can be broadly classified into which two main categories? A) TCP and UDP B) Distance Vector and Link-State C) IPv4 and IPv6 D) Static and Dynamic Answer: B) Distance Vector and Link-State Explanation: Routing prot...
Post a Comment
Read more