Excel Formula and Function Interview Questions And Answers

COUNT()

With the help of CONCATENATE function or Ampersand Operator(&).

COUNT() is used to count cells containing numeric values, dates, etc. ,it can count numeric values except blanks. COUNTA() counts all the values whther they are nbumeric,text,dates etc excluding blanks.The A in COUNTA refers to All. COUNTBLANK counts all blank cells as the name suggests. COUNTIF and COUNTIFS count cells with matching criteria,e.g. cells with value "Apple".

A formula that either directly or indirectly refer to itself is known as circular reference in excel.

Substitute function in excel provides substitute for an instance of text, consider this as a translator which translates a particular value with another value.It works on the basis of value. Syntax-SUBSTITUTE(text, oldText, newText, [instanceNumber]). Replace function swaps a string or part of a string with another string based on the position. Syntax-REPLACE(oldText, startNumber, NumberOfCharacters, newText).

INT function in excel removes fraction part and returns integer. e.g.INT(143.23) will return 143.

Meaning of dollar sign "$" in excel formula.

PMT function

Automatically calculates the result if some values involved in the calculation change or replaced. Makes complex calculation easy once we write formula for that.