Logical Functions: Introduction

Loading...

Do curso por PwC

Problem Solving with Excel

2727 classificações

Explore Related Videos

At Coursera, you will find the best lectures in the world. Here are some of our personalized recommendations for you

PwC

Problem Solving with Excel

2727 classificações

Curso 2 de 5 em Specialization Data Analysis and Presentation Skills: the PwC Approach

This course explores Excel as a tool for solving business problems. In this course you will learn the basic functions of excel through guided demonstration. Each week you will build on your excel skills and be provided an opportunity to practice what you’ve learned. Finally, you will have a chance to put your knowledge to work in a final project. Please note, the content in this course was developed using a Windows version of Excel 2013. This course was created by PricewaterhouseCoopers LLP with an address at 300 Madison Avenue, New York, New York, 10017.

Na lição

Logical Functions & Pivot Tables

In this module you will learn about logical functions and pivot tables. We will show you how to create and use pivot tables to solve business problems. We will give you an opportunity to practice creating and using a pivot table to solve a business problem. Finally, we will share some insight on PwC’s perspectives on the impact of Excel on your career and work.

Welcome to Week 32:06

Logical Functions: Introduction5:19

Logical Functions - COUNTIFS7:05

Logical Functions - COUNTIFS Part 22:55

Logical Functions - SUMIFS3:17

Logical Functions - SUMIFS Part 25:01

Logical Functions - IF + THEN1:36

Logical Functions - IF + AND2:50

Logical Functions - IF + OR2:19

Logical Functions - Nesting3:46

Conheça os instrutores

Alex Mannella
Alumni / Former Principal

[MUSIC]

Welcome to the logical functions module.

In this module, we will learn some simple and

very useful functions Excel offers to evaluate conditional logic.

The functions we will cover in this module are a subset of the logical functions

provided by Excel.

We will share references to where you can learn more about logical functions in

Excel as part of the materials provided with this module.

We encourage course participants to study these powerful functions independently to

master their use as they are foundational building blocks in spreadsheet analysis.

Let's review the functions we will cover in this module.

The CountIfs function allows you to count the number of occurrences of a value

in a range based on specific criteria.

It takes as parameters the range in which you wish to count values and

the criteria against which to apply to those values in order to make the count.

The SumIfs function allows you to sum values in a range

based on specific criteria.

SumIfs takes, as parameters, first the range of values which you care to sum.

Then, arrange with values you'd like to apply criteria against when summing,

and lastly criteria to apply against this range.

You'll note that you can feed any number of criteria ranges and

criteria into SumIfs, which makes it quite powerful.

The IF function allows you to compare different values or variables and

return a specified value depending on the outcome of that test.

The test must be structured as a boolean or true or false test.

If no outputs are specified, the function will return true if the test is true and

false, if the test is false.

IF requires, as parameters, firstly the logical test,

then the value if that test is true and lastly the value if that test is false.

Combining And with the If function

allows multiple logical tests in evaluating values in a data set.

It is useful when we want to do something only if all of our tests are true and

if we want to do something else if any of our tests is false.

Each function within the And function must be structured as a boolean test.

You can't simply list the values being compared.

The And function itself can be nested to run more advanced tests.

We'll talk more about If and And

in conjunction as we work through the problems of this modular.

Combining the If + Or

functions allows multiple logical tests in evaluating a data set.

This is useful when we want to do something when any of the tests is true

and something else when all of the tests are false.

Each function within the OR function must be structured as a unique test.

You can't simply list the values being compared.

And again, we'll come back to specific examples of using If and

Or during the problem sets for this module.

Lastly, combining, or nesting, multiple If functions

can be a powerful way to evaluate chains of conditional logic.

We can think of this as If a given test is true, then do this.

Otherwise, If our first test is true and

another test is true, then do something else.

Otherwise, because none of the previous tests is true and

then do this other thing.

Nested If functions can incorporate additional formulas as parameters.

We'll unlock some of the powerful uses for

nested If functions as we work through the problems in this module.

Let's begin with the spreadsheet accompanying this module.

Before we dig into the exercises associated with the module,

let's take a moment to explore the data set underlying all these problems.

You see here that we have 11 columns and just over a hundred or so

records that comprise sales records for a fictitional company

across sales channels for their various products throughout a period of time.

One important thing to note about this dataset is that it is structured formally

as an Excel table.

One thing this provides us is convenient references when referring to data

in that table.

Rather than using column letters and row numbers, we can use the name of the table,

and special syntax, to refer to the names of each column.

These special references are called structured references.

We will provide additional reading material for you, so

that you can learn more about this feature in Excel.

Structured references and named ranges in general,

significantly increased the readability of our spreadsheets.

We encourage you to incorporate these in your own spreadsheet analysis.

[MUSIC]

Explore nosso catálogo

Registre-se gratuitamente e obtenha recomendações, atualizações e ofertas personalizadas.

O Coursera proporciona acesso universal à melhor educação do mundo fazendo parcerias com as melhores universidades e organizações para oferecer cursos on-line.

© 2019 Coursera Inc. Todos os direitos reservados.

Baixar na App Store

Baixar no Google Play