Macro to automate hypothesis test

[appleguru]

Hi Excel Forum.

I need help creating a macro that will automate the calculation of a hypothesis test for me. In my attached file, I have a relatively easy and straight forward example. Can somebody help write a macro code that will automatically count D10:D14 and for it to state whether to reject the Null or Alterative Hypothesis?

Any help, suggestions, examples, links would be greatly appreciated

Best regards,

appleguru

[dogberry]

i

We cant see your attached file follow the instructions below and hopefully one of our excel vba gurus will be able to help you out.


To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.



Chris

[appleguru]

Sorry, long day forgot to attach file before clicking "post"

[appleguru]

So after recording my macro and performing it I was able to create a button that would give me my numbers... the only problem is that I can't get the calculation for the p-value where I have to use "=TDIS(x,DF,tail)" to calculate...

I was hoping somebody can help walk me through writing a code that would perhaps have variables and constants that would generate the p-value and have a message box of "denying the null hypothesis given that the p-value is less then the alpha" from the condition that the p-value is less then .03.

I was also thinking I can use "Case" function in my macro for this case, recommended?

[p24leclerc]

Hi, not sure at all what you want to achieve with your macro. It's not clear for me.
Your sample sheet gives you all you want. Why a macro?

[appleguru]

Just so I can familiarize myself with visual basics. The point is, I want to create a button that'll conduct the hypothesis test for me is if I present it to somebody, say a total stranger who has differet sample mean or n and a different sample std. dev, they cn enter those numbers and then the button will automatically shoot out std error, test stat, df, and p-value.

And so I can have it automatically display my conclusion of accepting or denying the null hypothesis.

[p24leclerc]

Do I get it right if I say you want a popup window with input boxes to enter values for the different variables and then on a click of a button, the answer comes?

[appleguru]

Yes sir, that's my goal from this post.

[p24leclerc]

Here is a workbook with a userform working as you want.
The color of the hypothesis changes in green or red to show the results.
Hope it will help you. You can play with the code to make it work as you wish.
Regards

[appleguru]

Oh wow, thanks a bunch!

Thank you,

appleguru

[Excelsius1]

How did you create UserForm? I didn't even know this was possible in Excel! It seems like with enough programming you can make Excel compete with some expensive programs. I need to find a good tutorial!

[appleguru]

Excelsius1, You can inset a userform by simply right clicking in your project explorer under the folder of your current excel project and inserting it! it's quite a nifty tool to play with and learn!

p24leclerc, please ig can you explain to me also the significance of using "Private Sub" as to "Public Sub" as well as the following code:

"Private Sub TextBox2_Change()
TextBox5 = ""
TextBox6 = ""
TextBox7 = ""
TextBox8 = ""
TextBox9.BackColor = &HFFFFFF
End Sub"

Also,

Is there a way for me to make it so that if the std. error, test stat, df, and p-value are empty in the cell, that i can input the formulas within the userform so that it'll calculate it without the user needing to go through calculating things manually? ie: press test and for the numbers to still despite having the cells empty in the excel sheet as in, put the formulas in to the code as oppose to on the cell sheet?

i know this is complicating things, a little bit but i've been trying for 2hours to try and see if its possible

Thanks, best regards,

appleguru

[p24leclerc]

First thing first. Private versus Public Sub
In a module, you can have multiple SUB and in a workbook, you can have multiple modules.
One sub can call another Private sub in the same module but it will not be able to call a Private sub from another module. In order to do that, the sub you want to call must be PUBLIC.

What does this macro: If you change one of the variables, you would like to clear the previous calculation results as it may be misleading to the operator. So every time a variable is changed, all the result cells are deleted and the color of the Hypothesis is back to WHITE or undefined.

Please Login or Register  to view this content.

About writing formula with VBA. this is quite tricky but there is a simple solution to that. You use the "RECORD MACRO" facility from Excel. Then you select the cell you want and you write the formula for that cell and hit the "ENTER" key. Then you stop recording the macro and look at the code generated in the module created by Excel. You copy the line with the formula into your program and it's done. Here the formula are written with relative references.
Hope this help you.
If you have any other question, do not hesitate to ask.
Regards