Create Advanced Formulas Flashcards

1
Q

All “IFS” (as opposed to IF) functions are:

A

For AND conditions

Ex. “Provide total sales for all businesses in the ‘west’ whose sales are more than $100,000” would be an example of a SUMIFS. Both the condition of “west” and “more than $100,000” – written as “>100000” in an argument – would need to be met, and if they are, SUM the sales.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

AND returns TRUE

A

if ALL conditions are met

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

OR returns True

A

If ANY conditions is met

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Easier to select rows for functions

A

View –> Split

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Date/Time function

A

To put a date or time together use :

DATE (month, day and year)

TIME (hour, minute, second).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

To pull out a component of a date or time, or to serialize:

A

MONTH, DAY, YEAR, WEEKDAY, HOUR, MINUTE, SECOND.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

NETWORKDAYS function

A

Excludes weekends

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

WORKDAY function

A

Excludes weekends and holidays

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Query Editor

A
  1. Data
  2. New query
  3. From file
  4. Workbook
  5. Sheet 1
  6. Load/load to or edit

Load brings in all, edit allows choices.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How to edit what to import from query editor?

A

Edit –> select column – remove column

Edit –> merge queries/append queries/split column

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

“link” to query editor

A

Data –> connections group –> connections button

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Vlookup:

A
  • Don’t use named ranges on this exam unless told to do so (must use F4)
  • Don’t include titles in table arrow
    3. Named range: Ctrl + F3
    4. True uses lowest # in rnage
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Hlookup:

A

Same as VLookup but horizontal

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Payment function

A
  • Remember to divide APR by 12 if there are 12 monthly payments
    Length of Loan in Years * 12 if there are monthly payments
  • FV - cash balance after payment is made
  • Type - beginning of the month, type is = to 1
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Index match

A
  • Uses vlookup and hlookup functions simultaneously
    always click on the array (tested)
  • Array = data
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Match function

A

What specific range are you looking for

Use 0 for exact match

Match is dynamic; do not hard code so you can autofill

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Fill down

A

Control + D

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

First day in Excel

A

1/1/1900

19
Q

Acrrued interest

A

accrint()

20
Q

Now function

A

Gives date+time

21
Q

Today function

A

Gives only date

22
Q

Data consolidate

A

Position –> all in same position on all worksheets

  • Make sure all labels needed are on summary sheet

Data –> consolidate –> select first reference, then all remainder, then where you want labels

23
Q

Consolidate: Category

A

Data –> Consolidate –> adjust all columns –> Check headings

24
Q

Purpose of Goal Seek tool:

A

Asking Excel to find a solution for you in an output cell, based on changing inputs

25
Q

Goal seek function:

A
  1. Check for Analysis toolpak in Data tab
  2. If not there, add Analysis toolpak
  3. “Set cell”
  4. Formula Cell; “To Value”
  5. Goal
  6. “Change cell”
26
Q

How to see function in cell

A

F2

27
Q

Scenario tool

A

Scenario allows you to create different “scenarios” by changing input cells – this way you don’t need to manually change the data on the worksheet every time you say “What if…”

28
Q

Scenario Function

A
  1. Data tab
  2. What-if analysis
  3. Scenario manager
  4. input changes
  5. Run
29
Q

Troubleshooting Formulas

A

Formulas → Formula Auditing

File → options → formula

Evaluating formulas are similar

30
Q

Evaluate Formula

A

Pulls up calculations and steps through

31
Q

Error Checking

A

Formulas –> Error Checking (in Formula Auditing)

32
Q

Name Manager

A

Control F3

33
Q

Charting: Selecting non continuously

A

Ctrl + Highlight

34
Q

Adding column into chart

A

Design –> Select Data –> Add

35
Q

Change Chart Style

A

Chart Tools –> Design –> hover over the bottom arrow –> select chart style

36
Q

Chart: Adding trendline

A

Select data point → right click → select “add trendline”

37
Q

Chart: Secondary Axis:

A

Change chart type → select secondary axis

38
Q

Pivot Tables

A

Calculated Field: pivot table tools → analyze → field, items & sets → calc field

Right click, group, fill out settings

Right click, expand, select new field

39
Q

Pivot Table slicer

A

Analyze –> Click slicer –> Select category

Remove slicer by clicking the x

Basically a filter

40
Q

Create a calculation in the pivot table (e.g., 10% decrease

A

Analyze –> Fields, Items, & Sets –> Input Formula

41
Q

GetPivotData

A

Press = + Select cell

42
Q

Why use secondary axis?

A

When you have data ranges that use different number formats, such as whole numbers versus percentages (always between 0-1), or when the ranges represented are extremely different (thousands versus millions), using a secondary axis can help clarify your data

43
Q

How to achieve secondary axis?

A
  1. If the chart is not created, select the data, and click the Combo Charts button in the Charts group
  2. If the chart is created, select the chart and use Change Chart Type in the design tab and click the All Charts tab, combo will be listed there.
  3. Select primary and secondary types and axis in the bottom of the dialog box.