Problem 4 Do the textbook problem 4 in Chapter 12hands on ac

Problem 4 Do the textbook problem #4 in Chapter 12-hands on activity.

Solution

Sub FormatXBarRTable()
   \'since the table of containig the observation data is not shown, the data is assumed to be in cells B2:D11
   \'each row in the range contains daily observations of three circuit boards
   \'Please make any required changes to the range selection and activecell.offsets according to the location of the \'data.
    dim x_bar[row_count] as Double
   dim r[row_count] as Double  
   dim x_dbar,x_sum,x_max,x_min,r_bar,r_sum as double
   dim x_barUCL,x_barLCL,r_barUCL,r_barLCL as double
   dim rng,cl as Range

\'select the range where the observation data has been stored and count the number of rows(each row is assumed to contain three daily observations)
    Range(\"B2:D11\").Select
   row_count=selection.rows.count
   x_sum=0  
   r_sum=0
   range(\"B2\").select
   for i=0 to row_count-1
       x_bar[i]=(activecell.value + activecell.offset(0,1).value + activecell.offset(0,2).value)/3 \'calculate x-bar;3 observations in 1 sample are assumed to be
                                                                                                   \'in 3 horizontally consecutive cells; make changes to offsets if this is not so
       \'find x_max
       if activecell.value>activecell.offset(0,1).value and activecell.value>activecell.offset(0,2).value then
           x_max=activecell.value
       endif
       if activecell.offset(0,1).value>activecell.value and activecell.offset(0,1).value>activecell.offset(0,2).value then
           x_max=activecell.offset(0,1).value
       endif
       if activecell.offset(0,2).value>activecell.value and activecell.offset(0,2).value>activecell.offset(0,1).value then
           x_max=activecell.offset(0,2).value
       endif
      
       \'find x_min
       if activecell.value<activecell.offset(0,1).value and activecell.value<activecell.offset(0,2).value then
           x_min=activecell.value
       endif
       if activecell.offset(0,1).value<activecell.value and activecell.offset(0,1).value<activecell.offset(0,2).value then
           x_min=activecell.offset(0,1).value
       endif
       if activecell.offset(0,2).value<activecell.value and activecell.offset(0,2).value<activecell.offset(0,1).value then
           x_min=activecell.offset(0,2).value
       endif

       x_sum=x_sum+x_bar[i]   \'take sum to calculate x_dbar later
       r[i]=x_max-x_min       \'calculate r for each row
       r_sum=r_sum+r[i]       \'take sum to calculate r_bar later
      
       activecell.offset(1,0).select \'go to next row
   next i
  
   x_dbar=x_sum/rowcount
   r_bar=r_sum/rowcount
   \'calculate upper and lower limits of x-bar chart
   x_barUCL=x_dbar+1.023*r_bar
   x_barLCL=x_dbar-1.023*r_bar
   \'calculate upper and lower limits of r-bar chart  
   r_barUCL=2.575*r_bar
   r_barLCL=0*r_bar   \'please recheck this from the question
  
   \'Now set formatting for B2:D11
   range(\"B2:D11).select
   set rng=range(selection)
  
   for each cl in rng
       if cl.value<x_barLCL or cl.value>x_barUCL then
           cl.font.bold=true
           cl.interior.color=RGB(255,255,0)
       endif
   next cl
  
   \'Now set formatting for F2:F11
   range(\"F2:F11).select
   set rng=range(selection)
   for each cl in rng
       if cl.value<r_barLCL or cl.value>r_barUCL then
           cl.font.bold=true
           cl.interior.color=RGB(255,0,0)
       endif
   next cl  
End Sub