Functions

Functions Interview with follow-up questions

1. Can you explain what a function is in Excel and why it is used?

A function in Excel is a named, built-in formula that takes one or more inputs (called arguments) and returns a calculated result. Functions handle tasks that would be impractical to write manually with operators alone.

Why functions are used:

  • Efficiency: =SUM(A1:A1000) replaces writing =A1+A2+A3+...+A1000.
  • Accuracy: Built-in logic reduces the chance of formula errors.
  • Readability: A named function communicates intent — =AVERAGE(B2:B50) is immediately clear.
  • Automation: Functions recalculate automatically whenever their input data changes.

Function syntax:

=FUNCTIONNAME(argument1, argument2, ...)

Arguments can be cell references, ranges, numbers, text strings, or even other functions (nested functions).

Categories of commonly used functions:

  • Math: SUM, ROUND, ABS, MOD, SUMIF, SUMIFS
  • Statistical: AVERAGE, MAX, MIN, COUNT, COUNTA, COUNTIF
  • Logical: IF, IFS, AND, OR, NOT, IFERROR, SWITCH
  • Lookup: XLOOKUP, VLOOKUP, INDEX, MATCH, FILTER, UNIQUE
  • Text: LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, PROPER, TEXTJOIN, TEXTSPLIT
  • Date & Time: TODAY, NOW, DATE, DATEDIF, EOMONTH, NETWORKDAYS
  • Dynamic array (Excel 365): FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY

Excel 365 additions: LET allows assigning named variables inside a formula, and LAMBDA lets you create your own reusable custom functions — both significantly extend what functions can do without VBA.

↑ Back to top

Follow-up 1

Can you give an example of a function you have used?

Sure! One example of a function in Excel is the SUM function. It is used to add up a range of cells. For example, the formula =SUM(A1:A5) adds up the values in cells A1 to A5.

Follow-up 2

What is the syntax for a function in Excel?

The syntax for a function in Excel consists of the function name followed by parentheses. Inside the parentheses, you can provide arguments, which are the inputs for the function. For example, the syntax for the SUM function is =SUM(argument1, argument2, ...). Each argument can be a cell reference, a value, or another function.

Follow-up 3

How does Excel handle errors in functions?

Excel provides several ways to handle errors in functions. One way is to use the IFERROR function, which allows you to specify a value or action to take if an error occurs. For example, the formula =IFERROR(A1/B1, "Error: Division by zero") will display the result of the division A1/B1, but if an error occurs (e.g., if B1 is zero), it will display the message "Error: Division by zero" instead. Another way to handle errors is to use the IF function in combination with the ISERROR function to check for errors and perform different actions based on the result.

2. What is the difference between SUM and AVERAGE functions in Excel?

Both SUM and AVERAGE operate on a range of numbers, but they answer different questions.

SUM

=SUM(A1:A10)

Adds all numeric values in the range and returns the total. Ignores blank cells and text. Use it when you need to know the grand total — total revenue, total units sold, total hours worked.

AVERAGE

=AVERAGE(A1:A10)

Adds all numeric values in the range and divides by the count of numeric cells, returning the mean. Blank cells and text are excluded from both the sum and the count (so blanks do not drag the average down to zero). Use it when you need the typical or central value — average score, average order size, average daily temperature.

Key difference: If A1:A5 contains 10, 20, 30, 40, 50:

  • =SUM(A1:A5) → 150
  • =AVERAGE(A1:A5) → 30 (150 ÷ 5)

Handling blanks and zeros:

A blank cell is excluded from AVERAGE — it is not treated as zero. A cell containing the number 0 is included and does count toward the average. This distinction matters when your data has genuine zeros versus missing data.

Conditional variants (common interview follow-up):

  • =SUMIF / =SUMIFS — sum where conditions are met
  • =AVERAGEIF / =AVERAGEIFS — average where conditions are met

Example: =AVERAGEIF(B2:B100,"North",C2:C100) — averages the values in column C only for rows where column B equals "North".

↑ Back to top

Follow-up 1

Can you provide a scenario where you would use each?

You would use the SUM function in Excel when you want to calculate the total of a range of values. For example, if you have a column of numbers representing sales for each month, you can use the SUM function to calculate the total sales for the year.

On the other hand, you would use the AVERAGE function in Excel when you want to calculate the average of a range of values. For example, if you have a column of test scores for a class, you can use the AVERAGE function to calculate the average score.

Follow-up 2

How would you handle errors when using these functions?

When using the SUM and AVERAGE functions in Excel, you may encounter errors if the range of cells contains non-numeric values or if the range is empty. To handle these errors, you can use the IFERROR function in combination with the SUM or AVERAGE function.

For example, if you want to calculate the sum of a range of cells but some of the cells contain non-numeric values, you can use the following formula: =IFERROR(SUM(A1:A10), 0). This formula will return the sum of the numeric values in the range A1:A10, and if any of the cells contain non-numeric values, it will return 0.

Similarly, you can use the IFERROR function with the AVERAGE function to handle errors in a similar way.

Follow-up 3

What are some alternatives to these functions?

In addition to the SUM and AVERAGE functions, Excel provides several other functions that can be used to perform calculations on ranges of cells. Some alternatives to the SUM function include the SUBTOTAL function, which can be used to calculate various types of subtotals, and the SUMIF function, which can be used to calculate the sum of values that meet specific criteria.

Similarly, some alternatives to the AVERAGE function include the MEDIAN function, which calculates the median of a range of values, and the MODE function, which calculates the mode of a range of values.

3. How would you use the COUNT function in Excel?

The COUNT function counts the number of cells in a range that contain numeric values (including dates and times, which are stored as numbers internally). It ignores blank cells and cells containing text.

Syntax:

=COUNT(value1, [value2], ...)

Each argument can be a single cell, a range, or a literal value. Multiple ranges are allowed.

Examples:

=COUNT(A1:A10)        ' counts numeric cells in A1:A10
=COUNT(A1:A10, C1:C10) ' counts numeric cells across two ranges
=COUNT(A:A)            ' counts all numeric cells in column A

COUNT vs. related counting functions:

Function Counts
COUNT Cells with numeric values only
COUNTA All non-empty cells (numbers, text, errors, booleans)
COUNTBLANK Empty cells in a range
COUNTIF Cells meeting one condition
COUNTIFS Cells meeting multiple conditions

COUNTIF example:

=COUNTIF(B2:B100, "North")

Counts how many cells in B2:B100 contain the text "North". Wildcards work too: "N*" counts any value starting with N.

Practical use: COUNT is commonly used to find how many data points exist in a column — useful for checking completeness before calculating an average or building a dashboard metric. If you expect 100 rows of numbers but COUNT returns 87, you know 13 cells are blank or contain non-numeric data.

↑ Back to top

Follow-up 1

Can you give an example of a scenario where the COUNT function would be useful?

Sure! Let's say you have a list of sales data in column A, and you want to count the number of sales that are above a certain threshold. You can use the COUNT function with a condition to achieve this.

For example, if you want to count the number of sales that are above $1000, you can use the formula =COUNTIF(A1:A10, ">1000"). This will count the number of cells in the range A1:A10 that are greater than 1000.

Follow-up 2

What is the difference between COUNT and COUNTA functions?

The COUNT function in Excel counts the number of cells in a range that contain numbers, whereas the COUNTA function counts the number of cells in a range that are not empty. The COUNTA function includes cells that contain text, logical values, errors, and empty strings.

For example, if you have a range of cells A1:A5 where A1 and A3 contain numbers, A2 contains text, and A4 and A5 are empty, the COUNT function will return 2 and the COUNTA function will return 4.

Follow-up 3

How would you use the COUNT function with condition?

To use the COUNT function with a condition, you can use the COUNTIF function in combination with the COUNT function.

For example, if you have a range of cells A1:A5 that contains numbers, and you want to count the number of cells that are greater than 5, you can use the formula =COUNTIF(A1:A5, ">5").

Alternatively, you can use the COUNTIFS function to count cells based on multiple conditions. For example, if you have a range of cells A1:A5 that contains numbers, and you want to count the number of cells that are greater than 5 and less than 10, you can use the formula =COUNTIFS(A1:A5, ">5", A1:A5, "<10").

4. Can you explain how the IF function works in Excel?

The IF function tests a condition and returns one of two values depending on whether the condition is TRUE or FALSE:

=IF(logical_test, value_if_true, value_if_false)

Arguments:

  • logical_test: an expression that evaluates to TRUE or FALSE — comparisons like A1>100, B2="Approved", C3<>0, or functions like ISNUMBER(A1).
  • value_if_true: the value returned when the test is TRUE. Can be a number, text, formula, or another function.
  • value_if_false: the value returned when the test is FALSE. Omitting it returns FALSE; using "" returns an empty string.

Basic example:

=IF(A1>100, "Over budget", "On track")

Returns "Over budget" if A1 exceeds 100, otherwise "On track".

Nested IF for multiple outcomes:

=IF(A1>=90,"A", IF(A1>=80,"B", IF(A1>=70,"C","F")))

Modern alternatives (Excel 365):

  • IFS — cleaner multi-condition version, no nesting required: =IFS(A1>=90,"A", A1>=80,"B", A1>=70,"C", TRUE,"F")
  • SWITCH — matches a value against a list of options, ideal for discrete cases: =SWITCH(A1, 1,"Low", 2,"Medium", 3,"High", "Unknown")

Combining with AND/OR:

=IF(AND(A1>50, B1="Active"), "Eligible", "Not eligible")
=IF(OR(A1="Manager", A1="Director"), "Senior", "Staff")

Error handling with IFERROR:

=IFERROR(A1/B1, 0)

Returns 0 instead of #DIV/0! — the modern replacement for the older IF(ISERROR(...)) pattern.

↑ Back to top

Follow-up 1

Can you provide an example of how you have used the IF function?

Sure! Here's an example of how I have used the IF function in Excel:

Let's say we have a spreadsheet with a column of student scores in column A, and we want to calculate the corresponding grade for each score. We can use the IF function to do this. Assuming the passing score is 60, we can use the following formula in cell B2:

=IF(A2 >= 60, "Pass", "Fail")

This formula will check if the score in cell A2 is greater than or equal to 60. If it is, it will return the string "Pass"; otherwise, it will return the string "Fail". We can then copy this formula down the column to calculate the grades for all the students.

Follow-up 2

What are some common errors you might encounter when using the IF function and how would you handle them?

Some common errors you might encounter when using the IF function in Excel include:

  1. #VALUE! error: This error occurs when the logical_test or either of the value_if_true or value_if_false arguments are not valid. To fix this error, you should check that the arguments are correctly formatted and that any cell references are valid.

  2. #NAME? error: This error occurs when Excel does not recognize the function name. To fix this error, you should check that the function name is spelled correctly and that any necessary add-ins or libraries are installed.

  3. #DIV/0! error: This error occurs when you divide a number by zero in one of the value_if_true or value_if_false arguments. To fix this error, you should add an additional logical test to check for a zero divisor before performing the division.

  4. #N/A error: This error occurs when a value is not available or cannot be found. To handle this error, you can use the IFERROR function to display a custom message or value instead of the error.

Follow-up 3

How would you use nested IF functions?

Nested IF functions in Excel allow you to perform multiple logical tests and return different values based on the results of those tests. You can nest up to 64 levels of IF functions in Excel.

Here's an example of how you can use nested IF functions:

Let's say we have a spreadsheet with a column of student scores in column A, and we want to calculate the corresponding grade for each score. We can use nested IF functions to assign letter grades based on the score ranges. Assuming the following grade ranges:

  • A: 90 and above
  • B: 80 to 89
  • C: 70 to 79
  • D: 60 to 69
  • F: below 60

We can use the following formula in cell B2:

=IF(A2 >= 90, "A", IF(A2 >= 80, "B", IF(A2 >= 70, "C", IF(A2 >= 60, "D", "F"))))

This formula will check the score in cell A2 and return the corresponding letter grade based on the nested IF functions. We can then copy this formula down the column to calculate the grades for all the students.

5. What is your experience with using Excel functions to manipulate text?

Excel has a rich set of text functions. In interviews, demonstrating familiarity with both the classic functions and the newer Excel 365 additions stands out.

Core text manipulation functions:

  • LEFT(text, n) — extracts the first n characters: =LEFT(A1, 3) returns the first 3 characters.
  • RIGHT(text, n) — extracts the last n characters.
  • MID(text, start, n) — extracts n characters starting at a given position: =MID(A1, 4, 5).
  • LEN(text) — returns the total number of characters in a string.
  • TRIM(text) — removes leading, trailing, and extra internal spaces (essential for cleaning imported data).
  • FIND(find_text, within_text) — returns the position of one string within another (case-sensitive).
  • SEARCH(find_text, within_text) — like FIND but case-insensitive and supports wildcards.
  • SUBSTITUTE(text, old_text, new_text) — replaces all instances of a substring.
  • REPLACE(text, start, n, new_text) — replaces characters at a specific position.
  • UPPER(text) / LOWER(text) / PROPER(text) — change case.
  • TEXTJOIN(delimiter, ignore_empty, range) — joins multiple values with a separator.
  • CONCAT(text1, text2, ...) — joins values with no delimiter (accepts ranges).

Excel 365 additions (2022+):

  • TEXTBEFORE(text, delimiter) — returns everything before a delimiter: =TEXTBEFORE(A1,"@") extracts the username from an email.
  • TEXTAFTER(text, delimiter) — returns everything after a delimiter.
  • TEXTSPLIT(text, col_delimiter, row_delimiter) — splits a string into a dynamic array across columns or rows.

Practical example combining functions:

To extract a domain from an email address in A1:

=TEXTAFTER(A1, "@")

Or with the classic approach: =MID(A1, FIND("@",A1)+1, LEN(A1)).

The TEXTAFTER/TEXTBEFORE/TEXTSPLIT trio dramatically simplifies tasks that previously required complex nested formulas.

↑ Back to top

Follow-up 1

Can you give an example of a text manipulation function and explain how it works?

Sure! One example of a text manipulation function is the CONCATENATE function. It is used to combine multiple text strings into one. For example, if you have the text 'Hello' in cell A1 and 'World' in cell B1, you can use the CONCATENATE function like this: =CONCATENATE(A1, B1) which will result in 'HelloWorld'. The function takes multiple arguments and concatenates them in the order they are provided.

Follow-up 2

How would you use functions to change the case of text in Excel?

To change the case of text in Excel, you can use the UPPER, LOWER, or PROPER functions. The UPPER function converts all letters in a text string to uppercase, the LOWER function converts all letters to lowercase, and the PROPER function capitalizes the first letter of each word. For example, if you have the text 'hello world' in cell A1, you can use the UPPER function like this: =UPPER(A1) which will result in 'HELLO WORLD'. Similarly, you can use the LOWER function or the PROPER function to change the case of text.

Follow-up 3

What are some challenges you might encounter when manipulating text with functions and how would you handle them?

Some challenges you might encounter when manipulating text with functions include dealing with leading/trailing spaces, handling errors when manipulating text that doesn't meet certain criteria, and working with text that contains special characters. To handle leading/trailing spaces, you can use the TRIM function to remove them. To handle errors, you can use functions like IFERROR or IF statements to check for specific conditions before applying text manipulation functions. When working with special characters, you may need to use additional functions like SUBSTITUTE or CHAR to replace or manipulate them as needed.

Live mock interview

Mock interview: Functions

Intermediate ~5 min Your own free AI key

Your voice and your AI key never touch our servers; the key stays in this browser and is sent only to Google. Only your round scores are saved to track progress.