BIS Exam 3
a dataset also called
"population"
The rules of rounding state that if the number immediately to the right of the rounding point is ______ the trailing digits are cut off. If the number immediately to the right of the rounding point is _____ the number is rounded up
0,1,2,3,4 5,6,7,8,9
if you are looking for a true value to multiple conditions use _____ function
AND
the logical functions designed specifically tor return a TRUE or FALSE response
AND and OR functions
calculate the average value where cells meet specified criteria
AVERAGEIF
optional AVERAGEIF argument
Average Range
the range of cells to average where criteria are met
Average_range
to change the chart style: 1. Select the chart 2. On the ____ tab, in the ___ group , click the style you want to use, or click the ___ button to see all of the Quick Styles available 3. To change the color scheme, on the ____ tab, in the ____ group, click the __ button and select the color scheme you want
Chart Tools Design Chart Styles More Chart Tools Design Chart Styles Change Colors
the conditions the cell must meet in order to be included in the total
Criteria
the first criteria
Criteria1
(optional) the second criteria
Criteria2
the range of cells containing the values to be evaluated against the first criteria
Criteria_range1
(optional) the range of cells containing the values to be evaluated against the second criteria
Criteria_range2
provides the bridge between the logical and physical view of the data; when users request data, this handles the details of actually locating the data
DBMS engine
database management software made up of:
DBMS engine data definition subsystem
to rename a worksheet: 1. ____ the sheet tab or ____ the worksheet tab and select ____ 2. type the new sheet name, and press_____
Double-click right-click Rename enter
this function calculates the future value of an investment
FV (future value)
to print only part of a worksheet or the entire workbook at once: 1. Click the ____ tab to open Backstage view 2. Click _____ 3. In the ____ section, the first button displays which part of the workbook will print. By default, _______ is selected. TO change the print selection, click the button and then click one of the other options. 4. If you want to ignore the defined print area, click _________ at the bottom of the list 5. Click the ____ button to print. Click ____
File Print Settings Print Active Sheets Ignore Print Area Print OK
If you want the first column to always be visible, click _____
Freeze First Column
if you wan the first row to always be visible click _____
Freeze Top Row
lets you enter a desired value (outcome) for a formula and specify an input cell that can be modified in order to reach that goal
Goal Seek
allows you to select the maximum number of pages you want the worksheet to print vertically
Height arrow
to add a worksheet to the left of the active worksheet, on the _____ tab, in the _____ group, click the ____ button arrow and select _____
Home Cells Insert Insert Sheet
this function displays a text message or a specific value that you define
IFERROR
this function allows you to evaluate multiple logical tests in a single function
IFS
returns the value at the intersection of a specified row and column in an array
INDEX
to insert a column chart: 1. Select the data you want to include in the column chart. 2. On the ___ tab, in the ____ group, click the ____ button 3. Click the chart type you want to insert into the worksheet
Insert Charts Inser Column or Bar Chart
to add a pie chart: 1. Select the data you want to include in the pie chart 2. On the __ tab, in the _____ group, click the ____ button 3. Click the chart type you want to insert into the worksheet
Insert Charts Insert Pie Chart
offers news and information on legal, public records, and business issues
LexisNexis
returns the position of a specific value in a single row or column array
MATCH
this function is used to find the middle value for a range
MEDIAN
the ____ function returns a single mode value (the first mode value it finds)
MODE.SNGL
this function calculates the number of payments available from a retirement account or due on a loan given a constant interest rate and payment amount
NPER
this function that is similar to PV, but it allows for variable payment amounts when calculating the present value of future payments
NPV
to add a new worksheet to the right of the active worksheet, click the _____ button to the _____ of the last worksheet tab
New sheet right
the typical working view in Excel; shows the aspects of the worksheet that are visible only on-screen
Normal view
Excel offers three ways to view a worksheet
Normal view Page Layout view Page Break Preview view
the total number of payments
Nper
the number of digits to the right of the decimal to round to
Num digits
the number of formula to round
Number
each of the three rounding functions takes two arguments
Number Num digits
if you are looking for a true value to any one of multiple conditions use ____ function
OR
when you create a new blank workbook in Excel 2016, the _____ theme is applied by default
Office
present value can be calculated using the ___ function when the payments are constant
PV
allows you to manipulate where page breaks occur when the worksheet is printed
Page Break Preview view
to add a header or footer to a worksheet from Page Layout view: 1. Switch to Page Layout view by clicking the ____ button on the status bar 2. The header area has three sections with the text _____ in the center section. If you do not see the ______ text, move the mouse pointer to the area just above ____ to make the header area visible. 3. The ______ tab appears.
Page Layout Add header Add header row 1 Header & Footer Tools Design
To repeat rows and columns on every printed page: 1. On the ____ tab, in the ____ group, click the _____ button 2. In the ____ dialog, on the _____ tab, click in the _____ box, and then click and drag to select the rows to repeat. You can also type the row reference(s) using the format _____
Page Layout Page Setup Print Titles Page Setup Sheet Rows to repeat at top $1:$1
shows all the worksheet elements as they will print, including page margins and headers and footers
Page Layout view
a graphic representation of a PivotTable
PivotChart
a special report view that summarizes data and calculates the intersecting totals
PivotTable
the amount of each payment
Pmt
the present value or how much the investment is worth today
Pv
two optional arguments for FV
Pv Type
two common forms of analysis tools
Query-by-example Structured query language
apply combinations of fonts, line styles, fils, and shape effects
Quick Styles
rounds the number up or down to the number of decimal places specified in the Num digits argument
ROUND
3 main rounding functions
ROUND ROUNDUP ROUNDDOWN
Ignores the rules of rounding and automatically rounds down, regardless of the value to the right of the rounding point
ROUNDDOWN
ignores the rules of rounding and automatically rounds up to the next number, regardless of the value to the right of the rounding point
ROUNDUP
Sum range must be the same size as
Range
the range of cells to evaluate against the criteria
Range
2 required arguments for AVERAGEIF
Range Criteria
the interest rate
Rate
the interest rate that would be reasonably expected if the money were invested elsewhere
Rate/discount rate
calculates standard deviation using the entire set of values as the argument
STDEV.P
calculates standard deviation for a set of values based on a sample from the population
STDEV.S
this function totals the values only where cells meet the specific criteria
SUMIF
function that allows you to multiply the corresponding cells in two or more ranges and then add the products
SUMPRODUCT
this allows you to enter a percentage to grow or shrink the worksheet when printed
Scale box
when you create a new workbook, it contains a single worksheet named
Sheet1
measures how far values in a dataset are spread out from the mean
Standard deviation
This argument is necessary only when the values to be summed are not the same as the values to be evaluated against the criteria
Sum Range
optional argument of SUMIF
Sum range
the range of cells containing the values to be summed
Sum range
shows all the Excel workbooks you have open
To book list
when this argument is empty, Excel assumes a value of 0 and calculates the result based on payments at at the end of each period
Type
the formula to calculate
Value
the payment amount both incoming (positive) and outgoing (negative)
Value
the value, text string, or formula to use if the formula in the Value argument results in an error
Value if error
allows you to select the maximum number of pages you want the worksheet to print across
Width arrow
after you have grouped sheets, the title bar now includes ______ after the file name
[Group]
measures how far the number is from 0.
absolute value of a number
query language: most common method to access database; ask a question of database to find data
analysis tools
provides tools to create data entry forms and specialized programming languages that interface with common and widely used programming languages
application generation subsystem
data now includes
audio music photographs video
example of batch processing
bank credit card billing
like column charts turned on their side; categories are displayed on the vertical axis, and the data point values are plotted along the horizontal axis
bar charts
data collected and stored over time, several hours, days, or even weeks, and the processed at once as a batch
batch processing
two main ways to process data
batch processing real time processing
to insert a row: 1. Place your cursor in a cell in the row ____ where you want the new row 2. On the ___ tab, in the ____ group, click the ____ button arrow, and select ______ 3. the new row will appear ___ the selected cell
below Home Cells Insert Insert Sheet Rows above
at the end of each accounting period, the asset's ____ is recalculated using the initial cost minus the accumulated depreciation
book value
examples of available databases include the following
business directories demographic data business statistical information tex databases web databases
You can/cannot undo the Delete Sheet command
cannot
basic logical data element; single letter, number, or special character
character
a graphic that represents numeric data visually
chart
the area that encompasses the entire chart including the plot area and optional layout elements, such as title and legend
chart area
a text box above or overlaying the chart
chart title
to delete a column: 1. place your cursor in a cell in the _____ you want to delete 2. On the ____ tab, in the ____ group, click the _____ button arrow and select _____ 3. the column will be deleted, and columns to the ____ of the deleted column will shift _____
column Home Cells Delete Delete Sheet Columns right left
work best with data that are organized into rows and columns like a table
column charts
data markers can be ____,____,____,____ or other visual elements
columns bars pie pieces lines
enormous database that organizations develop to cover particular subjects
commercial database
tables can be related or connected to other tables by
common key fields
usually stored on a central database server and managed by a database administrator; users throughout can access the database through its network
company
facts or observations about people, places, things, and events
data
helps manage the overall database; including maintaining security, providing disaster recovery support, and monitoring the overall performance of database operations
data administration subsystem
defines the logical structure of the database by using a data dictionary or schema
data definition subsystem
contains a description of the structure of data in the database
data dictionary
accurate updating of files
data integrity
maintaining data is also known as
data maintenance
provides tools for maintaining and analyzing data
data manipulation subsystem
used to search data warehouses
data mining
in a chart, the values selected in the worksheet
data points
many organizations have multiple files on the same subject or person; for example records for the same customer may appear in different files across multiple departments
data redundancy
related data points, usually in the same row or column, are grouped into a _____
data series
provide a quick what-f analysis of the effects of changing one or two variables within a formula
data tables
storing in a database for special use
data warehouse
collection of integrated data; logically related files and records
database
integrated collection of logically related tables
database
determine the most efficient ways to organize and access a company's data; responsible for database security and backing up the system
database administrators
highly trained computer specialists to interact with the data administrations subsystem; larger organizations typically employ these; additional duties include determining processing rights or determining which people have access to what kinds of data in the database
database administrators (DBA)
software that enables users to create, modify, and gain access to data
database management systems (DBMS)
when a business purchases a significant asset such as equipment or computer software, generally accepted accounting principles (GAAP) state that the expense must be spread over the asset's useful lifetime
depreciation
depreciation ove the life of an asset is figured in a
depreciation schedule
offers business information, as well as technical and scientific information
dialog information systems
real-time processing made possible by ____ and _____ devices
disk packs direct access storage
makes it possible to access the data quickly
disk packs and direct access storage devices
the data may be stored in multiple locations; it is made accessible through a variety of communications networks
distributed database
provides world news and information on business, investments, and stocks
dow jones interactive publishing
to unhide a row or column: 1. select the rows or columns on _____ of the row or column you want to unhide 2. On the ____ tab, in the ____ group, click the ____ button 3. Point to ______, and click _____ or _____
either side Home Cells Format Hide & Unhide Unhide Rows Unhide Columns
group of related characters
field
if your worksheet has many rows of data, you may want to __ the data to show only rows that meet criteria you specify
fileter
databases are valuable so protection is necessary by
firewalls
To specify an exact column width for multiple columns: 1. Select the columns you want to modify. click the column selector for the ____ column, press and hold _____, and click the column selector for the _____ column. 2. On the ___ tab, in the ____ group, click the ____ button. 3. select ______ 4. Enter the value you want in the _____ dialog. 5. click ____
first Shift last Home Cells Format Column Width Column Width ok
to group worksheets: 1. Click the _____ tab 2. Hold down ___ and click the tab of the ____ worksheet you want included in the group. If you want to select noncontiguous worksheets, press ____ instead, and then click each sheet tab 3. Make the change you want to the sheet. 4. To ungroup, click ______ tab that is not part of the group
first worksheet Shift last Ctrl any sheet
rows in excel are automatically sized to fit the ______
font size
text that appears at the bottom of every page, just above the bottom margin
footer
data points are transformed into these in a chart
graphic data markers
the lines that appear on the worksheet defining the rows and columns
gridlines
If you have multiple worksheets with the same structure, you can make changes to all of them at the same time by _____ them
grouping
text that appears at the top of every page, just below the stop margin
header
the numbers at the left of rows and the letters at the top of columns
headings
When you ____ row or column, the data still remain in your workbook, but they are no longer displayed on-screen and are not part of the printed workbook
hide
four kinds of databases whether large or small, limited or widely accessible
individual company distributed commercial
collection of integrated files primarily used by just one person
individual database
commercial databases also called
information utilities; data banks
also known as primary key; unique identifier to help locate data based on specific requests
key field
tables may be electronically linked via a ____ containing common data item
key field
where the width of the page is greater than the height
landscape orientation
tells you which data point or data series is represented by each color in the chart
legend
feature a line connecting each data point--showing the movement of values over time; work best when data trends over time are important
line chart
data is organized into groups or categories
logic view
you can move worksheets around in a workbook, rearranging them into the most _______
logical order
focuses on the meaning and content of the data; need users and most computer professionals are concerned with this view
logical view
the average of the absolute differences between each value and the overall mean
mean absolute deviation (MAD)
the middle value of a set of values
median
relational database common for
microcomputers
the value that appears most often in a group of values
mode
Pmt usually expressed as a______ number
negative
when one function is nested within the other function
nested formulas/function
sheets that are not next to each other
noncontiguous worksheets
real-time processing also known as
online processing
referes to the direction the worksheet prints; doesn't affect the way the worksheet looks on your computer screen
orientation
individual database also called a
personal computer database
actual format and location; usually only very specialized computer professionals are concerned with this view
physical
two ways to view data
physical view logical view
represent data as parts of a whole; work best when you want to evaluate values as they relate to a total value
pie charts
the area where the data series are plotted
plot area
the default print orientation
portrait orientation
when the height of the page is greater than the width
portrait orientation
in financial terms,_____ is the value today of a series of future payments
present value
prints all the sheets in the workbook
print entire workbook
prints the table only (available only if the current selection is within a defined table)
print selected table
prints only the selected cells in the active worksheet, overriding any print area definitions in the active worksheet
print selection
the result of multiplying two or more numbers
product
two required arguments for SUMIF
range criteria
three required functions of FV
rate Nper Pmt
happens "now" instead of "later"; occurs when data is processed at the same time the transaction occurs
real-time processing
collection of related fields; a collection of attributes
record
a more flexible type of database where there are no access paths down a hierarchy
relational database
To insert a column: 1. Place your cursor in a cell in the column to the ____ of where you want the new column 2. On the _____ tab, in the ____ group, click the ____ button arrow and select ______ 3. The new column will appear to the ___ of the selected cell
right Home Cells Insert Insert Sheet Columns left
You can also modify column widths manually: 1. Move your mouse over the ____ 2. the cursor will change to a ____ 3. Click and ___ until the column is the size you want, and then ____ the mouse button
right column border shape drag release
to make the column automatically fit the contents: 1. Move your mouse over the _____ 2. The curse will change to a _____ 3. Double-click the _____ to adjust the column width to fit the content exactly
right column boundary shape right column border
the rounding functions in Excel are used specifically to control the number of digits to the ____
right of the decimal point
refers to adjusting a number up or down to make it more appropriate to the context in which it is being used
rounding
To delete a row: 1. place your cursor in a cell in the ____ you want to delete 2. On the ____ tab, in the ___ group, click the ____ button arrow and select _____ 3. the row will be deleted and the rows ___ it will shift ____
row Home Cells Delete Delete Sheet Rows below up
to hide a row or column: 1. Select any cell in the _______ you want to hide 2. On the ____ tab, in the ____ group, click the ____ button 3. Point to ____ and click ___ or _____
row or column Home Cells Format Hide & Unhide Hide Rows Hide Columns
to insert a bar chart: _____
same as a column chart
each worksheet in the workbook has its own _____ settings. On the _____ tab, in the _____ group , select the options you want
scale Page Layout Scale to Fit
advantages to having databases: _____ information between departments an organization _____ limited access _____ decrease of unnecessary duplication of data when several departments use the same database of information ______ reduced likelihood of inconsistent, incomplete, or inaccurate data
sharing security less data redundancy increased data integrity
to move or copy a worksheet to another workbook or to a new workbook: 1. Right-click the ____ tab, and select ______ to open that dialog 2. In the Move or Copy dialog, expand the _____ list at the top of the dialog. Click the workbook you want. To move or copy the sheet to a new blank workbook, select _______ 3. The list of sheets in the _______ will update to show the sheets available in the workbook you selected. Click the name of the workbook you want to move the elected sheet before. If you want to move the sheet to the end of the workbook select _____ in the Before sheet box. 4. If you want to create a copy of the selected sheet, instead of moving the original, click the _____ check box. 5. Click _____
sheet Move or Copy To book (new book) Before sheet box (move to end) Create a copy OK
most valuable feature of relational database
simplicity
a visual representation fo filtering options
slicer
rearranges the rows in your worksheet by the data i a column or columns
sorting
represent a series of data values as an individual graphic within a single cell
sparklines
Excel uses the ____ value, not the ___ value, in calculations
stored displayed
most common query language
structured query language (SQL)
In excel, you can define a series of adjacent cells as a ____
table
collection of related records
table
relational database data stored in a collection of columns and rows called a ___ or ____
table relation
Excel offeres an easy way to automatically set columns to the width to best fit _____
the data in the column
The absolute value of a positive number is the same as
the number
a unified color, font, and effects scheme
theme
to freeze part of the worksheet: 1. Arrange the worksheet so the row you want to be visible is the _____ row or the column you want it the first column visible at the ___ 2. On the ____ tab, in the ____ group, click the _____ button
top left View Window Freeze Panes
to add a line chart: 1. Select the data you want to include in the line chart. Be sure to include both ____ and the related cells that represent _____ segments 2. On the __ tab, in the ___ group, click the ____ button 3. Click the chart type you want to insert into the worksheet 4. If Excel does not display the time categories along the _____, switch the data series and the categories. On the ____ tab, in the ____ group, click the ___ button
values time Insert Charts Insert Line Chart x axis Chart Tools Design Data Switch Row/Column
numbers can be rounded up or down to the nearest ____ or the nearest factor of ___,___,____, or any limit
whole number ten hundred thousand
The absolute value of a negative number is the number _________
with the negative sign
to delete a worksheet: 1. Select the sheet you want to delete by clicking the ______ tab 2. On the ____ tab, in the ____ group, click the _____ button arrow, and select _______ 3. IF you try to delete a sheet that contains data, Excel will display a warning that the sheet may contain data and ask if you are sure you want to permanently remove it from your workbook. Click the ____ button to continue and delete the worksheet
worksheet Home Cells Delete Delete Sheet Delete
to move a worksheet within a workbook: 1. click the ______ tab and _____ the mouse button 2. drage the mouse cursor to the position where you want to move the sheet, and _______. Excel places a ______ to let you know where the sheet will be placed
worksheet hold down release the mouse button small black triangle