Formulas & Functions Questions

🔹 Formulas & Functions

Q1. What is the difference between a Formula and a Function?
Answer:

Formula: A user-defined expression like =A1+B1.
Function: A built-in predefined operation like =SUM(A1:A10).

Q2. What is the syntax of a Function in Excel?
Answer:
The general syntax is:
=FUNCTION_NAME(arguments)
👉 Example: =AVERAGE(A1:A10)

Q3. What does the SUM function do?
Answer:
The SUM function adds a range of numbers.
👉 Example: =SUM(A1:A5) adds values from A1 to A5.

Q4. What is the use of the AVERAGE function?
Answer:
It calculates the mean of values.
👉 Example: =AVERAGE(10,20,30) returns 20.

Q5. What is the difference between MIN and MAX functions?
Answer:

MIN: Returns the smallest value in a range.
MAX: Returns the largest value in a range.

Q6. What does the COUNT function do?
Answer:
The COUNT function counts the number of numeric values in a range.
👉 Example: =COUNT(A1:A10)

Q7. What is the difference between COUNT and COUNTA?
Answer:

COUNT: Counts only numbers.
COUNTA: Counts all non-empty cells (numbers, text, etc.).

Q8. What is the IF function in Excel?
Answer:
The IF function tests a condition and returns different values depending on whether it is TRUE or FALSE.
👉 Syntax: =IF(condition, value_if_true, value_if_false)
👉 Example: =IF(A1>50,"Pass","Fail")

Q9. What is the difference between IF and Nested IF?
Answer:

IF: Single condition check.
Nested IF: Multiple conditions by placing IF inside IF.
👉 Example:
=IF(A1>80,"A",IF(A1>60,"B","C"))

Q10. What is VLOOKUP in Excel?
Answer:
VLOOKUP (Vertical Lookup) searches for a value in the first column of a table and returns a value from another column.
👉 Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Q11. What is HLOOKUP in Excel?
Answer:
HLOOKUP (Horizontal Lookup) searches for a value in the first row of a table and returns a value from another row.

Q12. What is the difference between VLOOKUP and HLOOKUP?
Answer:

VLOOKUP: Searches vertically in a column.
HLOOKUP: Searches horizontally in a row.

Q13. What is the difference between VLOOKUP and INDEX-MATCH?
Answer:

VLOOKUP: Limited, cannot look left.
INDEX-MATCH: More flexible and powerful, can look in any direction.

Q14. What does the TRIM function do?
Answer:
TRIM removes extra spaces from text, leaving only single spaces between words.
👉 Example: =TRIM(" Excel Basics ") → “Excel Basics”

Q15. What is the use of the LEN function?
Answer:
LEN returns the number of characters (including spaces).
👉 Example: =LEN("Excel") → 5

Q16. What does the CONCATENATE (or CONCAT) function do?
Answer:
It combines text from multiple cells into one.
👉 Example: =CONCAT(A1," ",B1)

Q17. What is the LEFT and RIGHT function?
Answer:

LEFT(text, n): Returns first n characters from the left.
RIGHT(text, n): Returns last n characters from the right.

Q18. What is the MID function?
Answer:
MID extracts a part of a text string from the middle.
👉 Syntax: =MID(text, start_num, num_chars)

Q19. What is the PROPER, UPPER, and LOWER function?
Answer:

PROPER: Capitalizes first letter of each word.
UPPER: Converts all text to uppercase.
LOWER: Converts all text to lowercase.

Q20. What is the NOW function?
Answer:
The NOW function displays the current date and time.

Q21. What is the TODAY function?
Answer:
The TODAY function returns only the current date.

Q22. What does the ROUND function do?
Answer:
ROUND changes a number to the specified number of digits.
👉 Example: =ROUND(12.345,2) → 12.35

Q23. What is the difference between ROUND, ROUNDUP, and ROUNDDOWN?
Answer:

ROUND: Rounds normally.
ROUNDUP: Always rounds up.
ROUNDDOWN: Always rounds down.

Q24. What does the PMT function do?
Answer:
PMT calculates the payment for a loan based on constant interest rate and time.
👉 Example: =PMT(rate, nper, pv)

Q25. What is the difference between ISNUMBER, ISTEXT, and ISBLANK?
Answer: