excel/ engr

USD ENGR 101 Fall 2018  

1

Homework 3: More Practice with Excel Due: Thursday September 27, 2018 at the start of class

(To be done individually, each student must create their own Excel file.)

Submission Guidelines: All of your problems should be completed in a single Excel workbook named ExcelHW101f18_lastname.xlsx. You will have at least one worksheet within the workbook for each problem. Please upload the Excel file on Blackboard. Problem 1 A group of electrical engineers working on the new 5G next generation mobile networks are trying to account for additional loss of the received signal power due to the atmosphere through testing over a fixed distance. They have measured data over the frequency range between 50- 70 GHz for standard atmosphere.

Frequency  (GHz) 

Atmospheric  Loss Factor 

(dB) 

50  0.48 

52  0.52 

54  0.54 

56  0.55 

58  0.61 

60  0.67 

62  0.74 

64  0.79 

66  0.82 

68  0.85 

70  0.89 

a) Create an Excel graph that shows the atmospheric loss factor as a function of the frequency.

 Clearly label the graph and both axes and add a trendline with equation.  Your graph should not include a legend or gridlines.  The data should be represented by markers and connected by straight lines.  The axes should be set to include only the range of frequencies where data was

collected. 

b) Use your graph to estimate these values – the estimates should be provided on the worksheet for this problem.

i. the expected atmospheric loss for 57 GHz signals ii. the frequency that would correspond to a 0.7 dB loss factor

USD ENGR 101 Fall 2018  

2

Problem 2 Neglecting air resistance, the horizontal distance, d, travelled by a projectile fired into the air at an angle, θ, is given by the formula:

 

 22 sin cosV

d g

Create a worksheet that computes d (in meters) for a selected initial velocity V (m/s) and firing angle (stated in degrees).

 V and θ will be entered by the user into specific cells.  You may assume that g = 9.8 m/sec2.  The formula that calculates d should use absolute addressing.  The data entry cells and the resulting distance should be clearly labeled.

Check yourself: When V = 150 m/sec and θ = 25, d = 1,759 meters.   Problem 3      Antennas are used to increase the power of a signal being transmitted or received. Antennas are needed for almost all wireless communications. For mobile applications, like cell phones, antennas have to be a reasonable size to fit in the phone and be carried around by a human. Engineers can design antennas to have the right diameter for the device and the communication situation. Antenna gain, the factor the antenna is able to increase the signal by, depends upon a variety of factors, including size of the antenna and the characteristics of signal. This equation computes the gain, G, of an antenna:

2 D

G  

      

,

where G is antenna gain (no units), D is the antenna diameter (m), and is the wavelength of the signal (m)

Because gain relies upon signal wavelength, the necessary antenna sizes or diameters are often chosen in terms of wavelength. The frequency f of the signal determines the wavelength, , based upon this relationship: c f  where c, the speed of light, is 3ꞏ108 m/s. Since the speed of light isn’t varying, we can use the

c f  relation to solve for wavelength based upon frequency: c

f  

USD ENGR 101 Fall 2018  

3

Signals that have a frequency of 60 GHz, for example, will have a wavelength 0.005 c

f    m.

This is why a signal is at 60 GHz is commonly called a “5 millimeter wave”. The gain can also be expressed in decibels (dB) using this relation: 1010 log ( )dBG G  .

a) Create a worksheet “Problem 3a” that calculates the antenna gains for the signal frequency of 60 GHz over a range of antenna sizes.

 Have your worksheet determine wavelength,  , for a signal frequency of 60 GHz.

 Produce a table of gains for antenna diameters that range from 2

 up to 5 . For

60 GHz, the range would then be 0.0025 m up to 0.025 m, and must be a column of related formulas. [Do not specify these diameters numerically, use copying and or filling, and some absolute addressing.]

.

 The table should then calculate both the gain G and GdB across this range of diameter values. The resulting worksheet would look something like this:

Antenna Gain Based Upon Antenna Diameter Frequency (Hz) 6.00E+10 Wavelength (m) 5.00E-03

Antenna Diameter

(m) Antenna

Gain Gain (dB)

0.0025 2.47 3.9 0.0050 9.87 9.9 0.0075 22.21 13.5 0.0100 39.48 16.0 0.0125 61.69 17.9 0.0150 88.83 19.5 0.0175 120.90 20.8 0.0200 157.91 22.0 0.0225 199.86 23.0 0.0250 246.74 23.9

USD ENGR 101 Fall 2018  

4

b) Copy your results from Problem 3a into a new worksheet (tab) “Problem 3bcd”

within the same workbook (file). Modify this table so that it will calculate antenna gains for any signal frequency specified by the user.

 The calculations should use the Range Name feature of Excel and will need to use the frequency input by the user to determine wavelength. These will then determine the range of diameters to calculate gain values for.

c) Use your sheet from Problem 3bcd to calculate antenna gains for 1575.42 MHz. (This is the frequency that your GPS uses.)

 Create a well-formatted table in Excel presenting the results of those calculations.

 This final table showing resulting GPS antenna gains should be formatted similarly to the one shown in Table 3c below. –This table is formatted using color. You may wish to view this file electronically if you don’t have a color printout.

Table 3c –Example Initial Rows for Problem 3c

Antenna Gain Based Upon Antenna Diameter

User input Frequency (Hz) 1.575E+09 Wavelength (m) 0.1905

Antenna Diameter

(m)

Antenna Gain

Gain (dB)

0.0952 2.47 3.9

0.1905 9.87 9.9

0.2857 22.21 13.5

d) For GPS, what is the antenna diameter you would need to achieve a Gain of 16 dB? (Indicate this answer on the worksheet.)