Forum Discussion
Trouble with statistical functions and excel
JoeUser2004 This is actually everything I have concerning the assignment.
I'm having trouble with which function to use and when to simulate more than once and only once, when to use INV and when VERT.
E.g. for the first part: Create a distribution function, which gives you an appropriate probability of one of the values of the record
Do I AVERAGE and STDEV.P the values, and give a RAND() for the x?
So: =NORM.INV(RAND(),AVERAGE("values"),STDEV.P("values"))
I have a feeling that doesn't do what is asked for.
Mmrtlm .... In general, we can help with how to use Excel to solve problems that are well-defined. But usually, we rely on you to "well-define" the problem.
Sometimes, we can offer insight that might clarify the problem. But the most reliable source of such clarification is the assignment, the instructor and other students who are familiar with the coursework to-date.
I'm afraid that I will not be of much help to you. And worse, I'm afraid that I might misdirect you.
I find inconsistencies that confuse me. For example....
-----
You wrote: ``for the first part: Create a distribution function, which gives you an appropriate probability of one of the values of the record [....] So: =NORM.INV(RAND(),AVERAGE("values"),STDEV.P("values"))``
Well, NORM.DIST("oneValue", AVERAGE("values"), STDEV.P("values"), 0) perhaps -- although I would use the difference of two cumulative NORM.DIST calls. (Note: "VERT" = "DIST" in English.)
That requires more explanation. But the point is: your data is uniformally distributed, not normally distributed(!).
And that's just the tip of the iceberg.
I want to help, but I'm afraid that I cannot. Good luck!
JoeUser2004 Misdirecting me shouldn't worry you, it's my risk by asking here, but anyways I wouldn't adapt any solution without thinking it through and understanding myself.
You say that my data is uniformly distributed. How can one tell? And is there a function for uniformly distribution?
If you're unsure or don't want to answer any statistical questions, I completely understand and next time I'll make sure to clearify all non excel concerning parts first.
Mmrtlm .... Yes, I'm unsure of my answers to your questions. So I think it is best that I step back.
You wrote: ``You say that my data is uniformly distributed. How can one tell?``
Graphically.
The blue histogram is the actual distribution of the data. The orange curve is the expected normal distribution of the data. See the attached Excel file.