際際滷

際際滷Share a Scribd company logo
Nested Functions

How do we evaluate a complex
formula?
What are DeMorgans Laws?

CS 105 Spring 2011

#1
Grade Example
 Suppose there is
2% extra credit
(column AK)
 Solution 1: create
column AN for
Points + EC

Y

Y

=IF(AK2=Y,AI2*102%,AI2)

Then use
 Solution 2:

=VLOOKUP(AN2,AL2:AM12,TRUE)

Y

=VLOOKUP( IF(AK2=Y,AI2*102%,AI2) ,AL2:AM12,TRUE)

CS 105 Spring 2011

#2
Nested Functions
 In order to evaluate a complex formula like
=VLOOKUP(IF(AK2=Y,AI2*102%,AI2),AL2:AM12,TRUE)

start by evaluating the inner parts
 The overall formula can be evaluated once the
values of the inner sub-expressions are known

 A more complex example:
IF(ISERROR(VLOOKUP(value,range,col)),No,Yes)
3

2

1

CS 105 Spring 2011

#3
DeMorgans Laws
 Consider the following formulas:
a) NOT(AND(X, Y))
b) AND(NOT(X), NOT(Y))
c) NOT(OR(X, Y))
d) OR(NOT(X), NOT(Y))
 The formulas (a) and (d) are equivalent, and so are
formulas (b) and (c)
 We can verify this with truth tables:
X

Y

AND(X,Y)

NOT(AND(X,Y))

NOT(X)

NOT(Y)

OR(NOT(X), NOT(Y))

0

0

0

1

1

1

1

0

1

0

1

1

0

1

1

0

0

1

0

1

1

1

1

1

0

0

0

0

CS 105 Spring 2011

#4
Example: College Admissions
 A college grants admissions based on three criteria:
SAT score (S), Letter score (L), and References (R)
S

L

R

Decision

1300

AND

8.5

AND

8.25

Strong accept

1200

AND

8.0

AND

7.80

Accept

1200

OR

7.5

OR

7.00

Reject



Manual



ELSE

 Which of these formulas is correct?
IF(AND(S>=1300,L>=8.5,R>=8.25),
"Strong accept",
IF(AND(S>=1200, L>=8, R>=7.8),
"Accept",
IF(OR(S<1200, L<7.5, R<7),
"Reject", "Manual")))

IF(OR(S<1200,L<7.5,R<7),
"Reject",
IF(OR(L<8,R<7.8),"Manual",
IF(OR(S<1300,L<8.5,R<8.25),
"Accept",
"Strong accept")))

CS 105 Spring 2011

#5
Testing Your Knowledge

 How do we evaluate a complex
formula?
 What are DeMorgans Laws?

CS 105 Spring 2011

#6
Ad

Recommended

03 excel1
03 excel1
dd
Research Peptides Website Reviews & Where to Buy Peptides
Research Peptides Website Reviews & Where to Buy Peptides
researchpeptides
Primeros pasos de power point
Alejitha Perdomo
Digital Gravity
Digital Gravity
SebSigloch
Arial9
Arial9
08070807
English whale
English whale
Milan Vukovi
C弍亳 P仄仆亳亳
C弍亳 P仄仆亳亳
Milan Vukovi
Math120 20122
Math120 20122
Tolga Cakar
Serbian football in 1930
Serbian football in 1930
Milan Vukovi
01
01
dd
CWTS
CWTS
Julienne Tan
Forecasting the future
Forecasting the future
Ryan Bautista
excel ppt
excel ppt
dd
Space
Space
Milan Vukovi
IMAGING OF INTRACRANIAL PRIMARY NON-NEOPLASTIC CYSTS
IMAGING OF INTRACRANIAL PRIMARY NON-NEOPLASTIC CYSTS
Ameen Rageh
Testicular Torsion
Testicular Torsion
Ameen Rageh
CYSTIC FIBROSIS
CYSTIC FIBROSIS
Ameen Rageh
Applicationofstack by Ali F.RAshid
Applicationofstack by Ali F.RAshid
ali rashid
Excel 2003 formulas
Excel 2003 formulas
jiya1
EXCEL ON POWER POINT.ppt fresh student learning
EXCEL ON POWER POINT.ppt fresh student learning
UNIVERSEDEVINE
Excel formulas
Excel formulas
devangimehta
Excel IF, IFs.ppt
Excel IF, IFs.ppt
EdwinAdeolaOluwasina1
Excel.fns frmls
Excel.fns frmls
rowenick
Iipm chapter 1
Iipm chapter 1
iipmff2
Iipm chapter 1
Iipm chapter 1
iipmff2
advance spreadsheet skils LESSON 4 - PPT.pptx
advance spreadsheet skils LESSON 4 - PPT.pptx
MaeOlbis
Excel
Excel
duttactg
Excel
Excel
duttactg
Excel CH 02 Working with Formulas Functions.ppt
Excel CH 02 Working with Formulas Functions.ppt
EmmanuelOppongAfriyi1
03 Logical functions.pdf
03 Logical functions.pdf
RizwanAli988729

More Related Content

Viewers also liked (10)

Serbian football in 1930
Serbian football in 1930
Milan Vukovi
01
01
dd
CWTS
CWTS
Julienne Tan
Forecasting the future
Forecasting the future
Ryan Bautista
excel ppt
excel ppt
dd
Space
Space
Milan Vukovi
IMAGING OF INTRACRANIAL PRIMARY NON-NEOPLASTIC CYSTS
IMAGING OF INTRACRANIAL PRIMARY NON-NEOPLASTIC CYSTS
Ameen Rageh
Testicular Torsion
Testicular Torsion
Ameen Rageh
CYSTIC FIBROSIS
CYSTIC FIBROSIS
Ameen Rageh
Applicationofstack by Ali F.RAshid
Applicationofstack by Ali F.RAshid
ali rashid
Serbian football in 1930
Serbian football in 1930
Milan Vukovi
01
01
dd
Forecasting the future
Forecasting the future
Ryan Bautista
excel ppt
excel ppt
dd
IMAGING OF INTRACRANIAL PRIMARY NON-NEOPLASTIC CYSTS
IMAGING OF INTRACRANIAL PRIMARY NON-NEOPLASTIC CYSTS
Ameen Rageh
Testicular Torsion
Testicular Torsion
Ameen Rageh
CYSTIC FIBROSIS
CYSTIC FIBROSIS
Ameen Rageh
Applicationofstack by Ali F.RAshid
Applicationofstack by Ali F.RAshid
ali rashid

Similar to 06 excel4 (18)

Excel 2003 formulas
Excel 2003 formulas
jiya1
EXCEL ON POWER POINT.ppt fresh student learning
EXCEL ON POWER POINT.ppt fresh student learning
UNIVERSEDEVINE
Excel formulas
Excel formulas
devangimehta
Excel IF, IFs.ppt
Excel IF, IFs.ppt
EdwinAdeolaOluwasina1
Excel.fns frmls
Excel.fns frmls
rowenick
Iipm chapter 1
Iipm chapter 1
iipmff2
Iipm chapter 1
Iipm chapter 1
iipmff2
advance spreadsheet skils LESSON 4 - PPT.pptx
advance spreadsheet skils LESSON 4 - PPT.pptx
MaeOlbis
Excel
Excel
duttactg
Excel
Excel
duttactg
Excel CH 02 Working with Formulas Functions.ppt
Excel CH 02 Working with Formulas Functions.ppt
EmmanuelOppongAfriyi1
03 Logical functions.pdf
03 Logical functions.pdf
RizwanAli988729
If and nested i fs
If and nested i fs
PaulCPLD
042-MIS105-L07.ppt
042-MIS105-L07.ppt
MuqaddasKhalid5
10 Excel Formulas that will help you in any Job
10 Excel Formulas that will help you in any Job
Hitesh Biyani
management
management
mumlilian
EXCEL FUNCTIONS and Advanced Formula Combinations Excel Mastery Series Unleas...
EXCEL FUNCTIONS and Advanced Formula Combinations Excel Mastery Series Unleas...
myrum7mongacd
Dbms quiz 01 solution
Dbms quiz 01 solution
mushiabro
Excel 2003 formulas
Excel 2003 formulas
jiya1
EXCEL ON POWER POINT.ppt fresh student learning
EXCEL ON POWER POINT.ppt fresh student learning
UNIVERSEDEVINE
Excel.fns frmls
Excel.fns frmls
rowenick
Iipm chapter 1
Iipm chapter 1
iipmff2
Iipm chapter 1
Iipm chapter 1
iipmff2
advance spreadsheet skils LESSON 4 - PPT.pptx
advance spreadsheet skils LESSON 4 - PPT.pptx
MaeOlbis
Excel CH 02 Working with Formulas Functions.ppt
Excel CH 02 Working with Formulas Functions.ppt
EmmanuelOppongAfriyi1
03 Logical functions.pdf
03 Logical functions.pdf
RizwanAli988729
If and nested i fs
If and nested i fs
PaulCPLD
10 Excel Formulas that will help you in any Job
10 Excel Formulas that will help you in any Job
Hitesh Biyani
management
management
mumlilian
EXCEL FUNCTIONS and Advanced Formula Combinations Excel Mastery Series Unleas...
EXCEL FUNCTIONS and Advanced Formula Combinations Excel Mastery Series Unleas...
myrum7mongacd
Dbms quiz 01 solution
Dbms quiz 01 solution
mushiabro
Ad

Recently uploaded (20)

Wax Moon, Richmond, VA. Terrence McPherson
Wax Moon, Richmond, VA. Terrence McPherson
TerrenceMcPherson1
LAZY SUNDAY QUIZ "A GENERAL QUIZ" JUNE 2025 SMC QUIZ CLUB, SILCHAR MEDICAL CO...
LAZY SUNDAY QUIZ "A GENERAL QUIZ" JUNE 2025 SMC QUIZ CLUB, SILCHAR MEDICAL CO...
Ultimatewinner0342
Birnagar High School Platinum Jubilee Quiz.pptx
Birnagar High School Platinum Jubilee Quiz.pptx
Sourav Kr Podder
ECONOMICS, DISASTER MANAGEMENT, ROAD SAFETY - STUDY MATERIAL [10TH]
ECONOMICS, DISASTER MANAGEMENT, ROAD SAFETY - STUDY MATERIAL [10TH]
SHERAZ AHMAD LONE
Publishing Your Memoir with Brooke Warner
Publishing Your Memoir with Brooke Warner
Brooke Warner
Paper 108 | Thoreaus Influence on Gandhi: The Evolution of Civil Disobedience
Paper 108 | Thoreaus Influence on Gandhi: The Evolution of Civil Disobedience
Rajdeep Bavaliya
What is FIle and explanation of text files.pptx
What is FIle and explanation of text files.pptx
Ramakrishna Reddy Bijjam
University of Ghana Cracks Down on Misconduct: Over 100 Students Sanctioned
University of Ghana Cracks Down on Misconduct: Over 100 Students Sanctioned
Kweku Zurek
Basic English for Communication - Dr Hj Euis Eti Rohaeti Mpd
Basic English for Communication - Dr Hj Euis Eti Rohaeti Mpd
Restu Bias Primandhika
Overview of Employee in Odoo 18 - Odoo 際際滷s
Overview of Employee in Odoo 18 - Odoo 際際滷s
Celine George
Pests of Maize: An comprehensive overview.pptx
Pests of Maize: An comprehensive overview.pptx
Arshad Shaikh
LDM Recording Presents Yogi Goddess by LDMMIA
LDM Recording Presents Yogi Goddess by LDMMIA
LDM & Mia eStudios
How to Manage Inventory Movement in Odoo 18 POS
How to Manage Inventory Movement in Odoo 18 POS
Celine George
How to Manage Multi Language for Invoice in Odoo 18
How to Manage Multi Language for Invoice in Odoo 18
Celine George
Plate Tectonic Boundaries and Continental Drift Theory
Plate Tectonic Boundaries and Continental Drift Theory
Marie
Revista digital preescolar en transformaci坦n
Revista digital preescolar en transformaci坦n
guerragallardo26
Non-Communicable Diseases and National Health Programs Unit 10 | B.Sc Nursi...
Non-Communicable Diseases and National Health Programs Unit 10 | B.Sc Nursi...
RAKESH SAJJAN
Sustainable Innovation with Immersive Learning
Sustainable Innovation with Immersive Learning
Leonel Morgado
The Man In The Back Exceptional Delaware.pdf
The Man In The Back Exceptional Delaware.pdf
dennisongomezk
Battle of Bookworms 2025 - U25 Literature Quiz by Pragya
Battle of Bookworms 2025 - U25 Literature Quiz by Pragya
Pragya - UEM Kolkata Quiz Club
Wax Moon, Richmond, VA. Terrence McPherson
Wax Moon, Richmond, VA. Terrence McPherson
TerrenceMcPherson1
LAZY SUNDAY QUIZ "A GENERAL QUIZ" JUNE 2025 SMC QUIZ CLUB, SILCHAR MEDICAL CO...
LAZY SUNDAY QUIZ "A GENERAL QUIZ" JUNE 2025 SMC QUIZ CLUB, SILCHAR MEDICAL CO...
Ultimatewinner0342
Birnagar High School Platinum Jubilee Quiz.pptx
Birnagar High School Platinum Jubilee Quiz.pptx
Sourav Kr Podder
ECONOMICS, DISASTER MANAGEMENT, ROAD SAFETY - STUDY MATERIAL [10TH]
ECONOMICS, DISASTER MANAGEMENT, ROAD SAFETY - STUDY MATERIAL [10TH]
SHERAZ AHMAD LONE
Publishing Your Memoir with Brooke Warner
Publishing Your Memoir with Brooke Warner
Brooke Warner
Paper 108 | Thoreaus Influence on Gandhi: The Evolution of Civil Disobedience
Paper 108 | Thoreaus Influence on Gandhi: The Evolution of Civil Disobedience
Rajdeep Bavaliya
What is FIle and explanation of text files.pptx
What is FIle and explanation of text files.pptx
Ramakrishna Reddy Bijjam
University of Ghana Cracks Down on Misconduct: Over 100 Students Sanctioned
University of Ghana Cracks Down on Misconduct: Over 100 Students Sanctioned
Kweku Zurek
Basic English for Communication - Dr Hj Euis Eti Rohaeti Mpd
Basic English for Communication - Dr Hj Euis Eti Rohaeti Mpd
Restu Bias Primandhika
Overview of Employee in Odoo 18 - Odoo 際際滷s
Overview of Employee in Odoo 18 - Odoo 際際滷s
Celine George
Pests of Maize: An comprehensive overview.pptx
Pests of Maize: An comprehensive overview.pptx
Arshad Shaikh
LDM Recording Presents Yogi Goddess by LDMMIA
LDM Recording Presents Yogi Goddess by LDMMIA
LDM & Mia eStudios
How to Manage Inventory Movement in Odoo 18 POS
How to Manage Inventory Movement in Odoo 18 POS
Celine George
How to Manage Multi Language for Invoice in Odoo 18
How to Manage Multi Language for Invoice in Odoo 18
Celine George
Plate Tectonic Boundaries and Continental Drift Theory
Plate Tectonic Boundaries and Continental Drift Theory
Marie
Revista digital preescolar en transformaci坦n
Revista digital preescolar en transformaci坦n
guerragallardo26
Non-Communicable Diseases and National Health Programs Unit 10 | B.Sc Nursi...
Non-Communicable Diseases and National Health Programs Unit 10 | B.Sc Nursi...
RAKESH SAJJAN
Sustainable Innovation with Immersive Learning
Sustainable Innovation with Immersive Learning
Leonel Morgado
The Man In The Back Exceptional Delaware.pdf
The Man In The Back Exceptional Delaware.pdf
dennisongomezk
Battle of Bookworms 2025 - U25 Literature Quiz by Pragya
Battle of Bookworms 2025 - U25 Literature Quiz by Pragya
Pragya - UEM Kolkata Quiz Club
Ad

06 excel4

  • 1. Nested Functions How do we evaluate a complex formula? What are DeMorgans Laws? CS 105 Spring 2011 #1
  • 2. Grade Example Suppose there is 2% extra credit (column AK) Solution 1: create column AN for Points + EC Y Y =IF(AK2=Y,AI2*102%,AI2) Then use Solution 2: =VLOOKUP(AN2,AL2:AM12,TRUE) Y =VLOOKUP( IF(AK2=Y,AI2*102%,AI2) ,AL2:AM12,TRUE) CS 105 Spring 2011 #2
  • 3. Nested Functions In order to evaluate a complex formula like =VLOOKUP(IF(AK2=Y,AI2*102%,AI2),AL2:AM12,TRUE) start by evaluating the inner parts The overall formula can be evaluated once the values of the inner sub-expressions are known A more complex example: IF(ISERROR(VLOOKUP(value,range,col)),No,Yes) 3 2 1 CS 105 Spring 2011 #3
  • 4. DeMorgans Laws Consider the following formulas: a) NOT(AND(X, Y)) b) AND(NOT(X), NOT(Y)) c) NOT(OR(X, Y)) d) OR(NOT(X), NOT(Y)) The formulas (a) and (d) are equivalent, and so are formulas (b) and (c) We can verify this with truth tables: X Y AND(X,Y) NOT(AND(X,Y)) NOT(X) NOT(Y) OR(NOT(X), NOT(Y)) 0 0 0 1 1 1 1 0 1 0 1 1 0 1 1 0 0 1 0 1 1 1 1 1 0 0 0 0 CS 105 Spring 2011 #4
  • 5. Example: College Admissions A college grants admissions based on three criteria: SAT score (S), Letter score (L), and References (R) S L R Decision 1300 AND 8.5 AND 8.25 Strong accept 1200 AND 8.0 AND 7.80 Accept 1200 OR 7.5 OR 7.00 Reject Manual ELSE Which of these formulas is correct? IF(AND(S>=1300,L>=8.5,R>=8.25), "Strong accept", IF(AND(S>=1200, L>=8, R>=7.8), "Accept", IF(OR(S<1200, L<7.5, R<7), "Reject", "Manual"))) IF(OR(S<1200,L<7.5,R<7), "Reject", IF(OR(L<8,R<7.8),"Manual", IF(OR(S<1300,L<8.5,R<8.25), "Accept", "Strong accept"))) CS 105 Spring 2011 #5
  • 6. Testing Your Knowledge How do we evaluate a complex formula? What are DeMorgans Laws? CS 105 Spring 2011 #6