Excel Homework


ENGR 1500

Excel Homework

ENGR 1500 - Intro to Eng. and Tech.

Problem 1

A quality control staff in a company that makes resistors is trying to check the actual resistance of a 1,000

W resistor and she has measured the voltage and current of a that resistor and recorded the results in Table 1.

Table 1: Recorded values for Problem 1

a) Plot the results using Excel by putting voltage values on y axis and current on x axis.

b) At each step, calculate the resistance by dividing voltage by current.

c) Find the average of the resistances you calculated in previous step.

d) Find the percentage error for each step using |Calculated Value−Claimed Value|

Claimed Value × 100%. Note: |x| denotes the

absolute value of x, and it is defined as |x| = x if x > 0 and |x| = −x if x < 0.

e) Find the maximum error and determine the tolerance color.

Problem 2

Create an Excel worksheet that calculates the resistance based on the color bands specified by the user. To do this,

first create a look-up table with the first column listing the colors and the second column determining numbers

associated with each color (see Figure 1). Then, in another row, ask the user to enter the colors on the resistor

(assume a five band resistor and ignore the tolerance). See Figure 2 (the green numbers are calculated by Excel)

Then, using the look-up table, find the values of each color. Finally, write a formula that calculates the resistance

based on these values. The formula should be in this format:

Resistance = ( Color 1 × 100 + Color 2 × 10 + Color 3

) × 10Color 4 [Ω]

The result for the colors provided in Figure 2 should be 64900.

Page 2

ENGR 1500 - Intro to Eng. and Tech.

Figure 1: Look-Up Table

Figure 2: Color Values

Page 3