Excel 2007 : Project help - STATISTICS

Hey guys!

I'm working on a project for a class, and I'm running into a few problems.

The assignment asks to take at least 50 random samples of size 100, out of a lot of 100,000 numbers. I was to take the sample variance and sample mean of each, which I have done. I have 51 different sheets of sample data(named Sample 1, Sample 2,....., Sample 51), with the mean in cell C1 and variance in C2. I am to make a histogram (I'll be using the Data Analysis add-in) of the sample mean and variance.

I was wondering how I can select that same cell in all 51 sheets and make a histogram of it. Or, if possible, a quick and simple way to copy the data from each cell into a list on a new worksheet.

I figured this out....Another part of the project asks that I make a confidence interval for a user selectable value of alpha. I linked the word "confidence interval" to the wiki page so you can read a little bit about it. The confidence interval uses the Cumulative Normal Distribution table. How can I make a sort of a lookup table in Excel? The table works in that it finds a value in the chart, takes the Y axis and attaches it to the X axis. (For example, if the user wanted a 95% CI (alpha = .05, look for alpha/2, which is .025), the output would be -1.96, which I would be able to use in a formula).

Any help would be appreciated.
Thanks.

start your formula inthe cell where you want the answer, on a seperate sheet to where your data is. then type =sum(, point to the cell in the 1st sheet, hold down the crt key, select the last sheet in your sheet range, type ), press enter.

this will sum your values across all sheets for that cell. you can modify the function to suite your needs, eg use average or mean or mod instead of sum

for the 2nd part, you can use a =vlookup(what-you-want-to-find,range-where-you-want-to-search,column-number-containing-the-answer,false) the false is used to search thru an unsorted list - use true if the list is sorted.

let me know if this is what you wanted?

Uhhh, not really.

I don't need a sum or an average of the sample mean/variance. I want to copy them over onto a new sheet into a list. (So, I want C1 on sheet 1, C1 sheet 2... and so on... to copy over to a new worksheet so C1 from sheet 1 is in A1, C1 from sheet 2 is in A2, C1 from sheet 3 is in A3, and so on)

I figured out how to do the second part of this. Didn't know that Excel was already equipped with a "CONFIDENCE" formula.

Isn't it so that the idea behind homework is to find the solution yourself?

I've been looking for a resource online, but I haven't actually found anything useful, so I figured I'd ask a forum some direction. That is finding it on my own, isn't it? Using my resources?

• Calculate SE from SD using the formula: SE = SD / √n

• Calculate SD from SE using the formula: SD = SE(√n)

• Calculate SE from CI using the formula: SE = [upper95% - lower95%]/(2*1.96)

• Calculate CI from SE and Point Estimate using the formula: CI = Point Estimate ± (SE*1.96)

In this case, the point estimate is the mean and standard error can be calculated from the samples.

Please upload a sample workbook so we can see what you are attempting to do.

Originally Posted by abousetta

Please upload a sample workbook so we can see what you are attempting to do.

Alright, yeah. Good idea. Here's the link : http://www.mediafire.com/?dt466ddeyf8st0b

Not sure what you are trying to do. So this might or might not help:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Oh, yeah. That's not really what I'm trying to get done haha. You see how I have the 51 different samples? Each sample has a value in C1, the mean of the sample. What I'm trying to do is, make a new sheet (Lets call it Sample Mean). I want to take C1 from Sample 1 and place it in A1 of Sample Mean. Then, C1 from Sample 2 and place it in A2 of Sample Mean, and so on and so forth.... Maybe there's a way to write a program where it copies C1 from Sheet 'n' (where n is a variable) and place it in A'n' in Sample Mean,

This should do the trick:

Please Login or Register  to view this content.

Result will look like this:

Please Login or Register  to view this content.

Uh, I think that looks like it would work, but I'm not really familiar with VB coding. I made a new sheet, opened the code, and pasted that, but when I run it I get a "Subscript out of Range" error.

The code goes into a regular module not the sheet code.

Alt + F11 --> Insert --> Module --> Paste the code

Do you intend on meta-analyzing this data? Assuming using inverse variance method of pooling.

Originally Posted by abousetta

The code goes into a regular module not the sheet code.

Alt + F11 --> Insert --> Module --> Paste the code

Oh hey, got it working. Thanks so much! And as far as meta-analysis, I'm not going to do that. I just needed to this last step. Thanks again, I'll add the [SOLVED] thing to the top.

OK. Thanks for the feedback. Heads-up the Excel Confidence formula uses Standard Deviation not Variance and in your workbook you were using Variance.

Good luck.

abousetta