# Creating a formula that modifies itself based on number of samples.

1. ## Creating a formula that modifies itself based on number of samples.

I am trying to create a form that peforms 95% UCL statistical analysis for a varying number of sample points. I am trying to create a spreadsheet that will put the proper Student T in the formula. I have already created a form that works but only if you select the correct form for the number of samples that you collected.

For example:
If 6 samples are taken then the formula is =(A35+2*(A44/SQRT(6)))
If 3 samples are taken then the formula is =(A35+2.9*(A39/SQRT(3)))

If i make A1 a cell that gives me the number of samples (between 2 and 9) how can i get the formulas above to insert the correct Student T?

The Student T Chart for the number of samples is as Follows:
2=6.3
3=2.9
4=2.4
5=2.1
6=2.0
7thru 9=1.9

I am using Excel 2000 if that matters.  Register To Reply

2. ## Re: Creating a formula that modifies itself based on number of samples.

Hi tdurton,

If you add your Student T Chart to another worksheet, you'll be able to use the LOOKUP function in place of the student T in your formula.

Cheers,  Register To Reply

3. ## Re: Creating a formula that modifies itself based on number of samples. Originally Posted by tdurton If 6 samples are taken then the formula is =(A35+2*(A44/SQRT(6)))
If 3 samples are taken then the formula is =(A35+2.9*(A39/SQRT(3)))

If i make A1 a cell that gives me the number of samples (between 2 and 9) how can i get the formulas above to insert the correct Student T?

The Student T Chart for the number of samples is as Follows:
2=6.3
3=2.9
4=2.4
5=2.1
6=2.0
7thru 9=1.9
This formula converts your table above into a formula:
=LOOKUP(A1, {2,3,4,5,6,7}, {6.3,2.9,2.4,2.1,2,1.9})

This inserts it into the original formula:
=(A35 + LOOKUP(A1, {2,3,4,5,6,7}, {6.3,2.9,2.4,2.1,2,1.9}) * (A44 / SQRT(A1)))  Register To Reply