Create Advanced Formulas

Question 1

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

Answer 1

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.

Question 2

AND returns TRUE

Answer 2

if ALL conditions are met

Question 3

OR returns True

Answer 3

If ANY conditions is met

Question 4

Easier to select rows for functions

Answer 4

View –> Split

Question 5

Date/Time function

Answer 5

To put a date or time together use :

DATE (month, day and year)

TIME (hour, minute, second).

Question 6

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

Answer 6

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

Question 7

NETWORKDAYS function

Answer 7

Excludes weekends

Question 8

WORKDAY function

Answer 8

Excludes weekends and holidays

Question 9

Query Editor

Answer 9

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.

Question 10

How to edit what to import from query editor?

Answer 10

Edit –> select column – remove column

Edit –> merge queries/append queries/split column

Question 11

“link” to query editor

Answer 11

Data –> connections group –> connections button

Question 12

Vlookup:

Answer 12

• 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

Question 13

Hlookup:

Answer 13

Same as VLookup but horizontal

Question 14

Payment function

Answer 14

• 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

Question 15

Index match

Answer 15

• Uses vlookup and hlookup functions simultaneously
  always click on the array (tested)
• Array = data

Question 16

Match function

Answer 16

What specific range are you looking for

Use 0 for exact match

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

Question 17

Fill down

Answer 17

Control + D

Question 18

First day in Excel

Answer 18

1/1/1900

Question 19

Acrrued interest

Answer 19

accrint()

Question 20

Now function

Answer 20

Gives date+time

Question 21

Today function

Answer 21

Gives only date

Question 22

Data consolidate

Answer 22

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

Question 23

Consolidate: Category

Answer 23

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

Question 24

Purpose of Goal Seek tool:

Answer 24

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

Question 25

Goal seek function:

Answer 25

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"

Question 26

How to see function in cell

Answer 26

F2

Question 27

Scenario tool

Answer 27

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…”

Question 28

Scenario Function

Answer 28

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

Question 29

Troubleshooting Formulas

Answer 29

Formulas → Formula Auditing File → options → formula Evaluating formulas are similar

Question 30

Evaluate Formula

Answer 30

Pulls up calculations and steps through

Question 31

Error Checking

Answer 31

Formulas --> Error Checking (in Formula Auditing)

Question 32

Name Manager

Answer 32

Control F3

Question 33

Charting: Selecting non continuously

Answer 33

Ctrl + Highlight

Question 34

Adding column into chart

Answer 34

Design --> Select Data --> Add

Question 35

Change Chart Style

Answer 35

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

Question 36

Chart: Adding trendline

Answer 36

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

Question 37

Chart: Secondary Axis:

Answer 37

Change chart type → select secondary axis

Question 38

Pivot Tables

Answer 38

Calculated Field: pivot table tools → analyze → field, items & sets → calc field Right click, group, fill out settings Right click, expand, select new field

Question 39

Pivot Table slicer

Answer 39

Analyze --> Click slicer --> Select category Remove slicer by clicking the x Basically a filter

Question 40

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

Answer 40

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

Question 41

GetPivotData

Answer 41

Press = + Select cell

Question 42

Why use secondary axis?

Answer 42

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

Question 43

How to achieve secondary axis?

Answer 43

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.