AND and Or functions

Lab3-ANDandORFunctions_Spring2024.xlsx

Home >Business & Finance homework help >Marketing homework help >AND and Or functions

Short Answer

		1.	Complete the following statement by selecting the correct option below: The OR function evaluates to TRUE when
			a. All of the logical values are TRUE.
			b. At least one of the logical values are TRUE.
			c. None of the logical values are TRUE.
		2.	Complete the following statement by selecting the correct option below: The AND function evaluates to TRUE when
			a. All of the logical values are TRUE.
			b. At least one of the logical values are TRUE.
			c. None of the logical values are TRUE.
		3.	Suppose I have the following formula: 2500-OR(A1>=1000,B2="A")*2500. In words, what is this formula doing? Explain.
		4.	Suppose I have the following formula: 2500-AND(A2>=100,B2>=200)*2500. In words, what is this formula doing?

Warm Ups

1.	Use the OR function in cell E6 to indicate whether the value in cell E5 is 185 or more, the value in cell F5 is 50 or less, or the value in cell G5 is "Dog".
		150	72	Cat
2.	Use the AND function in cell E13 to indicate whether the value in cell E12 is "Cat", the value in cell F12 is 0.5 or less, and the value in cell G12 is 0.85 or more.
		Dog	0.205	0.167
3.	Use the AND and OR functions in cell E20 to indicate whether one of the following is true: (a) The value in E19 is "Dog", or (b) Both the value in F19 and the value in G19 are greater than 0.3.
		Cat	0.063	0.005
4.	Use the AND and OR functions in cell E27 to indicate whether both of the following are true: (a) The value in E26 is "Cat", and (b) The value in F26 is greater than 0.5 or the value in G26 is 0.3 or less.
		Cat	0.615	0.522

Discount Scenario 1

Table 1. Seed Discount

If Brand =

…and Quantity >=

30,000

Seed Discount Rate =

Table 2. Phosphate Discount

If Brand =

...and Quantity >=

15,000

Phosphate Discount Rate =

Table 3. Order Information

Order Information

Seed

Phosphate

Total Rebate

Customer ID

Location

Seed Rebate Form

Phosphate Rebate Form

Quantity

Brand

Order Cost

Total Rebate

Quantity

Brand

Order Cost

Total Rebate

T276

Texas

34,700

$14,227

15,600

$7,644

T132

Texas

Yes

33,700

$13,143

18,900

$9,072

N703

New Mexico

12,400

$4,960

7,300

$3,577

A448

Arizona

Yes

24,800

$9,672

13,400

$6,298

T151

Texas

33,000

$13,200

19,100

$9,359

T175

Texas

Yes

28,300

$11,037

15,300

$7,497

T573

Texas

Yes

46,900

$18,291

21,100

$9,917

N946

New Mexico

19,800

$7,722

9,300

$4,557

A495

Arizona

Yes

35,800

$13,962

20,000

$9,600

A950

Arizona

Yes

13,700

$5,343

7,700

$3,773

N709

New Mexico

29,700

$12,177

12,800

$6,272

N929

New Mexico

8,400

$3,360

3,600

$1,692

N203

New Mexico

40,900

$16,360

22,100

$10,829

T381

Texas

7,200

$2,952

3,000

$1,470

N137

New Mexico

Yes

35,500

$14,200

18,800

$8,836

N938

New Mexico

11,600

$4,640

4,900

$2,401

A312

Arizona

Yes

9,200

$3,588

5,000

$2,400

T162

Texas

31,400

$12,560

13,500

$6,615

T721

Texas

43,900

$17,999

23,300

$11,417

T205

Texas

11,300

$4,520

6,400

$3,136

T953

Texas

29,400

$11,760

15,600

$7,644

A819

Arizona

33,000

$13,200

16,500

$7,755

T208

Texas

Yes

45,900

$17,901

23,000

$11,040

A692

Arizona

Yes

10,100

$4,141

6,100

$2,867

A733

Arizona

49,400

$19,760

23,700

$11,613

T986

Texas

Yes

21,500

$8,385

10,300

$5,047

A278

Arizona

Yes

28,900

$11,271

11,800

$5,782

N300

New Mexico

3,800

$1,520

1,700

$816

T849

Texas

23,000

$8,970

13,100

$6,419

N833

New Mexico

11,700

$4,563

6,100

$2,867

A441

Arizona

22,900

$9,160

13,100

$6,419

T231

Texas

21,200

$8,480

12,300

$6,027

T943

Texas

Yes

26,300

$10,520

11,600

$5,452

A429

Arizona

48,000

$19,680

19,200

$9,024

N192

New Mexico

Yes

40,000

$16,400

17,600

$8,272

A947

Arizona

23,400

$9,126

9,400

$4,606

Discount Scenario 2

Table 1. Discount Information
If seed quantity >=			30,000
…or phosphate quantity >=			20,000
…or seed quantity + phosphate quantity >=			45,000
Discount Rate =			6%
Table 2. Order Information
Order Information		Seed			Phosphate			Total Discounted Cost
Customer ID	Location	Quantity	Brand	Order Cost	Quantity	Brand	Order Cost
T276	Texas	34,700	Y	$14,227	15,600	A	$7,644
T132	Texas	33,700	Z	$13,143	18,900	C	$9,072
N703	New Mexico	12,400	X	$4,960	7,300	A	$3,577
A448	Arizona	24,800	Z	$9,672	13,400	B	$6,298
T151	Texas	33,000	X	$13,200	19,100	A	$9,359
T175	Texas	28,300	Z	$11,037	15,300	A	$7,497
T573	Texas	46,900	Z	$18,291	21,100	B	$9,917
N946	New Mexico	19,800	Z	$7,722	9,300	A	$4,557
A495	Arizona	35,800	Z	$13,962	20,000	B	$9,600
A950	Arizona	13,700	Z	$5,343	7,700	A	$3,773
N709	New Mexico	29,700	Y	$12,177	12,800	A	$6,272
N929	New Mexico	8,400	X	$3,360	3,600	B	$1,692
N203	New Mexico	40,900	X	$16,360	22,100	A	$10,829
T381	Texas	7,200	Y	$2,952	3,000	A	$1,470
N137	New Mexico	35,500	X	$14,200	18,800	B	$8,836
N938	New Mexico	11,600	X	$4,640	4,900	A	$2,401
A312	Arizona	9,200	Z	$3,588	5,000	C	$2,400
T162	Texas	31,400	X	$12,560	13,500	A	$6,615
T721	Texas	43,900	Y	$17,999	23,300	A	$11,417
T205	Texas	11,300	X	$4,520	6,400	A	$3,136
T953	Texas	29,400	X	$11,760	15,600	A	$7,644
A819	Arizona	33,000	X	$13,200	16,500	B	$7,755
T208	Texas	45,900	Z	$17,901	23,000	C	$11,040
A692	Arizona	10,100	Y	$4,141	6,100	B	$2,867
A733	Arizona	49,400	X	$19,760	23,700	A	$11,613
T986	Texas	21,500	Z	$8,385	10,300	A	$5,047
A278	Arizona	28,900	Z	$11,271	11,800	A	$5,782
N300	New Mexico	3,800	X	$1,520	1,700	C	$816
T849	Texas	23,000	Z	$8,970	13,100	A	$6,419
N833	New Mexico	11,700	Z	$4,563	6,100	B	$2,867
A441	Arizona	22,900	X	$9,160	13,100	A	$6,419
T231	Texas	21,200	X	$8,480	12,300	A	$6,027
T943	Texas	26,300	X	$10,520	11,600	B	$5,452
A429	Arizona	48,000	Y	$19,680	19,200	B	$9,024
N192	New Mexico	40,000	Y	$16,400	17,600	B	$8,272
A947	Arizona	23,400	Z	$9,126	9,400	A	$4,606

Discount Scenario 3

Table 1. Discount Information
If seed quantity >=			35,000	and phosphate quantity >=		15,000
…or seed quantity + phosphate quantity >=			40,000
Discount Rate =			6%
Table 2. Order Information
Order Information		Seed			Phosphate			Total Discounted Cost
Customer ID	Location	Quantity	Brand	Order Cost	Quantity	Brand	Order Cost
T276	Texas	34,700	Y	$14,227	15,600	A	$7,644
T132	Texas	33,700	Z	$13,143	18,900	C	$9,072
N703	New Mexico	12,400	X	$4,960	7,300	A	$3,577
A448	Arizona	24,800	Z	$9,672	13,400	B	$6,298
T151	Texas	33,000	X	$13,200	19,100	A	$9,359
T175	Texas	28,300	Z	$11,037	15,300	A	$7,497
T573	Texas	46,900	Z	$18,291	21,100	B	$9,917
N946	New Mexico	19,800	Z	$7,722	9,300	A	$4,557
A495	Arizona	35,800	Z	$13,962	20,000	B	$9,600
A950	Arizona	13,700	Z	$5,343	7,700	A	$3,773
N709	New Mexico	29,700	Y	$12,177	12,800	A	$6,272
N929	New Mexico	8,400	X	$3,360	3,600	B	$1,692
N203	New Mexico	40,900	X	$16,360	22,100	A	$10,829
T381	Texas	7,200	Y	$2,952	3,000	A	$1,470
N137	New Mexico	35,500	X	$14,200	18,800	B	$8,836
N938	New Mexico	11,600	X	$4,640	4,900	A	$2,401
A312	Arizona	9,200	Z	$3,588	5,000	C	$2,400
T162	Texas	31,400	X	$12,560	13,500	A	$6,615
T721	Texas	43,900	Y	$17,999	23,300	A	$11,417
T205	Texas	11,300	X	$4,520	6,400	A	$3,136
T953	Texas	29,400	X	$11,760	15,600	A	$7,644
A819	Arizona	33,000	X	$13,200	16,500	B	$7,755
T208	Texas	45,900	Z	$17,901	23,000	C	$11,040
A692	Arizona	10,100	Y	$4,141	6,100	B	$2,867
A733	Arizona	49,400	X	$19,760	23,700	A	$11,613
T986	Texas	21,500	Z	$8,385	10,300	A	$5,047
A278	Arizona	28,900	Z	$11,271	11,800	A	$5,782
N300	New Mexico	3,800	X	$1,520	1,700	C	$816
T849	Texas	23,000	Z	$8,970	13,100	A	$6,419
N833	New Mexico	11,700	Z	$4,563	6,100	B	$2,867
A441	Arizona	22,900	X	$9,160	13,100	A	$6,419
T231	Texas	21,200	X	$8,480	12,300	A	$6,027
T943	Texas	26,300	X	$10,520	11,600	B	$5,452
A429	Arizona	48,000	Y	$19,680	19,200	B	$9,024
N192	New Mexico	40,000	Y	$16,400	17,600	B	$8,272
A947	Arizona	23,400	Z	$9,126	9,400	A	$4,606

Points

Worksheet	Component	Points Possible	Points Earned
Short Answer	Problem 1	1.5	0
	Problem 2	1.5	0
	Problem 3	1.5	0
	Problem 4	1.5	0
Warm Ups	Problem 1	1.5	0
	Problem 2	1.5	0
	Problem 3	1.5	0
	Problem 4	1.5	0
Discount Scenario 1	Seed Rebate
	AND Function: Brand = Z	2	0
	AND Function: Quantity >= 30,000	2	0
	AND Function: Seed Rebate Form = Yes	2	0
	Total Rebate = (Order Amount)AND()(Discount Rate)	2	0
	Phosphate Rebate
	AND Function: Brand = B	2	0
	AND Function: Quantity >= 15,000	2	0
	AND Function: Seed Rebate Form = Yes	2	0
	Total Rebate = (Order Amount)AND()(Discount Rate)	2	0
	Total Rebate = Seed Rebate + Phosphate Rebate	1	0
	Cell References	1	0
Discount Scenario 2	OR Function: Seed Quantity >= 30,000	2	0
	OR Function: Phosphate Quantity >= 20,000	2	0
	OR Function: Seed + Phosphate Quantity >= 45,000	2	0
	Total Discounted Cost = (Seed Cost + Phosphate Cost)(1-(Discount Rate)OR())	2	0
	Cell References	1	0
Discount Scenario 3	AND Function: Seed Quantity >= 35,000	2	0
	AND Function: Phosphate Quantity >= 15,000	2	0
	OR Function: AND Function	2	0
	OR Function: Seed + Phosphate Quantity >= 40,000	2	0
	Total Discounted Cost = (Seed Cost + Phosphate Cost)(1-(Discount Rate)OR(AND(),))	2	0
	Cell References	1	0
TOTAL	TOTAL	50	0