Creating and expanding the use of nested IFs

Qtry
List Price
From To

price1 1.00 500 $ 3.00

price2 501 1,200 $ 2.70
price3 1,201 2,000 $ 2.30
price4 2,001 So on… $ 2.00

Order Quantity Per Unit Cost Cost

750 0.00
499 0.00
1,450 0.00
2,500 0.00
 logical operators

S.No SalesRep Sales Salary Promotion Incentives

1 Umer 55,000 28,000 Promot it 5,500.00

2 Bilal 48,268 19,000 Promot it 2,413.40
3 Zaid 46,103 17,000 Promot it 2,305.15
4 Anas 68,579 29,000 Promot it 6,857.90
5 Talha 35,000 27,000 try again 700.00
6 Abdullah 50,863 28,000 Promot it 5,086.30
7 Mansoor 68,590 17,000 Promot it 6,859.00
8 Muhammad 67,549 20,000 Promot it 6,754.90
9 Irfan 61,364 20,000 Promot it 6,136.40
10 Alee 68,785 30,000 Promot it 6,878.50
11 Aasim 63,490 20,000 Promot it 6,349.00
12 Usto 56,300 22,000 Promot it 5,630.00
13 Tasawar 45,445 23,000 Promot it 2,272.25
14 Tanvir 50,501 19,000 Promot it 5,050.10
15 Rameez 69,033 29,000 Promot it 6,903.30
16 Imran 67,406 30,000 Promot it 6,740.60
17 Hamid 58,063 29,000 Promot it 5,806.30
18 Salman 56,379 25,000 Promot it 5,637.90
19 Kamran 67,573 23,000 Promot it 6,757.30
20 Sameer 47,584 27,000 Promot it 2,379.20
21 Subaan 64,949 19,000 Promot it 6,494.90
22 Dua 39,784 19,000 try again 795.68
23 Romesa 55,278 30,000 Promot it 5,527.80
24 Kinza 62,166 16,000 Promot it 6,216.60
25 Sonia 67,789 26,000 Promot it 6,778.90

Total incentive 128,831.38

 40,000
50000 0.1
40000 0.05
20000 0.02

Incentive No bonus Calculate the com

SalesRep as per the

Commission
1
2
3

Commission
1
2
3

got promotion 0
total 25
% age of people who got promotion 0.00%
 Calculate the commission of every

SalesRep as per the following criterion.

Commission Cirteria ONE

>40000 <=50000 2,000
>50000 <=60000 4,000
>60000 so on 6,000

Commission Cirteria TWO

>40000 <=50000 5%
>50000 <=60000 6%
>60000 so on 7%

datalytics consultant registered

grades
c 68
c 60

try again 46 90 A plus

try again 45 70 kiborr

try again 45 50 c
c 68 25 try again
A plus 94
kiborr 88
A plus 94
c 58
c 69
sales data

ghulam mustafa age

 PASSING MARKS 20

Nicolo
Adams
Reagan
Mustafa

86
IF LOGIC

TRUE FALSE
hire him reject him
Excel Quiz Pass/ Fail Grading
95 PASS A+ 90
75 PASS A 75
65 PASS Fail 50
35 PASS Fail

marks grades
85 A+
70 A
Fail
excellent
good
Fine
Very bad
AND

FIRST YEAR SECOND YEAR AND OR IF(AND)

500 800 1 1 GIVE ADMISSION
500 25 0 1 NO ADMISSION
25 500 0 1 NO ADMISSION
12 0 0 NO ADMISSION

What Does It Do?

This function tests two or more conditions to see if they are all true.
It can be used to test that a series of numbers meet certain conditions.
It can be used to test that a number or a date falls between an upper and lower limit.
Normally the AND() function would be used in conjunction with a function such as =IF().

Syntax
=AND(Test1,Test2)
Note that there can be up to 255 possible tests.

Formatting
When used by itself it will show TRUE or FALSE.

Example 1
The following example shows a list of examination results.
The teacher wants to find the pupils who scored above average in all three exams.
The =AND() function has been used to test that each score is above the average.
The result of TRUE is shown for pupils who have scored above average in all three exams.

Name Maths English Physics

Alan 80 75 85
Bob 50 30 40
Carol 60 70 50
David 90 85 95
Eric 20 30 Absent
Fred 40 60 80
Gail 10 90 80
Harry 80 70 60
Ian 30 10 20
Janice 10 20 30

Averages 47 54 60
=AVERAGE(C29:C38)
 if(or)
give admission =AND(C4>=100,D4>=100)
give admission =AND(C5>=100,D5>=100)
give admission =AND(C6>=100,D6>=100)
NO Admission =AND(D7>=1,D7<=52)

Passed
1
0
0
1
0
0
0
1
0
0
=AND(C38>=AVERAGE($C$29:$C$38),D38>=AVER
29:$C$38),D38>=AVERAGE($D$29:$D$38),E38>=AVERAGE($E$29:$E$38))
OR

Payment
Order No. Cost
Type
AB001 1,000 Cash
AB002 1,000 Visa
AB003 2,000 Cheque
AB004 5,000 Delta

What Does It Do?

This function tests two or more conditions to see if any of them are true.
It can be used to test that at least one of a series of numbers meets certain conditions.
Normally the OR() function would be used in conjunction with a function such as =IF().

Syntax
=OR(Test1,Test2)
Note that there can be up to 255 possible tests.

Formatting
When used by itself it will show TRUE or FALSE.

Example
The following table shows a list of orders taken by a company.
A handling charge of $5 is made on all orders paid by Visa or Delta cards.
The =OR() function has been used to determine whether the charge needs to be applied.

Payment
Order No. Cost
Type
AB001 1,000 Cash
AB002 1,000 Visa
AB003 2,000 Cheque
AB004 5,000 Delta
Handling Charge

0 =IF(OR(E4="Visa",E4="Delta"),5,0)
5 =IF(OR(E5="Visa",E5="Delta"),5,0)
0 =IF(OR(E6="Visa",E6="Delta"),5,0)
5 =IF(OR(E7="Visa",E7="Delta"),5,0)

Handling Charge

0 =IF(OR(E27="Visa",E27="Delta"),5,0)
5
0
5
sprintzeal
1
2
3