Information Systems

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/55

encourage image

There's no tags or description

Looks like no tags are added yet.

56 Terms

1
New cards

what do formulas do

calculate results from the values in other cells

2
New cards

what happens when you copy a formula, what is this called

the cell references shift by the same distance you moved it (relative reference)

3
New cards

how to lock an absolute reference

put a dollar sign before and in middle of cell “name”

4
New cards

mixed reference for column

$A1

5
New cards

mixed reference for rows

A$1

6
New cards

ranges

a group of cells, written as two corner cells joined by a colon

7
New cards

What do formats do

change how a value looks when you use them, to present numbers clearly.

8
New cards

=SUM()

totals numbers in a range

9
New cards

=IF(condition, value if true, value if false)

checks condition and then gives one result If it is true and a different result if it is false.

10
New cards

when to use IF

use when calculation depends on something ex) vat charged only if price is over 200

11
New cards

OR and AND

to test several conditions at once and return TRUE of FALSE

OR = (condition 1, condition 2); true if at lease one condition is true is false only when all are false

AND = (condition 1, condition 2) is true only if all conditions are true. It is false as soon as one is false

12
New cards

ROUND

rounds a number to a set number of decimal places and actually changes the stored value (#, decimal)

13
New cards

JOIN and CONCATENATE

join values together as text, one after another, used to build full names from a table of cells

14
New cards

VLOOKUP

searches for a value in the first column of a table and then returns the value from another column in the same row

to look up something from a code or name

15
New cards

COUNTIF

counts the cell in a range that meet one condition

=countif(range, condition)

16
New cards

COUNTIFS

counts rows that meet several conditions at the same time

how many where x and y

=countifs(range 1, condition 1, range 2, condition 2)

17
New cards

SUMIFS

adds up values only in rows that meet several conditions at once

18
New cards

SUMIF

adds up values only in rows that meet one condition

19
New cards

clustered column chart

to compare values across categories (sales per month, per region)

<p>to compare values across categories (sales per month, per region)</p>
20
New cards

stacked column

one bar per category, split into segments. use when you want to total and see what makes it up

<p>one bar per category, split into segments. use when you want to total and see what makes it up</p>
21
New cards

100% stacked column

every bar is same hight, split into shares. use it when only the proportions matter, not the size of the totals

<p>every bar is same hight, split into shares. use it when only the proportions matter, not the size of the totals</p>
22
New cards

3-d column

column chart w/ depth

<p>column chart w/ depth</p>
23
New cards

stock charts

shows open, high, low, and close prices(plus volume)

<p>shows open, high, low, and close prices(plus volume)</p>
24
New cards

bubble chart

A bubble chart expands a standard scatter plot by plotting three variables: the X-axis (horizontal value), the Y-axis (vertical value), and the bubble size (third dimension)

<p>A bubble chart expands a standard scatter plot by plotting three variables: the <strong>X-axis</strong> (horizontal value), the <strong>Y-axis</strong> (vertical value), and the <strong>bubble size</strong> (third dimension)</p>
25
New cards

radar chart

several measurements arranged around a center, like a spider web. Use it to compare a profile against a target

<p>several measurements arranged around a center, like a spider web. Use it to compare a profile against a target</p>
26
New cards

map chart

colors countries or regions by valye

27
New cards

waterfall

starting value to ending value (bridge rev to profit)

28
New cards

funnel chart

shows stages that are shrinking w/ a drop off b/w them

use for sales or conversion pipelines

29
New cards

line

a value changing over time; use it for trends

30
New cards

pie

shows parts of one whole at one moment eg. mkt share

31
New cards

bar

like column but horizontal; use for long category names

32
New cards

what are conditional formats for

to color or style cells automatically depending on their value, following a rule

w/ large tables that are hard to analyze by eye

33
New cards

importing data

brings data from a text (“flat”) file into excel without retyping it

34
New cards

naming cells and ranges

gives a cell or range a word as its name, so you can use the name in formulas instead of the address

35
New cards

define name

a meaningful, descriptive label assigned to a cell, a range of cells, a constant value, or a formula

use when referencing other sheets, in complicated forumals, using constand values, or creating dynamic formulas

36
New cards

create from selection

if labels already sit next to cells

37
New cards

apply names

If formulas already exsist

38
New cards

paste names

writes out a list of all names and ranges they refer to

39
New cards

DATE(yr,mth,day)

use when defining parts of the date are in a diff cell

40
New cards

TODAY()

gives todays date from the computer


use for days overdue, days until deadline ages

41
New cards

NOW()

gives todays date + current time

42
New cards

WEEKDAY(Date, [type])

gives days of the week as number

43
New cards

DAYS360(start;end)

counts days b/w two dates using a 360-day year

44
New cards

YEAR(date), MONTH(DATE), Day(DATE)

pull out one part of a date

45
New cards

EDATE(start;months)

gives same day a # of months earlier or later

46
New cards

EOMONTH(start;months)

gives the last day of the month that many months away (month end payments)

47
New cards

WORKDAY(start; # of working days; [holidats])

calculates a future or past date by adding or subtracting a specified number of working days while automatically skipping weekends (Saturdays and Sundays) and optional holidays

delivery date, due date


48
New cards

NETWORKDAYS(start;end;[holidays])

calculates the number of whole working days between a start date and an end date, automatically excluding Saturdays and Sundays ex payroll, days work

49
New cards

DATEIF(start;end;”unit”)

alculates the difference between two dates in years, months, or days using the syntax

(“d”), months (“m”) or years (“y”)

50
New cards

what does goalseek do

performs reverse calculations to find an unknown input value required to reach a specific target result in a formula “what value do I need to reach a target”


loans, break even, budgeting

51
New cards

data table

recalculates results for a whole list of input values at once (sensitivity analysis). It works with one input and one results (what happens to the result if this input takes each of these values)

52
New cards

scenario manager

lets you create, save, and switch between multiple sets of input values to see how they affect your formula results

53
New cards

pivot tables

turns long list into a summary table of totals, averages, or counts, arraged however you want. to summarize by category

54
New cards

filter

shows only rows that meet your criteria and hide rests data doesnt chage

55
New cards

sort

reorders rows by one or more criteria, called levels ex) providence then within province

56
New cards

outline

groups rows or columns so they can be collapsed and expanded