bitm interview

Question 1

Spreadsheet

Answer 1

electronic file consisting of a grid of columns and rows that

Organizes related data
Displays results of calculations
Enables interpretation of quantitative data for decision making

Question 2

Cell

Answer 2

the intersections of columns and rows

Question 3

Cell reference and cell address

Answer 3

ref.: used in formulas to refer to a specific cell

add.: location of cells

Question 4

active cell

Answer 4

the current cell in a worksheet, found in the name box

Question 5

name box

Answer 5

displays the address(or name) of the active cell, selected chart, or selected table

used to go to cells and ranges or assign a name

Question 6

range

Answer 6

a group of adjacent or contiguous cells in a worksheet (close to each other)

Question 7

formula

Answer 7

combines cell references, arithmetic operations, values, and/or functions used in calculation

Question 8

what is a worksheet and a workbook

Answer 8

worksheet: spreadsheet that usually contains descriptive labels, numeric values, formulas, functions, and charts.

workbook: a collection of one or more related worksheets contained within a single file.

Question 9

AutoComplete

Answer 9

searches for and automatically displays any other matching text

Question 10

what does a function have to begin with to be valid

Answer 10

=

Question 11

cell style

Answer 11

impacts the appearance not the data itself

Question 12

Alignment:

Answer 12

indicates how data are positioned in the boundaries of a cell

Question 13

Wrap text:

Answer 13

word-wraps data on multiple lines within a cell

Question 14

Backstage view:

Answer 14

displays print options and the worksheet in print preview mode

Question 15

range finder

Answer 15

highlights and color codes each cell as you type its address

Question 16

Point and Click to Build a Formula:

Answer 16

Instead of typing a cell address, you point to and click the cell to enter its address in a formula.

Question 17

Excel follows PEMDAS but why

Answer 17

to control the sequence in which arithmetic operations are performed, which affects the result of the calculation.

Question 18

What is a relative reference and an example of one

Answer 18

when formulas adapt to their relative position when copied/pasted . A1

Question 19

What is an absolute reference and an example of one

Answer 19

in a formula it maintains that address when the formula is copied

meaning it remains constant
$A$1

Question 20

What is a mixed cell reference and an example of one

Answer 20

a combo, either the column letter or the row number that has the absolute reference remains fixed while the other part of the cell reference that is relative changes when the formula is copied.

Question 21

What is a 3D reference, an example of one, and what types of references can it use

Answer 21

includes the name of the worksheet and can be absolute, mixed, or relative.
Ex:
=Sheet2!$B$2

Question 21

SUM function

Answer 21

totals values in one or more cells and displays the result in the cell containing the function.

Question 22

AVERAGE function

Answer 22

calculates the arithmetic mean, or average, for the values in a range of cells.

Question 23

MEDIAN function

Answer 23

finds the numerical midpoint value, which is the value that one half of the data set is above or below.

Question 24

MIN function

Answer 24

analyzes a set of values to determine the lowest value max does the opposite

Question 25

PRODUCT function

Answer 25

multiplies all values in one or more cells and displays the results in the cell containing the function

Question 26

SUMPRODUCT function

Answer 26

used to add the total value of the products of two or more data arrays

Question 27

What is the difference between VLOOKUP and HLOOKUP formulas

Answer 27

VLOOKUP function looks for an accepted value in the left column of a specified table array, and then returns a related value located in the same row HLOOKUP function.This data must be arranged in ascending order from left to right.

Question 28

XLOOKUP

Answer 28

searches an array and returns the first value matching the specified data like vlookup XLOOKUP can also return approximate matches if no exact match is found, it can look up data on either the left or right of table array.

Question 28

syntax

Answer 28

the rules that dictate the structure and components required to perform the necessary calculations.

Question 29

PMT function

Answer 29

calculates payments for a loan with a fixed amount at a fixed periodic rate for a fixed time period. rate: interest rate per payment period nper: number of payment periods pv: present value of the loan (principal) fv: future value type: when payments are due

Question 30

IF function

Answer 30

tests specified criteria to see if it is true or false, then returns one value when a condition is met, or is true, and returns another value when the condition is not met, or is false.

Question 31

What is the difference between column, bar, and line charts

Answer 31

column: displays vertical columns where the height represents the value, used to compares values across categories bar: same as column but displays category names in an easy-to-read format line charts: display lines connecting data points to show trends over equal time periods.

Question 32

data point and data series

Answer 32

point: individual value in a cell that is plotted in a chart series: group of related data points

Question 33

What is the difference between category axis & value axis

Answer 33

Category axis: displays descriptive labels for the data points Value axis: displays incremental numbers to identify the approximate value of data points

Question 34

what is a legend

Answer 34

it identifies the color or pattern assigned to each data series and displays when a multiple series chart is created

Question 35

clustered column chart, stacked column chart and 100% stacked column chart

Answer 35

100%

Question 36

what are the different ways you can move a chart

Answer 36

cut and paste dragging it move to a chart sheet

Question 37

a pie chart

Answer 37

shows each data point as a proportion or ratio to the whole data series.

Question 38

combo chart

Question 39

What is a scatterplot chart

Answer 39

shows the relationship between two numeric variables using their X and Y coordinates.

Question 40

What is alt text used for

Answer 40

a description displayed when the pointer is moved over a chart or image

Question 41

What is a sparkline

Answer 41

A small line, column, or win/loss chart contained in a single cell

Question 42

data label

Answer 42

descriptive text that shows the exact value or name of a data point.

Question 43

what is a data table

Answer 43

organizes the results of several what-if analyses within a single table

Question 44

what if analysis

Answer 44

allows you to see how changing variables impacts calculated results

Question 45

what is the difference between one and two variable data tables

Answer 45

1 is a dynamic range that contains different values for one variable to compare how those values affect one or more calculated results. 2 enables you to use two variables, however you are restricted to only one result. creates a matrix

Question 46

goal seek

Answer 46

identifies the necessary input value to obtain a desired goal by changing ONE variable. (backsolve)

Question 47

scenario

Answer 47

a saved set of values in a worksheet allowing you to vary numbers and see potential results

Question 48

solver

Answer 48

an add in that searches for the best or optimal solution to a problem it extends the function of goal seek for more complex problems Optimization models find the highest, lowest, or exact values for one result

Question 49

solver reports

Answer 49

answer report: displays original values and solver results sensitivity report: how sensitive results are to changes in the variable cells limits report: displays impact on objective as variables are maximized or minimized

Question 50

add ins

Answer 50

programs that can be added to excel to add one file>options>add-ins>manage>go

Question 51

constraints

Answer 51

specify binding and non binding restrictions

Question 52

what is an excel function

Answer 52

a predefined computation that simplifies creating a formula that performs a complex calculation