Essay on IF Functions working with Logical Functions

Submitted By vaqas-amin
Words: 989
Pages: 4
Open Document
Excel for Office & Home

IF Functions - working with Logical Functions
Overview
=IF()
Nested IF Statements
=AND()
=OR()
=COUNTIF()
=COUNTIFS()
=SUMIF()
=SUMIFS()

Overview When analysing data in Excel, we often want to pick some data and eliminate unwanted data
(temporarily or permanently). This can be done in a number of ways:
● Using the functions =IF() | =AND() | =OR() | =COUNTIF()
● Using filters
● Using PivotTables
For the purpose of this topic we are going to concentrate on the functions.

=IF()
The =IF() function is used to highlight one set of data over another.
The format of the function is:
● IF some condition / criteria (logical test) is TRUE, then return this value, otherwise return that value For example if cell A2 contained 1, and cell B2 contained 6; we could set up an if statement to detect if consecutive cells contained the same value.
● In cell C2 we could enter =IF(A2=B2,)
● In English this reads "If the value in cell A2 equals the value in cell B2, then return 0 otherwise return 'FALSE'"

1

Excel for Office & Home

● Alternatively we could enter =IF(A2=B2,1,0)
● In English this reads "If the value in cell A2 equals the value in cell B2, then return 1 otherwise return 0"
● This is more useful because we could then use the =SUM() function to total the amount of scenarios where this condition is true.

Nested IF Statements
The =IF() function can be used to compare more than two scenarios. Let's say you wanted to create a RAG (Red, Amber, Green) report which is based on the following criteria:
1. If the due date is more than a month ahead ­ Status Green
2. If the due date is within a month ­ Status Amber
3. If the due date is passed ­ Status Red If Column A contains the due date and column B contains the formula for the status. We can utilise another function called =Today() which reads the current date from the computer.
Therefore, the three criteria are:
1. If the date in A2 is less than today then the status is Red
2. If the date in A2 is more than month ahead of today then the status is Green
3. Otherwise the status must be Amber To put this in an =IF() statement:

2

Excel for Office & Home
=IF(A2<TODAY(),"RED",IF(A2>TODAY()+30,"GREEN","AMBER"))

There are several things to note here:
1. The "=" sign is only used once at the beginning of the formula.
2. The =IF() function can hold the following outcomes:
● No values ­ then returns 0 or FALSE
● One value ­ then returns that value or FALSE
● Two values ­ then returns the first value if true or the second value if false
● One value and another IF() statement ­ returns the first value if true or evaluates the second IF() statement If you wanted you could add conditional formatting to add some more polish to this report.

=AND()
The =AND() function is used when both or all conditions are true. This function has limited use because it only returns TRUE or FALSE. It will return TRUE only if all conditions within the function are true otherwise it will return FALSE.

=OR()
The =OR() function is used when any conditions are true. This function has limited use because it only returns TRUE or FALSE. It will return TRUE if any condition within the function is true otherwise it will return FALSE if they are all false.

=COUNTIF()
The =COUNTIF() function is useful because it will only count elements where they are equal to a certain value.

3

Excel for Office & Home

In the scenario above we have a list of almost 300 marital status'. The =COUNTIF() function has been used in cell B2 to only count if the value in column A is equal to "S", in C2 it only counts if the value is "M". In cell D2 the number of blanks have been
Show More

Related Documents: Essay on IF Functions working with Logical Functions

Explain Free Cash Flow

1. Explain ‘Free cash flow’. Comment on its significance in business valuation. 2. Illustrate the ‘VLookup’ function. Explain its importance. 3. Explain ‘Pivot tables’ in excel & state their value in multi-dimensional analysis. 4. Explain ‘Options’ trading. State their significance in modern day trading. 5. Comment on the business models of private equity funds. 6. Comment on valuation of ‘distressed companies’. 7. Illustrate with an example any three capital…

Words 1669 - Pages 7

Discrete: Logic and Quantifiers Essay

DISCRETE MATHEMATICS AND ITS APPLICATIONS by Kenneth H. Rosen Chapter 1 -- The Foundations: Logic and Proof, Sets, and Functions 1.1 Logic A proposition is a statement that is either true or false, but not both. ( p ) Propositional logic is the area of logic that deals with propositions. A truth table displays the relationships between the truth values of propositions. Logical operators: Negation ( ( p, not p ) Conjunction ( p ( q, p and q ) Disjunction ( p ( q, p or q ) Exclusive…

Words 2406 - Pages 10

Roles and Functions of Law Essays

Role and Functions of Law Paper Ronald Sander LAW/421 10/8/12 Tiffany Brady Let’s start with how a law is defined. A law is defined as rules of conduct and can be carried out by intimidation or penalty if they are broken. Basically laws are principles and guidelines that assist in governing behavior, managing disagreements, and dealing with unlawful actions. In businesses and society the laws play are very important role. With laws they control the behavior of the society and help it…

Words 489 - Pages 2

Research Paper On C Programming

#include 4 1.1.3 Function main() 4 1.1.4 Strings, Escape Sequences and a first look at printf () 4 1.1.5 Simple Print Exercises 6 1.2 Identifiers 7 1.3 Variable Types and Declarations 8 1.3.1 Integers 8 1.3.2 Floating-point numbers 8 1.3.3 Characters 8 1.4 Manipulating Data: Basic Operations 10 1.4.1 The Assignment Operator 10 1.4.2 Arithmetic Operators 10 1.4.3 Symbolic Constants 11 1.4.4 Mathematical Functions 12 1.5 Input and Output 14 1.5.1 The printf() function revisited 14 1…

Words 973 - Pages 4

HR Management test questions Essay

common-law doctrine stating that employers have the right to hire, fire, demote, or promote whomever they choose, unless there is a law or a contract to the contrary. 2. List four major laws enacted since 1960 that directly impact the Human Resources function. Briefly describe each. - Family and Medical Leave Act – Allows eligible employees to take up to 12 weeks of leave for their serious health conditions or the birth or adoption of a child. . Injured employees may ask to use that leave time in…

Words 859 - Pages 4

Systems Analysis: Pseudocoding And Flowcharting

& Flowcharting 1 Flowcharting, pseudocoding, and process design The purpose of “flowcharts” is to represent graphically the logical decisions and progression of steps in the physical completion of a task. As such, flowcharts are the lowest level of decomposition. The flowchart specifies the order in which tasks are performed. As such anyone who knows about the function can recognize immediately changes to the actual performance of work. Since here the logic of the work is tested, it is here the…

Words 3144 - Pages 13

philosophy-nussbaum Essay

construction of a society. First of all, in comparison to Hobbes and Locke basic point of view on the human nature, Rawls give humans much more credits in terms of their capabilities and natural tendencies as can be understand from the list of good functions that is described in the reading. Second of all, there doesn’t seem to be any form of government mentioned in the text, meaning the contract to which Rawls is referring to, is to be decided on by the individuals of society and held among them. The…

Words 845 - Pages 4

Fair Trade Rhetoric

imagery, in which the video producers used simple text to draw a picture of starving communities in the viewers’ minds. For example; the comparison between boots and slippers denotes the difference between poor and rich people, this comparison also functions as a means of contrast between these two kinds of people. Another form of imagery is when the images of the three products are placed underneath the slippers, as a way to show how poor people…

Words 417 - Pages 2

Essay Management: Management and Good Leader

we will see further. The manager has many functions and roles and leading is an important one. We will see some about management and leadership and if a good manager can succeed without being a good leader. Despite management is a broad term, writers usually says about process when defining management. Henri Fayol defined management process in terms of five management functions: plan, organize, command, coordinate and control. These days, the functions are condensed as Schermerhorn & Corporation…

Words 956 - Pages 4

Essay on Continuity Recovery Principles

Principles of Continuity System Continuity Model • Consider your operation as a “system” of interacting components that produce services: – An urban system: city operations, agencies, services, etc. – An enterprise system: businesses, business units, functions, etc. – A societal system: people, social groups, activities, etc. • Consider the kind of response models required to address various kinds of unplanned events: – Proactive or preventative responses – Reactive and recovery responses – Combinations…

Words 1953 - Pages 8