5 Excel Questions mostly asked in Job Interviews

10 Advanced Excel Formulas

Here is a list of Excel formulas, categorized for ease of understanding:

SUM(number1, [number2], ...) - Adds all the numbers in a range of cells.
SUBTRACT(number1, number2) - Subtracts the second number from the first (use - operator).
MULTIPLY(number1, number2) - Multiplies the two numbers (use * operator).
DIVIDE(number1, number2) - Divides the first number by the second (use / operator).

IF(logical_test, value_if_true, value_if_false) - Returns one value if a condition is true and another value if it’s false.
AND(logical1, [logical2], ...) - Returns TRUE if all arguments are TRUE.
OR(logical1, [logical2], ...) - Returns TRUE if any argument is TRUE.
NOT(logical) - Reverses the logic of its argument.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) - Looks for a value in the first column of a table and returns a value in the same row from a specified column.
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) - Looks for a value in the first row of a table and returns a value in the same column from a specified row.
INDEX(array, row_num, [column_num]) - Returns the value of an element in a table or an array, selected by the row and column number indexes.
MATCH(lookup_value, lookup_array, [match_type]) - Returns the relative position of an item in an array that matches a specified value.
CHOOSE(index_num, value1, [value2], ...) - Returns a value from the list of values based on the index number.

CONCATENATE(text1, [text2], ...) - Joins several text items into one text item (use & operator in newer versions).
LEFT(text, [num_chars]) - Returns the specified number of characters from the start of a text string.
RIGHT(text, [num_chars]) - Returns the specified number of characters from the end of a text string.
MID(text, start_num, num_chars) - Returns a specific number of characters from a text string, starting at the position you specify.
LEN(text) - Returns the number of characters in a text string.
TRIM(text) - Removes all spaces from text except for single spaces between words.
UPPER(text) - Converts text to uppercase.
LOWER(text) - Converts text to lowercase.
PROPER(text) - Capitalizes the first letter of each word in a text string.
TEXT(value, format_text) - Converts a value to text in a specific number format.

TODAY() - Returns the current date.
NOW() - Returns the current date and time.
DATE(year, month, day) - Returns the number that represents the date in Excel date-time code.
DATEVALUE(date_text) - Converts a date in the form of text to a serial number.
DAY(serial_number) - Converts a serial number to a day of the month.
MONTH(serial_number) - Converts a serial number to a month.
YEAR(serial_number) - Converts a serial number to a year.
HOUR(serial_number) - Converts a serial number to an hour.
MINUTE(serial_number) - Converts a serial number to a minute.
SECOND(serial_number) - Converts a serial number to a second.
WEEKDAY(serial_number, [return_type]) - Converts a serial number to a day of the week.
WEEKNUM(serial_number, [return_type]) - Converts a serial number to a number representing where the week falls numerically with a year.

AVERAGE(number1, [number2], ...) - Returns the average of its arguments.
MEDIAN(number1, [number2], ...) - Returns the median of the given numbers.
MODE.SNGL(number1, [number2], ...) - Returns the most frequently occurring, or repetitive, value in an array or range of data.
STDEV.P(number1, [number2], ...) - Calculates standard deviation based on the entire population.
STDEV.S(number1, [number2], ...) - Estimates standard deviation based on a sample.
VAR.P(number1, [number2], ...) - Calculates variance based on the entire population.
VAR.S(number1, [number2], ...) - Estimates variance based on a sample.

PMT(rate, nper, pv, [fv], [type]) - Calculates the payment for a loan based on constant payments and a constant interest rate.
FV(rate, nper, pmt, [pv], [type]) - Returns the future value of an investment based on periodic, constant payments and a constant interest rate.
PV(rate, nper, pmt, [fv], [type]) - Returns the present value of an investment; the total amount that a series of future payments is worth now.
NPV(rate, value1, [value2], ...) - Calculates the net present value of an investment based on a series of periodic cash flows and a discount rate.
IRR(values, [guess]) - Returns the internal rate of return for a series of cash flows.

TRANSPOSE(array) - Transposes the rows and columns of an array.
FREQUENCY(data_array, bins_array) - Calculates how often values occur within a range of values and then returns a vertical array of numbers.

DSUM(database, field, criteria) - Adds the numbers in the field column of records in the database that match the criteria.
DAVERAGE(database, field, criteria) - Returns the average of selected database entries.
DCOUNT(database, field, criteria) - Counts the cells that contain numbers in the field column of records in the database that match the criteria.
DMAX(database, field, criteria) - Returns the maximum value from selected database entries.
DMIN(database, field, criteria) - Returns the minimum value from selected database entries.

ISNUMBER(value) - Returns TRUE if the value is a number.
ISTEXT(value) - Returns TRUE if the value is text.
ISBLANK(value) - Returns TRUE if the value is blank.
ISERROR(value) - Returns TRUE if the value refers to any error value.
ISNA(value) - Returns TRUE if the value refers to the #N/A error value.
IFERROR(value, value_if_error) - Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula.

ROUND(number, num_digits) - Rounds a number to a specified number of digits.
ROUNDUP(number, num_digits) - Rounds a number up, away from zero.
ROUNDDOWN(number, num_digits) - Rounds a number down, toward zero.
INT(number) - Rounds a number down to the nearest integer.
MOD(number, divisor) - Returns the remainder from division.
POWER(number, power) - Returns the result of a number raised to a power.
SQRT(number) - Returns a positive square root.
SUMIF(range, criteria, [sum_range]) - Adds the cells specified by a given condition or criteria.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) - Adds the cells specified by a set of conditions or criteria.
PRODUCT(number1, [number2], ...) - Multiplies all the numbers given as arguments.
ABS(number) - Returns the absolute value of a number.

CONVERT(number, from_unit, to_unit) - Converts a number from one measurement system to another.