Microsoft Excel - Advanced Formulas And Functions

Table of Contents

Welcome

How Advanced Is Advanced? 00:03:24

Using The Working Files 00:01:56

When Is A Formula A Function? 00:03:58

Getting The Basics Right

Sum And Count Syntax 00:06:18

Accessing Unknown Function Syntax Within Excel 00:07:58

Referencing Entire Rows/Columns 00:04:33

Function Processing Order 00:07:48

Convert Formulas To Values In A Split Second 00:04:22

Extend Your Formula Usage

Naming Cells To Improve Formula Appearance 00:06:48

3D Referencing 00:08:58

Full Absolute Referencing 00:04:37

Making Only Part Absolute Formula 00:06:23

If And If Type Functions

The If Logical Test 00:08:15

Nested If Statements 00:03:57

Using The And, Or, And Not Functions Within If 00:06:58

CountIf, SumIf, And AverageIf 00:10:11

Multiple Criteria With CountIfs, SumIfs, And AverageIfs 00:06:33

Lookup And Reference Type Functions

Looking Up Information With VLookup 00:09:10

HLookup 00:04:12

Nearest Match With VLookup 00:06:30

Nested Lookup Functions 00:04:30

Choose And Match Functions For Locating Data 00:05:11

Index And Match Functions For Retrieving Data 00:08:38

Mathematical Functions

Varying Rounding Functions 00:07:39

MRound, Ceiling, And Floor For Rounding Also 00:05:18

Int And Mod For Integer And Remainder Values 00:04:21

Generating Random Numbers And Values In Excel 00:05:14

Password Generating Workbooks 00:06:02

Display Different Number Types With Roman And Arabic 00:05:17

Statistics

The Middle Value With Median 00:05:07

Ranking Data Without Sorting 00:04:58

Large And Small For Retrieving Biggest And Smallest Values 00:04:39

A Choice Of Count Functions 00:05:39

Text Functions

Extracting Data With Left, Right And Mid 00:05:45

Locating Data With Find, Search, Exact 00:07:19

Use Trim And Clean To Remove Unwanted Gaps 00:05:49

Combine Data From Different Cells 00:06:18

Changing The Case Of Text Using Proper, Upper, And Lower 00:03:17

Replace And Substitute In Action 00:04:55

Format Number Content Within Strings With Text 00:05:53

Date And Time Functions

Using Today And Now Functions< 00:03:34

How Does Excel Deal With Dates And Time 00:05:53

What Day Of The Week Is It? 00:04:14

Difference Between Network Days And Work Day 00:06:59

DatedIf For Time Differences 00:07:12

EDate And EOMonth 00:04:57

DateValue And TimeValue Functions 00:07:43

Information And Referencing Functions

Extracting Information With Cell And Info Functions 00:08:13

Using IsBlank, IsOdd, IsEven, IsText, And IsNumber 00:04:37

IsErr, IsError, IfError, IsNA, And IfNA For Error Checking 00:08:26

The New IsFormula Function And FormulaText 00:03:42

Use Offset To Obtain Data From Related Cells 00:08:42

InDirect And Its Uses 00:07:13

Array Formulas And Functions

What Is An Array Formula? 00:08:27

Use An Array Formula To Count Unique Entries 00:05:18

Flipping Rows Or Columns With Transpose 00:03:56

Conclusion

Advanced Formula Summary 00:02:45

About Guy Vaccaro 00:01:27