Build your own function (a user-defined function in VBA, as discussed in the text and in

Question

class) for determining the MODE of a distribution resulting from the merger of two

normal distributions (using their normal distribution formulations). Your inputs should be

the means and standard deviations for the two distributions, with units of hours (e.g.

a mean of 10.15 would be equal to 10:09am (since a 0.15 of an hour is 9min). The

output should be the global mode of the composite distribution, and whether a second

local peak exists (just give a yes or no response). Do not use any portion of the

workbook’s spreadsheet to store numbers or do the calculations.

Create a dialog box that prompts users to enter the input information into text boxes, and a button

that makes VBA go through some kind of loop to determine the outputs required. Allow them to

also specify where in the spreadsheet the output should be sent. This exercise should all be done

in VBA – make sure you have a button on your sheet that pops up your form. Take advantage of

the function you’ve created in the prior step (i.e. call it as part of what happens when the

form’s button is clicked). Recall UDFs written in VBA are callable by Macros. Eg.

TextBox1.value=MyUDF(input1,input2)

chp9compounddistribforclass-2-5zwmjdka-s3swio0e-dh3gpfl5.xlsm

Aditi · Accepted Answer

Solution
VBA user-defined function that calculates the mode of a distribution resulting from the merger of two normal distributions, given their means and standard deviations.
Function MergeMode(mean1 As Double, stdev1 As Double, mean2 As Double, stdev2 As Double) As Double
    Dim delta As Double
    Dim mode As Double
    Dim p1 As Double
    Dim p2 As Double
    
    delta = mean2 - mean1
    p1 = stdev2 ^ 2 / (stdev1 ^ 2 + stdev2 ^ 2)
    p2 = stdev1 ^ 2 / (stdev1 ^ 2 + stdev2 ^ 2)
    
    mode = mean1 * p1 + mean2 * p2
    
    MergeMode = mode
End Function
To determine whether a second local peak exists, we can use a separate function that calculates the second mode (if it exists) and compares it to the first mode. If the second mode is sufficiently far away from the first mode (i.e., more than a certain multiple of the combined standard deviation), then a second local peak is deemed to exist.
Function SecondModeExists(mean1 As Double, stdev1 As Double,

Build your own function (a user-defined function in VBA, as discussed in the text and in class) for determining the MODE of a distribution resulting from the merger of two normal distributions...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment