Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Pooled standard deviation calculator excel

04/12/2021 Client: muhammad11 Deadline: 2 Day

CH271

Spreadsheet Exercise 2

Reference: Section 4.5C in textbook

In this exercise, we will set up a spreadsheet that will allow us to compare the means of two sets of data obtained on two separate samples and determine if the means are the same or significantly different. In other words, is the difference between the two sets of data real, i.e. the means are not the same due to a systematic error in one or both analyses, or the samples are not the same, or is the difference simply the result of random error. In order to do this, we will use the Student’s t test. As stated in your textbook, for two sets of data consisting of n1 and n2 measurements (with averages x1 and x2 and standard deviations s1 and s2), the value of t is calculated using the following formula (equation 4-4, p. 89):

tcalc = ((x1 - x2(/spooled)[(n1n2)/(n1 + n2)]1/2 (1)

where spooled is a “pooled” standard deviation for both sets of data. The latter is calculated with the assumption that the standard deviations for the two sets of data are not significantly different. Spooled may be calculated using the formula

spooled = [(s12(n1-1) + s22(n2-1))/(n1 + n2 -2)]1/2 (2)

Once t has been calculated, it is compared to the critical value of t from a table (Table 4-2, p. 87) for the desired confidence level for n1 + n2 -2 degrees of freedom. If the calculated t is less than the critical t value, then there is no significant difference between the two means. If the calculated t is greater than the critical t value, then there is a significant difference between the two means.

To obtain the critical value of t with Excel, tcrit, use the TINV function of Excel. The syntax used by Excel is TINV(probability, deg_freedom). The confidence level is determined from the probability value as 100*(1-probability). For example, for the 95% confidence level, the probability is entered as 0.05. Assuming 10 degrees of freedom and 95 % confidence level, the formula entered into Excel to obtain the t-value would be =TINV(0.05,10).

The data for replicate measurements carried out by Carol Khemistry using two different analytical methods with similar precision on the same sample are given below:

Trial

Method 1A Results

Method 2A Results

1

1.97

1.89

2

1.93

1.88

3

1.97

1.91

4

1.96

1.92

5

1.98

1.90

6

1.94

1.88

7

1.99

1.88

8

1.96

1.89

9

1.93

1.92

10

1.96

1.91

11

1.95

1.90

12

1.97

1.89

13

1.94

1.91

Set up a spreadsheet that will calculate the average for each sample, the standard deviation for each average, the number of data points for each method, the degrees of freedom (calculated as n1 + n2 – 1), the pooled standard deviation for the two sets of data (using equation 2), the tcalc value for the two sets of data (using equation 1), and the tcrit value. The spreadsheet should also determine if the means are the same or statistically different using the IF function of Excel.

The average and standard deviation may be calculated directly by using the AVERAGE() and STDEV() functions in Excel. For example, to average the numeric values found in cells A1 through A5, use the following formula: =AVERAGE(A1:A5). A similar formula would be written to calculate the standard deviation. Because the formula for spooled uses the variance, i.e., the square of the standard deviations for each set of data, you can use the VAR function of Excel to calculate the variance to be used in the spreadsheet equation for spooled. To calculate the variance for the numeric values found in cells A1 through A5, use the following formula: =VAR(A1:A5). The COUNT() function should be used to count the number of trials in each set of data. To use this function to count the numeric values found in cells A1 through A5, use the formula: =COUNT(A1:A5).

Once a tcalc value has been calculated by Excel, you can compare it to tcrit via Excel using the IF function and print the appropriate statement: (1) “The two methods give the same results.”, or (2) “The two methods give statistically different results.” The syntax for this function is IF(logical_test,value_if_true,value_if_false). Thus, to use the IF function to compare tcalc in B18 to tcrit in B19, the formula would be: =IF(B18>B19, " The two methods give statistically different results.", " The two methods give the same results.")

Your spreadsheet should have the following properly labeled columns: Trial, Method 1A Results, Method 2A Results. The average, standard deviation, variance, and number of measurements should be calculated below each column of data. You should also include the following properly labeled values below the columns of data and the average and standard deviation values: degrees of freedom, spooled, tcrit, and tcalc. Below the data, the IF function should indicate whether or not the two methods give the same or different results. An example spreadsheet is attached for your reference.

Note that all calculations are to be completed using Excel. Be sure to type your name, the date, and a title for the spreadsheet at the top of the worksheet. Include at the bottom of the page text which clearly gives the formulas used in the calculations and the columns or cells in which they are located. See the example spreadsheet given to you with the Introduction to Spreadsheets handout.

Although students can help each other with questions and problems that might arise while writing a spreadsheet, each student is to complete his or her own spreadsheet!!! Handing in the exact same spreadsheet generated with or by other students is academic dishonesty, and will negate the original purpose of doing the exercise, which is to learn how to use spreadsheets.

Hand in a paper copy of your spreadsheet, and email it as an attachment to Dr. Crawford at pcrawford@semo.edu .

Be sure to save this spreadsheet since we will be using it again in lab.

CH271

Spreadsheet Exercise 2

Comparison of Two Means Using the t-Test

Dr. Crawford's Example

Trial

Sample 1 Results

Sample 2 Results

1

35.51

35.25

2

35.46

35.25

3

35.44

35.28

4

35.48

35.28

5

35.50

35.27

6

35.43

35.26

7

35.44

35.30

8

35.47

Average

35.47

35.27

Standard Deviation

0.029246489

0.018257419

Variance

0.000855357

0.000333333

N

8

7

Degrees of Freedom

13

spooled

0.024787559

tcrit

2.160368652

tcalc

15.29763484

Conclusion:

The two means are significantly different.

Equations Used

Average

AVERAGE(B9:B15)

Standard Deviation

STDEV(B9:B15)

Variance

VAR(B9:B15)

N

COUNT(B9:B16)

Degrees of Freedom

B20+C20-2

spooled

SQRT(((B18*(B19-1))+(C18*(C19-1)))/(B19+C19-2))

tcrit

TINV(0.05,(B19+C19-2))

tcalc

((abs(B16-C16))/B22)*SQRT((B19*C19)/(B19+C19))

Conclusion:

IF(B25>B24, "The two means are significantly different.", "The two means are not significantly different.")

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Writing Factory
Coursework Help Online
Pro Writer
Homework Master
Engineering Guru
Top Writing Guru
Writer Writer Name Offer Chat
Writing Factory

ONLINE

Writing Factory

I have assisted scholars, business persons, startups, entrepreneurs, marketers, managers etc in their, pitches, presentations, market research, business plans etc.

$33 Chat With Writer
Coursework Help Online

ONLINE

Coursework Help Online

I am an elite class writer with more than 6 years of experience as an academic writer. I will provide you the 100 percent original and plagiarism-free content.

$24 Chat With Writer
Pro Writer

ONLINE

Pro Writer

I will provide you with the well organized and well research papers from different primary and secondary sources will write the content that will support your points.

$22 Chat With Writer
Homework Master

ONLINE

Homework Master

I am an experienced researcher here with master education. After reading your posting, I feel, you need an expert research writer to complete your project.Thank You

$19 Chat With Writer
Engineering Guru

ONLINE

Engineering Guru

I will be delighted to work on your project. As an experienced writer, I can provide you top quality, well researched, concise and error-free work within your provided deadline at very reasonable prices.

$45 Chat With Writer
Top Writing Guru

ONLINE

Top Writing Guru

I am an experienced researcher here with master education. After reading your posting, I feel, you need an expert research writer to complete your project.Thank You

$49 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Go ahead waste time on the internet kenneth goldsmith summary - ETHICS AND PROFESSIONS - WK1 Project hsc4010 epidemiology and disease control - God never closes a door without opening a window - Designing a drone for power line inspection - Hacking 101 book pdf - Hacking and Invading - 10 strategic points grand canyon university - Discussion 4 law enforcement - Critical thinking assignment 2 - ET WK3 Paper - App-Biography Viewer - Anzca primary exam preparation course - Barnet hospital pathology department - Essay - 2 iodopropane sodium hydroxide - Huckleberry finn chapter 13 summary - Flat file database management system - Ali macgraw tv series - Windshield survey example - Investigating the social world 7th edition - Caterpillar swot analysis 2016 - Nursing Leadership and Management - An independent measures t hypothesis test is appropriate when ____ - Kinematic equations practice worksheet - Effective Teams - 200 Words - Excel module 2 sam exam answers - Stampy quest to eat ice cream - What is a service channel - Cincinnati zoo case study - Biostatistics practice questions and answers - A widely used disaster recovery approach includes - Creating a healthy and safe supportive environment in childcare - FINANCING NEW VENTURES - Openintro statistics third edition pdf - Reading - La trobe university bachelor of food and nutrition - Duvall's 8 stages of family development - 3 pages - Long term parking by arman - American society for public administration code of ethics - Solidworks stress analysis tutorial - Practice exams business management - 560 dis - Flow measuring apparatus lab report discussion - Research proposal for marketing problem - Crosby's basic elements of improvement - International marketing case study - Acs professional code of ethics - Assignment on marketing plan for a new product ppt - Integrated logistics system ppt - Http hhonors3 hilton com en index html - Environmental Discussion Question (Mod 4) - Student success in college doing what works pdf download - STATISTICS TIMED EXAM NOW! DUE IN 1.5 HOURS - 3 meter line violation volleyball - American history research paper topics - Breezair evaporative cooling manual - Cosby show theo moves out - Barnsley federation of racing pigeons - Kelly consulting accounting - Ap calculus ab scoring guidelines - Drama Analysis - Margaret beeson billings mt - Blue mountains bus timetable - Hazardous area inspection checklist - St martin guide to writing 11th edition - Radius ratio of cscl - Madison harris the owner invested - Where can i find answers for my homework - Trait theory of leadership - An ethical perspective by which choices are based - What is the difference between composition and improvisation - Medibank private dividend history - Pot luck quiz questions and answers 2014 - Paige turner tax return - One crown office row - Lección 1 lesson test vhlcentral answers - Sirius xm case study - Factors affecting evaporation ppt - Conveyor belt project part 3b answers - Human resource management mcgraw hill test bank - A knight's tale movie questions and answers - Healthcare Economic- Week 1 - 8 90 30 fencing - African spear crossword clue - Ashfield park primary school - The further mathematics support programme - Discussion B week 7 - How to write a case study assignment - Discussion question - Why was oglethorpe's prohibition of slavery reversed - Need help - HW - Merck and river blindness case study answers - Andrew chinn tell the good news - Bones that form the orbit of the eye - Coca cola push and pull strategy - One pass shell and tube heat exchanger - Consequences of the Fall and Contemporary Response