Deck 13: Excel Best Practices

Question 1

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.

Answer 1

Question 2

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.

Answer 2

Question 3

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
strokes 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.

Answer 3

a.Cell A3 contains the count.B3 contains formula =A3+1.Macro named "Count1"
records following key stroke sequence: copy B3.Paste/special/values into A3.
Esc (to remove dashed line).Select A3.Count1 Macro assign to oval.
The Macro in the VBA editor appears as the following.
Sub Count1()
' VBA from recording key strokes
Range("B3").Select
Selection.Copy
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B14").Select
Application.CutCopyMode = False
Range("A3").Select
End Sub
b.
Sub Count2()
' VBA counter
Range("D3").Value = Range("D3").Value + 1
End Sub
c.
Sub Reset()
' VBA reset
Range("A3").Value = 0
Range("D3").Value = 0
End Sub

Question 4

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?

Question 5

Generating random bits with RAND() and conditional formulas.
a.What is the formula that generates random number
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
from the value
in B3?

Question 6

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.

Question 7

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?

Question 8

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.

Question 9

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.

Question 10

Simple formula with relative and absolute addressing.The following worksheet computes values
of
a.What is the formula using relative addressing in A5?
b.What is the formula using relative and absolute addressing in B7?

Question 11

Using Standardized Gaussian PRNG to generate Gaussian noise with specified variance.The
following work sheet specifies the noise variance 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?

Question 12

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?

Question 13

Generating sinusoidal signal in noise and computing SNR.The following worksheet specifies a
15-sample sinusoidal signal having one period with amplitude specified in A2.The noise variance is specified in B2.
a.What is the formula that computes the signal
in B6?
b.What is the formula that computes the noise
in C6?
c.What is the formula that computes the observed signal
in D6?
d.What is the formula that computes the signal energy
in G5?
e.What is the formula that computes the SNR in G7?