Exam 13: Excel Best Practices

Switch array.Consider the following worksheet that shows four buttons arranged in a switch array.Compose a VBA Macro for each button that inserts the binary row address in E2 and the binary column address in F2.

Digital to Binary to Digital Conversions.Consider the following worksheet that converts the specified decimal digit in A3 into its 4-bit binary representation in C3:F3 and the specified 4-bit binary sequence in A7:D7 into its decimal equivalent in F7. a.What is the Excel formula in E3? b.What is the Excel formula in F7? A B C D E F 1 decimal to binary 2 decimal 3 8 1 0 0 0 4 5 binary to decimal 6 decimal 7 1 1 1 0 14

Modulo-K counter.Consider the following worksheet that forms a modulo-K counter with the K value specified in A5.The clear value in E4 equals 1 when the counter Q values are equal to the K value in A5.The VBA Macro Input_Counter assigned to the arrow increments the input count in A2 and clears the input count when Clear equals 1. a.What is the Excel formula in C2? b.What is the Excel formula in D2? c.What is the Excel formula in E2? d.What is the Excel formula in F2? e.What is the Excel formula in E4? f.Compose the VBA Macro Input_Counter.

Truth table and logic gates and VBA Macro.Consider the following worksheet that forms a truth table row number in A2 and converts the row number into the corresponding logic variable (A, B and C) input values in C2:E2.Elementary gates are formed: Y = NOT(A) in A5, Y= AND(ABC) in C5 and Y = OR(ABC) in E5.VBA Macro TT_row assigned to the row arrow increments the value in A2 and resets it to 0 if it is greater than 7. a.What is the Excel formula in C2? b.What is the Excel formula in D2? c.What is the Excel formula in E2? d.What is the Excel formula in A5? e.What is the Excel formula in C5? f.What is the Excel formula in E5? g.Compose Macro TT_row.

Rand() to form other random numbers and outcomes.The following work sheet shows Y = RAND() in A2 expressed in 3 decimal-point precision.Using the value in A2, generate the following random numbers. a.Y1 in B2 that is uniformly distributed over [-0.5, 0.5) in 3-digit precision. b.Y2 in C2 that is uniformly distributed over [-2,3) c.Y3 in D2 simulates a fair coin flip experiment with outcome "H" for head and "T" for tail. d.Y4 in E2 simulates a fair die toss experiment with outcome 1 to 6. A B C D E 1 Y 1 2 3 4 2 0.137 -0.363 -1.817 1

Using Standardized Gaussian PRNG to generate Gaussian noise with specified variance.The following work sheet specifies the noise variance $\sigma _ { \mathrm { N } } ^ { 2 }$ in A2.Using the standardized Gaussian random numbers generated in Column B form the random noise values having the specified variance in Column C. a.What is the formula that computes the noise SD in B2? b.What is the formula in C5? 1 = = 2 4 2 3 4 5 0 -1.306 -2.612 6 1 -0.734 -1.468 7 2 1.602 3.205 8 3 -1.488 -2.975 9 4 -0.392 -0.784

Simple formula with relative and absolute addressing.The following worksheet computes values of $y = m x + b$ a.What is the formula using relative addressing in A5? b.What is the formula using relative and absolute addressing in B7? A B 1 m= b= 2 2 1 3 4 x y 5 0 1 6 1 3 7 2 5 8 3 7

Generating random bits with RAND() and conditional formulas. a.What is the formula that generates random number $\mathrm { Y } 2 _ { \mathrm { i } }$ in A3 that is uniformly distributed over [0,2)? b.What is the conditional formula in C3 that generates random bit bi from the value in B3? c.What is the non-conditional formula in D3 that generates random bit $b _ { i } ^ { \prime }$ from the value in B3? 1 2 0 1.060022 1 1 3 1 0.50675 0 0 4 2 1.242734 1 1 5 3 1.913354 1 1

DAC output with random inputs.Consider the following worksheet that shows ten random samples in Column B that have been matched to a b-bit quantizer with Δ=1 and b specified in B1.The maximum amplitude that is matched to the quantizer is computed in D1.Column C computes the b-bit code using the Excel formula that converts a numerical value to its quantized b-bit binary value.Column D computes the b-bit DAC level using the Excel formula that converts a binary value to its decimal value. a.What is the Excel formula in D1? b.What is the Excel formula in B7? c.What is the Excel formula in C7? d.What is the Excel formula in D7? A B C D 1 = 6 = 63 2 3 bit-code DAC Level 4 0 32.63 100000 32 5 1 40.59 101000 40 6 2 28.06 011100 28 7 3 42.19 101010 42 8 4 15.98 001111 15 9 5 8.04 001000 8 10 6 42.73 101010 42 11 7 33.69 100001 33 12 8 5.95 000101 5 13 9 19.92 010011 19

Formulas with random time sequence with index starting with i=0 and conditional formatting. The following worksheet computes 10 random values using RAND() and finds the average,variance and standard deviation. a.What is the formula in E2? b.What is the formula in E4? c.What is the formula in E6? d.The cells in Column B that are less than the ________ are highlighted using conditional formatting. 1 2 0 0.974928 Aue (Y)= 0.43504 3 1 0.510697 4 2 0.135691 VarIYI= 0.096131 5 3 0.138395 5 4 0.78206 5D(Y)= 0.31005 7 5 0.249 8 5 0.16439 9 7 0.69363 10 8 0.149533 11 9 0.552078

Generating sinusoidal signal in noise and computing SNR.The following worksheet specifies a 15-sample sinusoidal signal $\mathbf { S } _ { 1 }$ having one period with amplitude specified in A2.The noise $\mathrm { N } _ { \mathrm { i } }$ variance $\sigma _ { \mathrm { N } } ^ { 2 }$ is specified in B2. a.What is the formula that computes the signal $\mathrm { S } _ { 1 }$ in B6? b.What is the formula that computes the noise $\mathrm { N } _ { 1 }$ in C6? c.What is the formula that computes the observed signal $x _ { 1 }$ in D6? d.What is the formula that computes the signal energy $\mathrm { E } _ { \mathrm { s } }$ in G5? e.What is the formula that computes the SNR in G7?

Counters using Macro-recorded key strokes and VBA Macro.Consider the following worksheet. a.Implement a counter in A3 using an assist value in B3 by recording a sequence of key trokes in a Macro "Count1" and assigned to the oval shape. b.Compose VBA Macro "Count2" to implement a counter in D3 assigned to the rectangle. c.Compose VBA Macro "Reset" assigned to the rounded rectangle shape that resets count values in A3 and D3 to zero.

ADC b-bit Quantizer of sinusoidal signal.Consider the following worksheet that shows ten samples of a one-period sinusoidal waveform in Column B that has been matched to a b-bit quantizer with Δ=1 and b specified in B1.The waveform amplitude that is matched to the quantizer is computed in D1.Column C computes the quantizer level.Column D computes the b-bit code using the Excel formula that converts a decimal value to its b-bit binary value. a.What is the Excel formula in D1? b.What is the Excel formula in B7? c.What is the Excel formula in C7? d.What is the Excel formula in D7? A B C D 1 = 6 = 63 2 3 -bit code 4 0 31.5 31 011111 5 1 50.0 50 110010 6 2 61.5 61 111101 7 3 61.5 61 111101 8 4 50.0 50 110010 9 5 31.5 31 011111 10 6 13.0 12 001100 11 7 1.5 1 000001 12 8 1.5 1 000001 13 9 13.0 12 001100