1. Coming to the Excel formulas, What is their order of precedence of operations?
Parentheses
Reference operators
Exponents
Negation
Percent
Multiplication and Division
Addition and Subtraction
Concatenation
Logical operators
2. I want Named ranges. How to make it happen in Excel?
Select the range you want to name, including the row or column labels.
Click Formulas > Create from Selection.
In the Create Names from Selection dialog box, select the checkbox (es) depending on the
location of your row/column header. ...
Click OK.
3. Define Relative Cell Address
By default, a cell reference is a relative reference, which means that the reference is relative
to the location of the cell. If, for example, you refer to cell A2 from cell C2,
4. Define Absolute Cell Address?
an absolute reference is a cell reference in which the column and row coordinates stay
constant while copying a formula from one cell to the other. A dollar symbol ($) is used
before the coordinates to correct them. For instance, $D$2 is an absolute reference to cell
D2
5. What is a formula in excel?
Formulas in spreadsheet programs such as Excel are used to perform calculations on values
entered and stored in the [Link]'s main function is to interlink cells,through the use
of formulas, so that the formula cell changes when data is inputted into linked cells. The best
way to store information in spreadsheets is through the use of [Link] can range
from basic mathematical operations-such as addition and subtraction-to complex
engineering and statistical calculations.
6. Give an example of a formula.
=A2*A3 Multiplies the value in cell A1 times the value
in A2
7. Name some mathematical functions we use in excel.
SUM.
AVERAGE.
AVERAGEIF.
COUNTA.
COUNTIF.
MOD.
ROUND
8. What does the COUNT function do?
counts the number of cells that contain numbers, and counts numbers within the list of
arguments. Use the COUNT function to get the number of entries in a number field that is in
a range or array of numbers.
9. What does the AVERAGE function do?
Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20
contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.
10. Name various types of functions we find in Excel.
Excel groups functions into 12 categories: Compatibility, Cube, Database, Date and Time,
Engineering, Financial, Information, Logical, Lookup & Reference, Math & Trigonometry,
Statistical and Text. There is an additional category for user-defined functions installed with
add-ins.
11. Name 5 COUNT functions.
COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS.
12. Define COUNTIF
to count the number of cells that meet a criterion; for example, to count the number of
times a particular city appears in a customer list.
13. Define COUNTIFS
applies criteria to cells across multiple ranges and counts the number of times all criteria are
met.
14. Define COUNTA
counts cells containing any type of information, including error values and empty text ("").
15. Define COUNTBLANK.
to count the number of empty cells in a range of cells.
16. Tell us the major difference you find between formulas and functions in Excel?
Formulas are defined as a statement written by any user, either simple or complex whereas
a function is a type of formula which is pre-defined. On the other hand, functions are
predefined formulas that are already there in the sheet.
17. What does the IF function do?
The IF function is one of the most popular functions in Excel, and it allows you to make
logical comparisons between a value and what you expect. So an IF statement can have two
results. The first result is if your comparison is True, the second if your comparison is False.
18. How Cell Reference Is Useful In The Calculation?
In order to avoid writing the data again and again for calculating purpose, cell reference is
used. When you write any formula, for specific function, you need to direct excel the specific
location of that data. This location is referred as, cell reference. So, every time a new value
added to the cell, the cell will calculate according to the reference cell formula.
19. Explain what is RAND?
Answer: A rand function generates random numbers, starting from 0.001 to 0.999. All the
rand numbers generated here are unique, a caution that any user must take when working
with rand is keep the formula calculations to manual else the RAND will generate a fresh set
of numbers. A use case to use this function is when we have a large data and we need only
sampled rows, to avoid being biased we can let the system give some random numbers. We
can further take help of IF condition to filter the exact samples we need to pick.
20. Explain what is RANDBETWEEN?
Answer: RANDBETWEEN Function is available in the versions above 2013 and office365
version, this can generate random numbers between any given numbers. However, these
are not unique. You need to keep the calculation into manual when using this function else
every change will lead to a random number from the range. The syntax allows you to access
random numbers of Integers alone & not decimals. The syntax needs only to arguments
bottom integer like 10 & the top integer like 100, RANDBETWEEN(bottom, top).
21. What is the difference between a formula and a function?
Answer: Formula are user defined. However, functions are inbuilt and defined formulas and
calculations in excel system organized. An individual can add up data present in cells A1, B1,
C1 & D1 as =A1+B1+C1+D1 to get the output as sum. This can be achieved by using a sum
function =SUM(A1:C1).
22. What is rank function do?
Answer: A RANK function returns position of a given number from a list of numbers present
in the same list. Rank can be identified either by ascending or descending order. Students
with highest marks needs descending order (), similarly employees with least absent%.
23. Where can we see instant summary of a any selected range in a worksheet window?
Answer: Any selected range can show it's summary like average, sum, min, max for number
values at the bottom right side of the sheet, which is also called as status bar, for text only
data only the count of cells in the selected range will be shown at the status bar.
24. What is the difference between relative and absolute cell referencing?
Answer: When you copy a formula to another cell and it repeats, it is relative referencing. It
exists by default in MS Excel. Absolute cell reference does not change when the formula is
copied from one cell to the next.
25. What is the difference between Cut, Paste and Paste special?
Answer: All the three have the same output which is to repeat a certain value from one
position to a newly identified location. However, all the three have their own importance.
● A cut operation (shortcut Ctl + X) is used when we do not want the original value to
remain in the source location. instead, we wanted it to be moved to a new destination.
This will move the cell contents, the formulas and also renames its dependents with
the newly identified location
● A paste operation will reproduce the copied cell from its origin to a newly identified
location. This will simple repeat the entire cell content, the cell properties too. The
shortcut to paste is Ctrl + V
● A paste special is the commonly used operation when using excel. The paste special
dialog box can paste a copied data into different options like pasting only values and
not the formulas, pasting only the formats, pasting only the formulas and not the
values etc.
● The shortcut to get to the paste special dialog box is Alt + E + S + V
o Above all the first need to paste in any of the three keys is to copy. The
shortcut to copy is Ctrl + C.
26. What is a Range?
Answer: A group cells together form a range. A range can be referring to two cells A1:B1 or it
be extended to more than two cells too like A:B. In the first example we are referring to first
cell in the column a & the first cells in the column B. However, in the second example we are
selecting all the cells in the Column A & Column B.
27. What does referencing mean?
Answer: Referencing is a step to anchor values to a fixed position which helps us to repeat
formulas to many other cells without having the need to write the formula every time. This
helps an excel user to be more efficient and accurate.
28. What is the use AVERAGE v/s AVERAGEIF ?
Answer: The average function derives an arithmetic mean of any given numbers. Adds up the
sum of all the digits and divides it with the number of entries. Whereas the AVERAGEIF
specifically gets arithmetic mean for only specific values satisfying a condition.
Eg. Considering a class completed their exams, the total class size is of 30 students who took
up the exam of 5 subjects. Applying average on the total data gives as the average score for
the class. However, AVERAGEIF can split the data at a subject wise average or even student
wise average.
29. What is the use MAX v/s MAXIF ?
Answer: Max is a shorter name for MAXIMUM. The MAX function identifies the highest
number in supplied numbers. However, MAX IF finds the highest number in a series of
numbers but with a condition. Let us say we a data into columns Column A having the Months
& Column B having Temperature for the last 5 years. If we want to know the Maximum
temperature in the recent 5 years, we use MAX on the total 5 years data, if we need to know
Maximum temperature in June within the same five years, we can use MAXIF, which will
return the MAX with condition June.
30. What is the use MIN v/s MINIF?
Answer: MIN is a shorter name for Minimum. The MIN function identifies the smallest number
in supplied numbers. However, MINIF finds the least number in a series of numbers but with
a condition. Let us say we a data into columns Column A having the Months & Column B having
Temperature for the last 5 years. If we want to know the Maximum temperature in the recent
5 years, we use MIN on the total 5 years data, if we need to know MIN temperature in June
within the same five year we can use MIN, which will return the MIN with condition June.
31. What is the function Product used for?
Answer: Product function multiplies a set of number into each eg. 10 * 10 * 10. can also be
referred as =Product(10,10,10) which will have the same output. the 10 mentioned here can
be actual cell address.
32. What is the use COUNT v/s COUNTA?
Answer: As the word defines count the formula compute a series of data points to identify
‘how many’ of a given values are in the selected range. COUNT computes only numbers and
ignores blank cells. However, the COUNTA identifies both number and alphabets and ignores
blank cells and gives the output. Considering we have a data of Employees working hours for
a given day (there are some having Absent [cells are left blank]/leave[denoted by writing ‘L’
in the cell] in the same column & others having their worked hours). COUNT will tell how many
employees logged in for the day whereas COUNTA. Can be used to count employees on Leave.
33. What logical condition will you use when there are two different criteria?
Answer: Use the ‘AND” logical condition when there are two different criteria.
34. What is the Logical Function?
Answer: There are 4 logical functions – AND, OR, XOR, and NOT.
A Logical function helps in:
- Checking if a condition is true or false
- Combine two or more conditions together
[Link] is #N/A Error?
Answer: The #N/A error is most found when doing lookups, it denotes that the lookup value
is not present in the fact table. If the errors are expected we can add another subfunction to
handle, the error just add =IFERROR(YOUR function,"Value does not exist"). This will show
data for rows where data is match and for not matching cases the result will be Value does
not exist.
[Link] is #NULL Error?
Answer: The Excel #NULL error value is shown whenever the user commits a typing error,
such as incorrectly typing the range name or referring to a range name already deleted or
failing to put quotation marks for a string data in a formula. This error typically occurs mostly
because of syntax errors. For example, if the user forgets to put a comma, semicolon or
space in a formula.
10. What is #DIV/0! Error?
Answer: The #DIV/0! error occurs in Excel whenever in a formula the user tries to divide by
zero, or any number equivalent to zero.
[Link] is rolling average?
Answer: A rolling average commonly known as a moving average or running average, it is an
average calculated on the basis of subsets of data at specified intervals.
[Link] is #REF error?
Answer: The #REF! error of Excel is one of the formula errors that may occur for a few
reasons related to used cell references. The #REF! itself represents the meaning of
reference. In particular, the #REF! error is the result of invalid or non-existent references
used inside the formula(s) in the sheet.