Formulas

Absolute - $H$4 – to establish an absolute value include $ before column address $ before row address.

=COUNTIF(AR5:AR161, "1") – Counts value in range. In this formula the range begins with the cell address - column AR row 5 through cell address - column AR row 161. The value the formula is looking for within the range is 1. Another formula may be entered in adjacent cell to look for additional values (i.e., 2, 3, 4, etc.) The COUNTIF formula may also be used to count same names within the column.

=IF(C33=" "," ",(B33-C33)) - Used to display blank cell if no value is present in cell C33.

=IF(B33=0,"0",SUM(B33-$E$23)) - Used to display 0 if no value is present in cell C33.

=IF(B25=0," ",SUM(B25+14+$H$4)) - Used to display blank cell if 0 value is present in cell C33.

=SUM((AVERAGE(art_k!G26,bea_b!G26,ben_k!G26,bra_w!G26,bro_b!G26,car_s!G26,cha_s!G26,cha_c!G26,cha_k!G26,chr_c!G26,coo_s!G26,cra_r!G26,els_a!G26,far_a!G26,fra_t!G26,gar_m!G26,hal_b!G26,her_a!G26,key_d!G26,lar_j!G26,mat_t!G26,max_a!G26,max_c!G26,naq_n!G26,phi_t!G26,pin_j!G26,rus_d!G26,sco_j!G26,sco_s!G26,sep_s!G26)+(AVERAGE(sha_r!G26,tha_e!G26,tho_l!G26,wil_c!G26))/2)) - This formula Adds the results of the first and second set of Averages then divides by 2. The formula looks for value in defined address (G26) within each individual worksheet. art_k!G26 is the first worksheet - identified by the worksheet assigned name art_k. The ! separates the worksheet name and the cell address G26. Note each worksheet address is separated with ,.

Excel can Create a statistical random sample:

1. Create a numbered list of names/data

2. In a blank column insert formula =randbetween(1,750) in first cell of the empty column and copy it in each row down the column to generate a random number.

4. Select column. Copy the data then use Paste Special (right click on column where data will be placed - paste) anc choosing Values. (this routine places a constant number in each row. The the number values are randomly generated. (Note: there may be duplicates)

5. Then sort the data range using the column with random values.

6. Example, if you want 20% randomly selected - 750 x 20% = 150 rows. This will be your sample.

If a number isn't generated in the formula range but an error message, then go to Tools - Add In -- and add Analysis Tool pack. Generate again.