Excel Formulas

Top 10 Excel Formulas Interview Questions & Answers (2022)

1) SUM formula: =SUM (C2,C3,C4,C5)

Excel’s SUM function is what’s utilised to determine the overall sum of the numbers. For example, here we had computed the total number of computer products sold throughout different regions in the United States of America by using the formula =SUM (C2,C3,C4,C5) by the time you reach to the end you get the total of $ 20, 500, as indicated in the following formula. When writing a formula, you must include the column or row number that you want to add inside of any brackets that are present.

Excel Formula Interview Questions
Interview Questions Regarding the SUM Formula in Excel

2) Average Formula: = Average (C2,C3,C4,C5)
Excel’s “average” formula can be applied to any set of numbers in order to calculate their average value. As an example, we have determined the national average for the amount of money spent on computer-related goods. The first screen photo illustrates the calculation that we used for our data, which is denoted by the formula = Average (C2, C3, C4, and C5).

Interview Questions about Excel’s Formulas for the Average Formula

The following screen capture demonstrates the average amount of money that was saved when the formula was applied.

3) The formula for SumIF is as follows: SUMIF (A2:A7,”Items requested”, D2:D7)

The SumIF returns the total number of items for any ranges that have been selected. For example, in this situation, we simply want to determine the total revenue generated by sales of software products; in order to accomplish this, we will use the formula =SUMIF (A2:A7, “software”, D2:D7). Here, A2 and A7 establish the range for software, and by using the same process, we can determine the quantity of sales for hardware. (“hardware”, A2:A7, D2:D7)

Excel Formula Interview Questions
Excel Formula Interview Questions
The table in the following screen shot displays the total revenue generated from sales of both hard and soft ware.

Excel Formula Interview Questions
SumIF formula

4) COUNTIF Formula: COUNTIF(D2:D7, “Function”)
The COUNTIF function has a variety of applications; the formula can be used in a variety of contexts. To demonstrate how to use the COUNTIF function, we will use a straightforward example in which our goal is to determine the total number of cells whose values are larger than $3000. In order to determine this, we will use the formula =COUNTIF(D2:D7,”3000″) to analyse the data.

Interview Questions Regarding the Excel Formula
The screen capture that follows demonstrates the total number of cells that have a value that is higher than 3000.

Interview Questions Regarding the Excel Formula
COUNTIF Formula

5) Use the concatenate function, which is written as =CONCATENATE(C4,Text, D4,Text,…)

Excel’s Concatenate function is used to join many segments of text or segments into a single sentence so that it can be shown. For instance, in this case, we want to display the text “New York has the highest sale of a total of 12,000 dollars,” and in order to achieve so, we will use the formula =CONCATENATE(C4,”has the highest sale of”,D4,dollar”).

Interview Questions Regarding the Excel Formula
The text will appear as shown in the screen photo below after you execute the formula and display it.

Interview Questions Regarding the Excel Formula
The Function of Concatenating

6) Int Formula: int (this number)
The int formula is what’s utilised to get rid of the integer part of the number, as seen in the sample below below.

Interview Questions Regarding the Excel Formula
Interview Questions for Excel’s Formula Int Formula Excel Interview Questions

7) MAX Formula: =Max (D2:D7)

This Excel formula will save the cells that have the highest value in the column. As an illustration, in this case, we are interested in the highest value for computer items, and the algorithm keeps the value 12,000 dollars. You may also use the same technique to calculate the minimal value; however, in the formula, you will need to change the word “Max” to “Min.”

Interview Questions Regarding the Excel Formula
The following screen shot displays the value that is highest in the column.

Interview Questions Regarding the Excel Formula
MAX Formula

8) Factorial Formula= FACT (number)
The result of the factorial formula is the factorial of the given integer. We apply this procedure to determine the factorial number for 3, which is 3. You can use this method to get the likelihood of any number; in this case, we will use the factor 3, which is equal to 3 times 2 times 1.

Interview Questions Regarding the Excel Formula
Interview Questions Regarding the Excel Formula
Factorial Formula

9) VLookup Formula = Vlookup (value, range, and get me value in the column, is my list sorted)

The VLookup formula is utilised in situations in which the user is familiar with some aspect of an item or person and wishes to maintain additional formations depending on that aspect. For illustration’s sake, let’s take a look at the keyboard. You are aware of the keyboard’s suggested retail price, but you do not know how much money the company made in total sales from selling keyboards in the state of California. In order to be aware that you will using =Vlookup (20,D2:D7,2,False). Using this formula, you will be able to calculate the sum of all sales depending on the retail price. If you are using this formula, you have to make sure that anything you are inserting as a reference is one of a kind; for instance, if you are looking for a certain employee by their ID number, that number must not be shared with anybody else, or else the formula will give you an error.

Interview Questions Regarding the Excel Formula
When the formula is run, the total amount of sales that is displayed is $2500.

Interview Questions Regarding the Excel Formula
VLookup Formula

10) The formula for the IF function is: IF (E2 > 2000, correct/Incorrect).
The IF function has been used here; you will use this function whenever you want to determine whether the following condition has been met correctly or incorrectly. The word “good” has been used in this context since any sales that are higher than 2000 should be considered to be good. In a similar fashion, you have the option of setting something as “bad,” “correct,” or “incorrect.”

Interview Questions Regarding the Excel Formula
The following table demonstrates that when we used our formula, the cells that were “good” were highlighted.

Interview Questions Regarding the Excel Formula
IF function formula