Top 10 Excel Formulas Interview Questions & Answers (2022)

[soybean]

Following are the Most Frequently Asked Excel Formulas in an interview

 

[soybean]

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

In excel, SUM formula is used to calculate the total number. For instance here we had calculated the total number of computer items sold across different region in U.S.A by using formula =SUM (C2,C3,C4,C5) at the end you get the total $ 20, 500, as shown in next formula. In formula, inside bracket you have to mention the column or row number which you want to add.

SUM formula

 

[soybean]

2) Average Formula: = Average (C2,C3,C4,C5)


In excel, the average formula, is used to retrieve the average for any number. Like we have calculated the average sales of computer merchandise across U.S.A. The first screen shot highlights the formula, i.e., = Average (C2,C3,C4,C5) that we have applied for our data.

Average Formula

The below screen shot shows the average amount that we retained after applying the formula.

 

[soybean]

3) SumIF formula = SUMIF (A2:A7,“Items wanted”, D27)

The SumIF gives the total number of any items for selected ranges. For instance here we want to calculate only the total sales amount for software items, to do that we will apply the formula as =SUMIF (A2:A7, “software”, D27). Here A2 and A7 defines the range for software and same way we can find sales amount for hardware. (A2:A7, “hardware”, D27).

 

[soybean]

Below screen-shot show the total sale amount of hard-ware and soft-ware in the table.

SumIF formula

 

[soybean]

4) COUNTIF Formula: COUNTIF(D27, “Function”)


COUNTIF function offers wide application; you can apply the formula according. Here we have taken a simple example of COUNTIF function, where our motive is to find the total number of cells whose value is greater than $3000. In order to know that we will apply the formula =COUNTIF(D27,”3000”).

 

[soybean]

Below screen shot shows the total number of cells that has value greater than 3000.

COUNTIF Formula

 

[soybean]

5) Concatenate Function: =CONCATENATE(C4,Text, D4, Text,…)

Concatenate function is used in excel to connect different segment or text to display as a single sentence. For example, here we want to display text as “NewYork has the highest sale of 12000 dollars”, for that we will use the formula =CONCATENATE(C4,”has the highest sale of”,D4,dollar”).

 

[soybean]

When you execute the formula and display the text as show in below screen-shot

View attachment 453448

Concatenate Function

 

[soybean]

6) Int Formula: int (this number)


Int formula is used to remove integer from the number like we have demonstrated over here in below example.

Int Formula