excel

AEEC350Lab4-NestedIFFunctions-SV-Spring2024.xlsx

Home >Computer Science homework help >excel

Short Answer

1.	Suppose you want to use nested IF functions to return "A" if D11 <= 50, "B" if D11 <=100, "C" if D11 <= 150, and "D" if D11 <=200. What is the minimum number of IF functions you would need, assuming you only use IF functions?
	Minimum IF functions =
2.	Suppose we want to assign annual bonuses to employees based on the following table:
			Sales
			<$100,000	>=$100,000
	Years with Company	<10	$2,000	$4,000
		>=10	$4,000	$8,000
	What is the minimum number of IF functions you would need, assuming you only use IF functions?
	Minimum IF functions =
3.	Suppose you have the following:
	Sales =			$160,000
	Years with Company =			15
	Which of the following IF functions would not work based on the scenario in question 2? Highlight all that apply.
	(a) =IF(G23<10,IF(G22<100000,F12,G12),IF(G22<100000,F13,G13))
	(b) = IF(G22<100000,IF(G23>10,F13,F12),IF(G23>10,G13,G12))
	(c) =IF(G23>=10,IF(G22>=100000,G13,F13),IF(G22>=100000,G12,F12))
	(d) =IF(G22<=100000,IF(G23<10,F12,F13),IF(G23<10,G12,G13))

Warm Ups

1.	Use nested IF functions (and only nested IF functions) in cell E6 that generates the same results as the formula in cell E5 (displayed in F5).
		0.080	0.800
		50	=AND(E4<0.11,F4>=0.91)*125+50
2.	Use nested IF functions (and only nested IF functions) in cell E13 that generates the same results as the formula in cell E12 (displayed in F12).
		0.60	0.06
		50	50+OR(E11<0.5,F11>=0.5)*125
3.	Use nested IF functions (and only nested IF functions) in cell E24 that generates the appropriate value from cells F20:G21 depending on the criteria given in E20:E21 and F19:G19 and the value in E23:F23. For example, if E23 = 100 and F23 = "B", then E24 = 25.
			E23>=100	E23<100
		F23="A"	50	75
		F23="B"	25	35
		103	B

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	3	0
	Problem 2	3	0
	Problem 3	3	0
Warm Ups	Problem 1	3	0
	Problem 2	3	0
	Problem 3	3	0
Discount Scenario 1	Seed Rebate
	logical_test for Brand = Z	0.5	0
	Associated value_if_true	0.5	0
	Associated value_if_false	0.5	0
	logical_test for Quantity >= 30,000	0.5	0
	Associated value_if_true	0.5	0
	Associated value_if_false	0.5	0
	logical_test for Seed Rebate Form = Yes	0.5	0
	Associated value_if_true	0.5	0
	Associated value_if_false	0.5	0
	Total Rebate = (Order Amount)IF()(Discount Rate)	0.5	0
	Phosphate Rebate
	AND Function: Brand = B	0.5	0
	Associated value_if_true	0.5	0
	Associated value_if_false	0.5	0
	AND Function: Quantity >= 15,000	0.5	0
	Associated value_if_true	0.5	0
	Associated value_if_false	0.5	0
	AND Function: Seed Rebate Form = Yes	0.5	0
	Associated value_if_true	0.5	0
	Associated value_if_false	0.5	0
	Total Rebate = (Order Amount)AND()(Discount Rate)	0.5	0
	Total Rebate = Seed Rebate + Phosphate Rebate	3	0
	Cell References	3	0
Discount Scenario 2	logical_test for Seed Quantity >= 30,000	0.5	0
	Associated value_if_true	0.5	0
	Associated value_if_false	0.5	0
	logical_test for Phosphate Quantity >= 20,000	0.5	0
	Associated value_if_true	0.5	0
	Associated value_if_false	0.5	0
	logical_test for + Phosphate Quantity >= 45,000	0.5	0
	Associated value_if_true	0.5	0
	Associated value_if_false	0.5	0
	Total Discounted Cost = (Seed Cost + Phosphate Cost)(1-(Discount Rate)OR())	0.5	0
	Cell References	3	0
Discount Scenario 3	logical_test for Seed Quantity >= 35,000	0.5	0
	Associated value_if_true	0.5	0
	Associated value_if_false	0.5	0
	logical_test for Phosphate Quantity >= 15,000	0.5	0
	Associated value_if_true	0.5	0
	Associated value_if_false	0.5	0
	logical_test for Seed + Phosphate Quantity >= 40,000	0.5	0
	Associated value_if_true	0.5	0
	Associated value_if_false	0.5	0
	Total Discounted Cost = (Seed Cost + Phosphate Cost)(1-(Discount Rate)OR(AND(),))	0.5	0
	Cell References	3	0
TOTAL	TOTAL	50	0