Actual Excel Exam, CS88, Spring 2009

12 points each

Date, Time arithmetic

1. Put today’s date in cell A1. In cell A2, give me the date which is 26 days earlier than it. Using addition / subtraction.
   
2. Put the current time in cell A5. In cell A6, give me the time which is 8 hours and 14 minutes, 32 seconds later. Using addition / subtraction, division.
   
3. Using the Date function and other functions, give me the date which is 1 year, 5 months, 8 days from today.
   
   Using the Time function and other functions, give me the time which is 16 hours, 14 minutes, and 32 seconds from now.
   
4. Using functions cleverly. Using date functions, determine whether the present year (using the TODAY function) is a leap year. As a hint: In a leap year, February has one extra day in it.

Counting, summing techniques

1. Make a range of numbers.
   In subsequent rows, we would have 17; 45; 68; 69; 12; 21; 32
   Give me the count of all numbers which are between 12 and 46, inclusive. Use CountIfs. For an additional 10 points, do it using an array formula as well.
   
2. Next to those numbers, we have:
   “Josh”; “Paul”; “Peter”; “Mary”; “Sally”; “Betty”; “Veronica”
   Give me the sum of all scores where the person’s first name contains 4 letters.
   
3. Use the Frequency function to separate a bunch of numbers into bins. 47; 55; 28; 39; 30; 20; 31; Of size 10 each.
   

Lookup Functions

1. Perform a two-way lookup, looking up Item type by Salesperson. You might want to use match and index for this.
   
   

Financial functions

1)

 

2) Know how to use CUMPRINC and CUMIPMT.

For an additional 10 points, also give me the same calculations using array formulas.

Ch 18, PivotTabes

1)

Order Date,Salesperson,Order Amount,Category,Sale Item
1/1/2005,Dixon,1856.16,Dresses,No
1/2/2005,Hernandez,920.44,Blouses,Yes
1/2/2005,Lugo,532.03,Accessories,Yes
1/3/2005,Paredes,3316.74,Dresses,No
1/5/2005,Lugo,2058.95,Dresses,Yes
1/5/2005,Lugo,593.1,Skirts and Pants,No
1/5/2005,Paredes,1899.54,Dresses,No
1/5/2005,Hernandez,568.46,Skirts and Pants,Yes
1/6/2005,Dixon,1559.58,Dresses,No
1/7/2005,Lugo,237.86,Blouses,Yes
1/8/2005,Hernandez,256.25,Skirts and Pants,No
1/8/2005,Lugo,1167.31,Skirts and Pants,Yes
1/8/2005,Lugo,326.48,Blouses,Yes
1/9/2005,Dixon,1109.53,Sweaters,No
1/9/2005,Paredes,835.85,Skirts and Pants,No
1/9/2005,Paredes,1809.46,Dresses,Yes
1/9/2005,Paredes,1062.66,Blouses,Yes
1/10/2005,Lugo,3391.94,Dresses,Yes
1/10/2005,Paredes,978.55,Dresses,No
1/12/2005,Hernandez,2154.15,Dresses,No
1/13/2005,Hernandez,891.65,Dresses,Yes
1/13/2005,Lugo,1441.66,Blouses,No
1/13/2005,Hernandez,1288.7,Sweaters,Yes
1/14/2005,Dixon,100.95,Accessories,No
1/14/2005,Lugo,2037.14,Dresses,No
1/14/2005,Paredes,1356.34,Sweaters,No
1/14/2005,Dixon,2241.9,Dresses,Yes
1/14/2005,Dixon,1701.11,Blouses,Yes
1/14/2005,Hernandez,438.65,Blouses,No
1/15/2005,Lugo,447.25,Blouses,Yes
1/16/2005,Dixon,703.95,Blouses,Yes
1/16/2005,Hernandez,308.78,Blouses,No
1/16/2005,Lugo,1716.8,Dresses,Yes
1/16/2005,Paredes,986.13,Dresses,Yes
1/16/2005,Lugo,67.84,Lingerie,No
1/19/2005,Dixon,618.5,Skirts and Pants,No
1/19/2005,Paredes,1901.48,Dresses,Yes
1/19/2005,Paredes,997.68,Sweaters,Yes
1/21/2005,Dixon,25.25,Lingerie,No
1/21/2005,Lugo,1493.88,Dresses,Yes
1/21/2005,Lugo,1242.75,Sweaters,No
1/21/2005,Lugo,316.63,Blouses,No
1/21/2005,Paredes,93.75,Lingerie,No
1/22/2005,Paredes,519.45,Sweaters,No
1/22/2005,Paredes,1084.71,Skirts and Pants,Yes
1/23/2005,Hernandez,1469.59,Sweaters,No
1/23/2005,Hernandez,118.75,Lingerie,No
1/23/2005,Lugo,2239.44,Sweaters,Yes
1/23/2005,Lugo,1370.5,Sweaters,No
1/23/2005,Paredes,3469.24,Dresses,No
1/23/2005,Lugo,63.08,Lingerie,Yes
1/24/2005,Lugo,148.89,Skirts and Pants,No
1/24/2005,Hernandez,148.89,Skirts and Pants,No
1/26/2005,Hernandez,206.43,Blouses,Yes
1/26/2005,Hernandez,206.43,Blouses,No
1/26/2005,Lugo,938.19,Skirts and Pants,Yes
1/27/2005,Lugo,1616.56,Blouses,No
1/27/2005,Dixon,2212.39,Dresses,No
1/28/2005,Dixon,1565.85,Dresses,Yes
1/29/2005,Lugo,1200.64,Blouses,Yes
1/29/2005,Lugo,1437.38,Dresses,No
1/29/2005,Paredes,1284.86,Sweaters,No
1/30/2005,Dixon,959.24,Dresses,No
1/30/2005,Hernandez,1604.56,Blouses,No
1/30/2005,Hernandez,55.5,Accessories,No
1/30/2005,Lugo,1283.43,Sweaters,Yes
1/30/2005,Lugo,949.9,Sweaters,Yes
1/30/2005,Lugo,333.25,Blouses,No
1/30/2005,Paredes,873.33,Blouses,No
1/30/2005,Paredes,2741.64,Dresses,No
1/31/2005,Hernandez,74.79,Accessories,Yes
2/2/2005,Lugo,816.94,Blouses,No
2/2/2005,Paredes,1426.23,Sweaters,No
2/3/2005,Lugo,863.71,Skirts and Pants,No
2/3/2005,Lugo,1234.36,Dresses,No
2/4/2005,Dixon,2129.85,Dresses,No
2/4/2005,Lugo,1224.11,Sweaters,No
2/4/2005,Lugo,357.31,Accessories,No
2/4/2005,Paredes,2856.23,Dresses,No
2/4/2005,Lugo,681.34,Blouses,Yes
2/6/2005,Hernandez,938.14,Blouses,Yes
2/6/2005,Hernandez,462.48,Accessories,Yes
2/6/2005,Lugo,258.99,Lingerie,No
2/6/2005,Paredes,138.4,Lingerie,No
2/7/2005,Lugo,881.86,Blouses,Yes
2/7/2005,Paredes,677.19,Blouses,No
2/7/2005,Paredes,1910.46,Dresses,Yes
2/9/2005,Hernandez,264.01,Lingerie,Yes
2/9/2005,Lugo,492.51,Sweaters,No
2/9/2005,Paredes,477.15,Sweaters,No
2/10/2005,Dixon,1237.3,Dresses,No
2/10/2005,Hernandez,1370.93,Sweaters,Yes
2/10/2005,Lugo,31.99,Lingerie,No
2/11/2005,Hernandez,454.96,Accessories,No
2/11/2005,Hernandez,142.51,Skirts and Pants,No
2/11/2005,Paredes,142.51,Skirts and Pants,Yes
2/11/2005,Lugo,252.34,Lingerie,Yes
2/12/2005,Hernandez,1438.74,Sweaters,No
2/12/2005,Lugo,1246.16,Sweaters,No
2/12/2005,Lugo,527.64,Sweaters,No
2/13/2005,Hernandez,219.35,Blouses,No
2/13/2005,Hernandez,1843.13,Dresses,Yes
2/13/2005,Hernandez,453.9,Blouses,No
2/13/2005,Dixon,219.35,Blouses,Yes
2/14/2005,Dixon,622.08,Skirts and Pants,Yes
2/16/2005,Lugo,174.75,Blouses,Yes
2/16/2005,Hernandez,174.75,Blouses,Yes
2/17/2005,Dixon,560.31,Skirts and Pants,No
2/18/2005,Hernandez,1618.35,Sweaters,Yes
2/18/2005,Lugo,1167.75,Skirts and Pants,Yes
2/18/2005,Lugo,258.79,Blouses,No
2/18/2005,Lugo,1989.03,Dresses,No
2/19/2005,Dixon,26.23,Lingerie,Yes
2/19/2005,Hernandez,354.41,Blouses,Yes
2/19/2005,Lugo,554.94,Accessories,No
2/20/2005,Dixon,1132.8,Skirts and Pants,No
2/20/2005,Lugo,139.53,Skirts and Pants,Yes
2/20/2005,Lugo,139.53,Skirts and Pants,No
2/20/2005,Paredes,3471.83,Dresses,No
2/20/2005,Paredes,1391.2,Dresses,Yes
2/21/2005,Paredes,1387.35,Dresses,No
2/23/2005,Lugo,1725.06,Sweaters,Yes
2/24/2005,Dixon,674.98,Blouses,No
2/25/2005,Dixon,529.88,Accessories,No
2/25/2005,Paredes,1126.41,Dresses,Yes
2/26/2005,Hernandez,254.16,Blouses,No
2/26/2005,Lugo,65.28,Lingerie,No
2/26/2005,Paredes,534.24,Sweaters,No
2/27/2005,Hernandez,257.98,Blouses,Yes
2/27/2005,Lugo,1026,Sweaters,No
2/28/2005,Dixon,1238.8,Dresses,No
2/28/2005,Hernandez,783.91,Blouses,Yes
3/2/2005,Dixon,761.1,Accessories,No
3/2/2005,Hernandez,787.79,Skirts and Pants,Yes
3/2/2005,Paredes,980.24,Dresses,Yes
3/3/2005,Dixon,909.85,Blouses,Yes
3/3/2005,Hernandez,3400.49,Dresses,No
3/3/2005,Hernandez,297.74,Blouses,Yes
3/3/2005,Hernandez,537.05,Skirts and Pants,No
3/3/2005,Paredes,1101.63,Skirts and Pants,Yes
3/3/2005,Lugo,1998.6,Dresses,Yes
3/4/2005,Dixon,164.23,Lingerie,Yes
3/4/2005,Hernandez,1512.05,Sweaters,No
3/4/2005,Lugo,978.76,Dresses,Yes
3/4/2005,Paredes,606.71,Skirts and Pants,Yes
3/4/2005,Paredes,60.3,Accessories,Yes
3/4/2005,Hernandez,1285.68,Sweaters,No
3/4/2005,Dixon,1151,Skirts and Pants,Yes
3/5/2005,Dixon,1191.01,Skirts and Pants,No
3/6/2005,Dixon,874.26,Blouses,No
3/6/2005,Hernandez,177.14,Lingerie,Yes
3/6/2005,Lugo,474.98,Blouses,Yes
3/6/2005,Hernandez,69.38,Accessories,No
3/7/2005,Dixon,1265.78,Sweaters,No
3/9/2005,Dixon,600.1,Accessories,No
3/9/2005,Paredes,2483.69,Sweaters,Yes
3/10/2005,Paredes,855.4,Skirts and Pants,Yes
3/11/2005,Dixon,740.44,Blouses,No
3/11/2005,Dixon,563.6,Blouses,No
3/11/2005,Hernandez,547.01,Skirts and Pants,Yes
3/11/2005,Hernandez,379.44,Accessories,No
3/11/2005,Lugo,1834.18,Dresses,No
3/12/2005,Dixon,965.38,Dresses,No
3/12/2005,Hernandez,1592.4,Dresses,Yes
3/13/2005,Hernandez,1334.8,Sweaters,Yes
3/13/2005,Hernandez,824.09,Blouses,Yes
3/13/2005,Lugo,809.16,Blouses,No
3/13/2005,Lugo,170.68,Lingerie,No
3/13/2005,Lugo,315.54,Accessories,Yes
3/14/2005,Dixon,2115.45,Dresses,Yes
3/14/2005,Paredes,3235.49,Dresses,Yes
3/14/2005,Paredes,1300,Sweaters,No
3/16/2005,Hernandez,1204.96,Sweaters,Yes
3/16/2005,Paredes,869.18,Dresses,No
3/17/2005,Paredes,1401.13,Dresses,No
3/18/2005,Dixon,2823.93,Dresses,Yes
3/18/2005,Dixon,887.48,Skirts and Pants,No
3/18/2005,Dixon,273.83,Accessories,No
3/18/2005,Hernandez,1674.21,Sweaters,No
3/18/2005,Hernandez,1557.9,Dresses,No
3/18/2005,Lugo,721.66,Blouses,No
3/18/2005,Paredes,275.5,Lingerie,Yes
3/18/2005,Paredes,368.54,Accessories,No
3/18/2005,Lugo,620.53,Skirts and Pants,No
3/19/2005,Dixon,835.15,Sweaters,No
3/19/2005,Dixon,694.53,Accessories,No
3/19/2005,Hernandez,1062.11,Sweaters,Yes
3/19/2005,Hernandez,754.84,Blouses,No
3/20/2005,Hernandez,663.73,Accessories,Yes
3/20/2005,Hernandez,73.63,Accessories,Yes
3/20/2005,Lugo,776.8,Dresses,No
3/21/2005,Hernandez,1209.92,Dresses,No
3/21/2005,Paredes,256.42,Blouses,No
3/23/2005,Hernandez,865.65,Sweaters,Yes
3/23/2005,Hernandez,391.51,Blouses,Yes
3/23/2005,Hernandez,203.74,Accessories,No
3/23/2005,Paredes,178.96,Blouses,No
3/23/2005,Paredes,178.96,Blouses,No
3/24/2005,Hernandez,764.92,Sweaters,No
3/24/2005,Hernandez,198.17,Blouses,Yes
3/24/2005,Paredes,487.89,Blouses,Yes
3/24/2005,Dixon,19.71,Lingerie,No
3/25/2005,Hernandez,1425.52,Dresses,No
3/25/2005,Hernandez,545.62,Blouses,Yes
3/25/2005,Lugo,1066.51,Sweaters,No
3/26/2005,Hernandez,500.06,Blouses,No
3/26/2005,Hernandez,323.73,Accessories,No
3/26/2005,Lugo,65.04,Accessories,No
3/27/2005,Hernandez,330.51,Accessories,No
3/27/2005,Lugo,802.28,Sweaters,Yes
3/27/2005,Paredes,743.69,Sweaters,No
3/28/2005,Hernandez,923.15,Skirts and Pants,No
3/30/2005,Hernandez,1123.46,Dresses,Yes
3/30/2005,Hernandez,882.27,Sweaters,No
3/30/2005,Paredes,184.78,Blouses,No
3/31/2005,Dixon,1551.52,Dresses,No
3/31/2005,Hernandez,925.38,Dresses,No
4/1/2005,Paredes,1397.36,Dresses,Yes
4/1/2005,Dixon,433.94,Skirts and Pants,No
4/2/2005,Hernandez,1331.43,Dresses,No
4/2/2005,Paredes,1755.57,Sweaters,No
4/2/2005,Paredes,1259.16,Dresses,Yes
4/2/2005,Hernandez,50.28,Lingerie,No
4/3/2005,Lugo,759.7,Sweaters,No
4/3/2005,Paredes,903.33,Sweaters,No
4/3/2005,Paredes,128.18,Blouses,Yes
4/3/2005,Paredes,128.18,Blouses,Yes
4/3/2005,Lugo,502.26,Skirts and Pants,Yes
4/4/2005,Lugo,1476.58,Dresses,Yes
4/6/2005,Hernandez,677.13,Sweaters,Yes
4/6/2005,Paredes,1155.49,Blouses,Yes
4/7/2005,Hernandez,821.85,Dresses,No
4/7/2005,Lugo,1363.62,Sweaters,No
4/7/2005,Lugo,515.95,Blouses,Yes
4/7/2005,Lugo,199.36,Lingerie,Yes
4/7/2005,Paredes,2181.6,Dresses,No
4/8/2005,Dixon,1049.36,Sweaters,No
4/8/2005,Dixon,823.39,Skirts and Pants,No
4/8/2005,Hernandez,1027.29,Sweaters,Yes
4/8/2005,Hernandez,265.02,Accessories,No
4/8/2005,Paredes,272.68,Blouses,No
4/9/2005,Dixon,944.72,Sweaters,No
4/9/2005,Hernandez,961.39,Blouses,Yes
4/9/2005,Hernandez,492.72,Accessories,Yes
4/10/2005,Dixon,636.04,Accessories,No
4/10/2005,Dixon,570.58,Blouses,Yes
4/10/2005,Hernandez,1150.41,Dresses,No
4/10/2005,Hernandez,729.75,Blouses,No
4/10/2005,Lugo,931.06,Dresses,Yes
4/10/2005,Paredes,207.36,Blouses,Yes
4/10/2005,Paredes,853.89,Sweaters,Yes
4/10/2005,Lugo,387.14,Sweaters,Yes
4/11/2005,Hernandez,548.35,Blouses,No
4/11/2005,Hernandez,817.43,Skirts and Pants,No
4/13/2005,Hernandez,2582.14,Dresses,No
4/13/2005,Hernandez,1865.33,Blouses,No
4/13/2005,Lugo,530.29,Blouses,Yes
4/13/2005,Dixon,899.32,Dresses,No
4/14/2005,Hernandez,922.25,Skirts and Pants,No
4/14/2005,Dixon,744.36,Blouses,No
4/14/2005,Hernandez,672.65,Sweaters,No
4/15/2005,Lugo,399.08,Accessories,No
4/15/2005,Paredes,313.98,Accessories,No
4/16/2005,Dixon,1371.04,Sweaters,Yes
4/16/2005,Hernandez,25.02,Lingerie,Yes
4/16/2005,Lugo,142.94,Blouses,No
4/16/2005,Lugo,142.94,Blouses,No
4/16/2005,Paredes,616.98,Accessories,Yes
4/17/2005,Hernandez,681.03,Blouses,No
4/17/2005,Hernandez,559.77,Accessories,Yes
4/17/2005,Paredes,681.13,Dresses,No
4/17/2005,Paredes,305.96,Skirts and Pants,No
4/18/2005,Dixon,1354.13,Dresses,No
4/20/2005,Dixon,613.24,Accessories,No
4/20/2005,Dixon,23.87,Lingerie,Yes
4/20/2005,Lugo,1298.99,Dresses,No
4/20/2005,Lugo,360.17,Accessories,No
4/21/2005,Dixon,817.91,Blouses,No
4/21/2005,Hernandez,2140.44,Dresses,No
4/21/2005,Hernandez,1213.59,Dresses,Yes
4/21/2005,Dixon,644.31,Accessories,Yes
4/22/2005,Hernandez,2100.39,Dresses,Yes
4/22/2005,Lugo,115,Lingerie,Yes
4/23/2005,Hernandez,1696.76,Blouses,No
4/23/2005,Lugo,1243.12,Dresses,No
4/23/2005,Lugo,784.86,Dresses,No
4/24/2005,Hernandez,1447.35,Dresses,No
4/24/2005,Hernandez,783.96,Blouses,Yes
4/24/2005,Hernandez,325.3,Blouses,Yes
4/24/2005,Lugo,501.13,Skirts and Pants,No
4/24/2005,Lugo,254.64,Accessories,Yes
4/25/2005,Lugo,714.16,Accessories,No
4/27/2005,Dixon,1271.8,Dresses,Yes
4/27/2005,Hernandez,587.85,Accessories,Yes
4/27/2005,Lugo,1608.15,Dresses,Yes
4/27/2005,Lugo,111.32,Skirts and Pants,No
4/27/2005,Dixon,111.32,Skirts and Pants,No
4/28/2005,Dixon,1049.54,Sweaters,No
4/28/2005,Hernandez,1133.16,Sweaters,No
4/28/2005,Lugo,189.94,Lingerie,Yes
4/29/2005,Dixon,207.3,Lingerie,Yes
4/29/2005,Hernandez,726.1,Dresses,Yes
4/29/2005,Hernandez,218.73,Accessories,No
4/29/2005,Hernandez,393.6,Accessories,Yes
4/30/2005,Lugo,562.88,Blouses,No
5/1/2005,Dixon,588.6,Blouses,No
5/1/2005,Dixon,410.38,Skirts and Pants,No
5/1/2005,Hernandez,756.09,Blouses,No
5/1/2005,Hernandez,740.06,Blouses,Yes
5/1/2005,Hernandez,711.97,Dresses,Yes
5/1/2005,Lugo,278.74,Blouses,No
5/1/2005,Lugo,1347.29,Dresses,No
5/1/2005,Paredes,44.78,Accessories,No
5/2/2005,Hernandez,232.06,Accessories,No
5/2/2005,Lugo,398.15,Skirts and Pants,Yes
5/5/2005,Dixon,795.74,Dresses,No
5/6/2005,Dixon,761.8,Blouses,Yes
5/6/2005,Paredes,705.06,Dresses,Yes
5/7/2005,Lugo,1497.04,Dresses,Yes
5/7/2005,Paredes,761.77,Dresses,No
5/9/2005,Hernandez,423.05,Blouses,Yes
5/12/2005,Lugo,811.4,Dresses,No
5/13/2005,Paredes,285.57,Accessories,No
5/14/2005,Hernandez,471.7,Accessories,No
5/15/2005,Dixon,1108.61,Sweaters,Yes
5/16/2005,Lugo,204.99,Accessories,No
5/19/2005,Hernandez,150.03,Blouses,Yes
5/19/2005,Lugo,150.03,Blouses,No
5/21/2005,Dixon,1882.59,Blouses,No
5/21/2005,Hernandez,180.74,Blouses,Yes
5/21/2005,Dixon,180.74,Blouses,No
5/22/2005,Hernandez,729.35,Blouses,No
5/23/2005,Hernandez,381.53,Skirts and Pants,No
5/23/2005,Hernandez,145.87,Skirts and Pants,No
5/23/2005,Hernandez,47.39,Lingerie,No
5/23/2005,Paredes,806.55,Skirts and Pants,No
5/24/2005,Dixon,1222.03,Dresses,No
5/26/2005,Paredes,524.12,Blouses,No
5/27/2005,Paredes,689.45,Sweaters,No
5/29/2005,Hernandez,1881.54,Dresses,No
5/30/2005,Dixon,427.67,Accessories,No
5/30/2005,Hernandez,853.52,Dresses,No
5/30/2005,Lugo,802.43,Blouses,No
6/2/2005,Lugo,1704.62,Blouses,No
6/3/2005,Hernandez,1505.71,Dresses,No
6/4/2005,Hernandez,704.24,Dresses,No
6/5/2005,Dixon,1707.3,Blouses,No
6/5/2005,Hernandez,929.25,Sweaters,No
6/6/2005,Lugo,1132,Sweaters,No
6/6/2005,Paredes,147.25,Skirts and Pants,No
6/9/2005,Hernandez,1105.64,Sweaters,Yes
6/9/2005,Hernandez,678.87,Sweaters,No
6/10/2005,Paredes,2070.88,Dresses,No
6/10/2005,Paredes,543.8,Blouses,No
6/11/2005,Lugo,490.68,Accessories,No
6/12/2005,Paredes,525.79,Skirts and Pants,No
6/13/2005,Hernandez,858.27,Sweaters,No
6/13/2005,Hernandez,548.85,Skirts and Pants,No
6/13/2005,Hernandez,19.43,Lingerie,No
6/14/2005,Dixon,238.02,Skirts and Pants,No
6/16/2005,Dixon,1191.99,Sweaters,No
6/17/2005,Paredes,804.37,Blouses,Yes
6/18/2005,Hernandez,652.75,Blouses,Yes
6/18/2005,Lugo,1710.1,Sweaters,Yes
6/19/2005,Dixon,1468.11,Sweaters,No
6/19/2005,Hernandez,782.74,Blouses,No
6/20/2005,Lugo,20.06,Lingerie,Yes
6/24/2005,Hernandez,976.03,Sweaters,No
6/25/2005,Lugo,1454.81,Dresses,Yes
6/26/2005,Hernandez,289.51,Blouses,No
6/30/2005,Dixon,803.25,Skirts and Pants,No
6/30/2005,Hernandez,2064.76,Dresses,Yes
6/30/2005,Lugo,624.19,Skirts and Pants,No
6/30/2005,Paredes,813.82,Sweaters,Yes
7/1/2005,Dixon,1010.05,Sweaters,No
7/1/2005,Dixon,704.39,Sweaters,No
7/1/2005,Hernandez,338.87,Skirts and Pants,No
7/2/2005,Hernandez,2063.78,Dresses,Yes
7/2/2005,Paredes,449.52,Accessories,Yes
7/4/2005,Dixon,579.45,Blouses,No
7/4/2005,Dixon,566.01,Skirts and Pants,Yes
7/4/2005,Dixon,467.26,Skirts and Pants,Yes
7/4/2005,Hernandez,738.89,Blouses,No
7/4/2005,Paredes,361.34,Blouses,Yes
7/6/2005,Hernandez,745.5,Blouses,No
7/6/2005,Hernandez,220.44,Accessories,No
7/7/2005,Paredes,363.13,Skirts and Pants,No
7/8/2005,Dixon,667.81,Dresses,Yes
7/9/2005,Lugo,54.94,Accessories,Yes
7/9/2005,Lugo,199.07,Accessories,No
7/10/2005,Lugo,221.01,Accessories,Yes
7/10/2005,Hernandez,758.37,Sweaters,Yes
7/10/2005,Dixon,319.77,Accessories,No
7/11/2005,Dixon,876.03,Dresses,No
7/11/2005,Hernandez,933,Sweaters,Yes
7/12/2005,Dixon,1491.91,Dresses,No
7/14/2005,Hernandez,1137.25,Sweaters,No
7/14/2005,Hernandez,86.38,Accessories,No
7/14/2005,Hernandez,86.38,Accessories,No
7/14/2005,Dixon,341.42,Accessories,Yes
7/14/2005,Hernandez,1059.49,Sweaters,Yes
7/15/2005,Lugo,792.61,Blouses,No
7/16/2005,Hernandez,486.49,Blouses,Yes
7/16/2005,Hernandez,245.45,Accessories,No
7/16/2005,Paredes,848.88,Skirts and Pants,Yes
7/16/2005,Lugo,1425,Dresses,Yes
7/16/2005,Paredes,541.62,Accessories,No
7/18/2005,Paredes,762.88,Blouses,No
7/19/2005,Dixon,2140.78,Dresses,No
7/20/2005,Lugo,162.49,Blouses,No
7/20/2005,Paredes,162.49,Blouses,No
7/21/2005,Dixon,464.18,Blouses,No
7/21/2005,Hernandez,519.91,Skirts and Pants,No
7/22/2005,Dixon,1151.91,Dresses,Yes
7/22/2005,Dixon,129.31,Blouses,No
7/22/2005,Lugo,749.24,Blouses,Yes
7/22/2005,Paredes,129.31,Blouses,No
7/23/2005,Hernandez,560.38,Blouses,Yes
7/23/2005,Paredes,1546.4,Dresses,Yes
7/25/2005,Dixon,649.04,Blouses,No
7/25/2005,Dixon,524.17,Blouses,Yes
7/25/2005,Hernandez,1236.7,Dresses,Yes
7/25/2005,Hernandez,120.35,Blouses,No
7/25/2005,Lugo,120.35,Blouses,No
7/27/2005,Hernandez,1705.15,Sweaters,No
7/29/2005,Dixon,1496.74,Dresses,Yes
7/29/2005,Paredes,781.39,Dresses,No
7/29/2005,Paredes,251.99,Accessories,No
7/30/2005,Dixon,483.38,Accessories,No
7/30/2005,Lugo,420.23,Skirts and Pants,No
7/31/2005,Dixon,1120.11,Dresses,Yes
7/31/2005,Dixon,422.98,Accessories,Yes
8/1/2005,Dixon,1019.17,Blouses,No
8/1/2005,Dixon,517.89,Blouses,No
8/1/2005,Dixon,410.82,Skirts and Pants,No
8/1/2005,Hernandez,2500.28,Dresses,No
8/1/2005,Lugo,818.74,Sweaters,Yes
8/1/2005,Paredes,1223.51,Dresses,No
8/2/2005,Paredes,1367.38,Dresses,No
8/4/2005,Dixon,1379.25,Blouses,No
8/5/2005,Paredes,1146.18,Dresses,Yes
8/6/2005,Dixon,845.57,Dresses,No
8/6/2005,Dixon,154.74,Blouses,No
8/6/2005,Hernandez,154.74,Blouses,No
8/6/2005,Paredes,362.01,Accessories,Yes
8/7/2005,Dixon,1215.29,Skirts and Pants,Yes
8/8/2005,Dixon,1473.64,Dresses,No
8/8/2005,Paredes,661,Sweaters,No
8/9/2005,Hernandez,618.51,Blouses,No
8/9/2005,Paredes,278.61,Accessories,Yes
8/11/2005,Dixon,612.04,Skirts and Pants,No
8/11/2005,Dixon,443.66,Dresses,Yes
8/11/2005,Dixon,119.9,Lingerie,No
8/12/2005,Dixon,1331.75,Dresses,No
8/12/2005,Dixon,1161.58,Sweaters,No
8/12/2005,Hernandez,1188.68,Dresses,No
8/12/2005,Hernandez,50.09,Lingerie,Yes
8/13/2005,Dixon,547.4,Skirts and Pants,No
8/13/2005,Hernandez,1227.12,Dresses,No
8/13/2005,Hernandez,345.72,Blouses,Yes
8/13/2005,Lugo,48.43,Lingerie,Yes
8/14/2005,Dixon,412.11,Accessories,Yes
8/14/2005,Lugo,1641.19,Dresses,No
8/14/2005,Paredes,485.95,Skirts and Pants,Yes
8/14/2005,Paredes,115.66,Skirts and Pants,Yes
8/14/2005,Lugo,115.66,Skirts and Pants,Yes
8/15/2005,Hernandez,288.19,Blouses,Yes
8/15/2005,Lugo,90.89,Accessories,Yes
8/15/2005,Hernandez,90.89,Accessories,Yes
8/16/2005,Dixon,1430.23,Dresses,No
8/16/2005,Dixon,850.73,Sweaters,No
8/16/2005,Hernandez,518.89,Blouses,No
8/16/2005,Paredes,382.6,Accessories,No
8/18/2005,Lugo,1053.28,Dresses,No
8/18/2005,Paredes,24.16,Lingerie,Yes
8/19/2005,Dixon,577.76,Skirts and Pants,No
8/20/2005,Dixon,2638.84,Dresses,Yes
8/20/2005,Dixon,495.86,Skirts and Pants,No
8/21/2005,Dixon,1284.36,Dresses,No
8/21/2005,Dixon,1172.72,Dresses,No
8/21/2005,Dixon,961.75,Dresses,No
8/21/2005,Dixon,156.03,Blouses,Yes
8/21/2005,Hernandez,117.91,Skirts and Pants,No
8/21/2005,Dixon,156.03,Blouses,Yes
8/21/2005,Dixon,117.91,Skirts and Pants,No
8/22/2005,Paredes,646.85,Blouses,No
8/23/2005,Hernandez,1406.08,Sweaters,No
8/26/2005,Dixon,601.32,Blouses,No
8/26/2005,Dixon,25.29,Lingerie,Yes
8/26/2005,Hernandez,2632.74,Dresses,No
8/27/2005,Dixon,1145.8,Blouses,Yes
8/27/2005,Lugo,494.39,Blouses,No
8/28/2005,Dixon,110.09,Lingerie,Yes
8/28/2005,Lugo,486.89,Skirts and Pants,No
8/29/2005,Hernandez,1364.55,Dresses,Yes
8/30/2005,Dixon,717.19,Dresses,No
8/30/2005,Hernandez,100.64,Skirts and Pants,Yes
8/30/2005,Hernandez,100.64,Skirts and Pants,No
8/31/2005,Lugo,248.31,Blouses,No
9/1/2005,Paredes,949.99,Skirts and Pants,No
9/2/2005,Dixon,1524.09,Dresses,Yes
9/2/2005,Hernandez,100.27,Lingerie,No
9/2/2005,Lugo,1239.03,Sweaters,No
9/3/2005,Dixon,2093.08,Dresses,No
9/3/2005,Dixon,683.87,Skirts and Pants,Yes
9/3/2005,Dixon,168.17,Blouses,Yes
9/3/2005,Hernandez,1716.91,Dresses,No
9/3/2005,Paredes,168.17,Blouses,No
9/4/2005,Hernandez,515.45,Accessories,No
9/5/2005,Dixon,1874.71,Sweaters,Yes
9/5/2005,Lugo,671.73,Blouses,No
9/8/2005,Dixon,1070.19,Dresses,Yes
9/8/2005,Hernandez,349.1,Blouses,No
9/9/2005,Hernandez,428.55,Skirts and Pants,Yes
9/10/2005,Hernandez,649.87,Blouses,No
9/10/2005,Lugo,1249.33,Dresses,No
9/10/2005,Lugo,206.05,Accessories,No
9/10/2005,Paredes,1388.88,Sweaters,No
9/11/2005,Hernandez,2552.98,Dresses,No
9/11/2005,Hernandez,1065.37,Skirts and Pants,No
9/11/2005,Hernandez,575.59,Blouses,Yes
9/11/2005,Paredes,858.52,Skirts and Pants,Yes
9/11/2005,Paredes,472.7,Skirts and Pants,Yes
9/11/2005,Paredes,211.61,Skirts and Pants,No
9/12/2005,Lugo,621.34,Blouses,No
9/13/2005,Paredes,379.2,Accessories,Yes
9/15/2005,Dixon,1047.31,Dresses,Yes
9/17/2005,Paredes,1319.26,Blouses,No
9/17/2005,Paredes,944.47,Dresses,Yes
9/18/2005,Dixon,754.15,Blouses,No
9/18/2005,Dixon,1131.33,Sweaters,No
9/18/2005,Hernandez,643.25,Blouses,Yes
9/18/2005,Hernandez,185.08,Blouses,No
9/19/2005,Hernandez,484.47,Skirts and Pants,No
9/19/2005,Lugo,1545.95,Dresses,No
9/19/2005,Paredes,1273.93,Dresses,No
9/19/2005,Paredes,1115.98,Sweaters,No
9/22/2005,Dixon,558.78,Blouses,No
9/22/2005,Lugo,1130.44,Dresses,Yes
9/23/2005,Hernandez,1410.72,Dresses,No
9/23/2005,Paredes,544.92,Blouses,No
9/24/2005,Lugo,119.75,Blouses,No
9/24/2005,Paredes,881.16,Sweaters,No
9/24/2005,Dixon,119.75,Blouses,Yes
9/25/2005,Dixon,496.78,Blouses,No
9/26/2005,Lugo,742.04,Dresses,Yes
9/26/2005,Paredes,838.79,Skirts and Pants,No
9/26/2005,Paredes,341.09,Accessories,No
9/26/2005,Paredes,147.25,Skirts and Pants,No
9/27/2005,Paredes,410.23,Sweaters,No
9/29/2005,Hernandez,554.71,Blouses,No
9/30/2005,Hernandez,136.62,Blouses,Yes
9/30/2005,Paredes,689.02,Accessories,No
9/30/2005,Hernandez,1224.19,Dresses,Yes
9/30/2005,Lugo,329.51,Blouses,No
9/30/2005,Paredes,136.62,Blouses,No
10/1/2005,Lugo,61.7,Accessories,No
10/1/2005,Paredes,397.7,Skirts and Pants,Yes
10/3/2005,Dixon,494.15,Blouses,No
10/3/2005,Dixon,23.62,Lingerie,No
10/3/2005,Hernandez,1620.22,Blouses,No
10/3/2005,Paredes,843.79,Skirts and Pants,No
10/4/2005,Paredes,254.13,Skirts and Pants,Yes
10/4/2005,Paredes,25.14,Lingerie,Yes
10/4/2005,Lugo,183.07,Blouses,No
10/7/2005,Dixon,768.71,Sweaters,Yes
10/7/2005,Paredes,498.02,Skirts and Pants,Yes
10/8/2005,Paredes,676.53,Dresses,Yes
10/8/2005,Hernandez,1378.1,Dresses,Yes
10/9/2005,Dixon,2595.19,Dresses,Yes
10/9/2005,Paredes,1148.52,Sweaters,No
10/9/2005,Paredes,728.55,Dresses,No
10/10/2005,Hernandez,286.78,Blouses,Yes
10/10/2005,Lugo,1014.87,Dresses,No
10/10/2005,Dixon,198.46,Lingerie,Yes
10/11/2005,Lugo,1133.71,Dresses,No
10/11/2005,Dixon,173.16,Blouses,No
10/11/2005,Hernandez,173.16,Blouses,No
10/13/2005,Hernandez,147.65,Blouses,No
10/13/2005,Lugo,697.08,Blouses,No
10/13/2005,Paredes,147.65,Blouses,No
10/14/2005,Dixon,178.17,Blouses,No
10/14/2005,Hernandez,178.17,Blouses,No
10/14/2005,Paredes,1454.84,Dresses,No
10/14/2005,Paredes,968.77,Sweaters,Yes
10/14/2005,Paredes,1019.68,Sweaters,Yes
10/14/2005,Paredes,509.14,Accessories,Yes
10/15/2005,Hernandez,697.29,Sweaters,No
10/15/2005,Paredes,1674.96,Dresses,No
10/15/2005,Lugo,549.47,Accessories,Yes
10/16/2005,Lugo,1005.93,Dresses,No
10/16/2005,Hernandez,1649.23,Dresses,Yes
10/17/2005,Dixon,1135.62,Skirts and Pants,No
10/17/2005,Dixon,817.72,Dresses,No
10/18/2005,Hernandez,545.96,Blouses,Yes
10/20/2005,Hernandez,2510.51,Dresses,No
10/21/2005,Lugo,174.87,Blouses,Yes
10/21/2005,Lugo,174.87,Blouses,Yes
10/21/2005,Dixon,339.07,Accessories,Yes
10/22/2005,Dixon,1181.71,Dresses,No
10/23/2005,Dixon,1634.97,Dresses,No
10/23/2005,Dixon,1092.71,Sweaters,Yes
10/23/2005,Lugo,141.56,Blouses,No
10/23/2005,Paredes,106.44,Skirts and Pants,No
10/23/2005,Paredes,106.44,Skirts and Pants,Yes
10/23/2005,Dixon,141.56,Blouses,No
10/24/2005,Lugo,24.79,Lingerie,No
10/24/2005,Paredes,693.8,Dresses,Yes
10/25/2005,Hernandez,80.75,Lingerie,Yes
10/25/2005,Lugo,358.63,Accessories,No
10/27/2005,Dixon,698.02,Blouses,Yes
10/27/2005,Hernandez,729.21,Blouses,Yes
10/28/2005,Paredes,143.45,Blouses,Yes
10/28/2005,Hernandez,1644.81,Dresses,Yes
10/28/2005,Lugo,143.45,Blouses,No
10/29/2005,Dixon,927.67,Sweaters,Yes
10/29/2005,Dixon,804.66,Blouses,Yes
10/29/2005,Hernandez,1059.53,Sweaters,Yes
10/29/2005,Paredes,1298.85,Sweaters,No
10/29/2005,Paredes,831.68,Sweaters,No
10/29/2005,Paredes,1315.73,Dresses,No
10/30/2005,Lugo,1094.12,Dresses,No
10/31/2005,Paredes,1379.82,Dresses,Yes
11/4/2005,Paredes,1828.89,Dresses,No
11/4/2005,Lugo,946.25,Sweaters,Yes
11/5/2005,Dixon,848.85,Blouses,Yes
11/5/2005,Hernandez,253.28,Blouses,No
11/5/2005,Lugo,467.47,Skirts and Pants,No
11/5/2005,Lugo,364.56,Blouses,Yes
11/5/2005,Paredes,947.67,Skirts and Pants,No
11/5/2005,Paredes,609.81,Accessories,No
11/6/2005,Dixon,1782.92,Blouses,No
11/7/2005,Hernandez,471.93,Blouses,No
11/8/2005,Paredes,911.64,Blouses,No
11/8/2005,Dixon,1886.24,Dresses,No
11/10/2005,Paredes,1700.74,Dresses,No
11/11/2005,Dixon,1528.08,Sweaters,No
11/11/2005,Hernandez,811.97,Sweaters,No
11/12/2005,Hernandez,1712.04,Blouses,No
11/14/2005,Hernandez,560.31,Blouses,No
11/14/2005,Lugo,2186.66,Dresses,Yes
11/15/2005,Hernandez,163.93,Blouses,Yes
11/15/2005,Lugo,163.93,Blouses,Yes
11/15/2005,Paredes,405.24,Skirts and Pants,No
11/17/2005,Dixon,285.31,Accessories,Yes
11/18/2005,Dixon,264.2,Blouses,No
11/18/2005,Hernandez,231.86,Skirts and Pants,No
11/18/2005,Hernandez,89.96,Accessories,Yes
11/18/2005,Hernandez,89.96,Accessories,Yes
11/18/2005,Lugo,499.66,Skirts and Pants,No
11/18/2005,Hernandez,878.94,Dresses,No
11/18/2005,Lugo,236.98,Lingerie,No
11/20/2005,Hernandez,570.46,Skirts and Pants,Yes
11/20/2005,Paredes,1198.49,Dresses,No
11/20/2005,Paredes,954.02,Sweaters,Yes
11/20/2005,Paredes,651.71,Blouses,No
11/21/2005,Paredes,855.42,Blouses,No
11/21/2005,Paredes,412.89,Blouses,Yes
11/24/2005,Lugo,1782.01,Dresses,No
11/25/2005,Dixon,550.98,Skirts and Pants,No
11/25/2005,Lugo,1895.3,Dresses,Yes
11/25/2005,Paredes,526.94,Skirts and Pants,No
11/26/2005,Hernandez,957.36,Dresses,No
11/27/2005,Hernandez,1248.26,Blouses,Yes
11/27/2005,Hernandez,543.38,Skirts and Pants,Yes
11/27/2005,Hernandez,194.07,Blouses,Yes
11/27/2005,Hernandez,109,Accessories,No
11/27/2005,Hernandez,109,Accessories,No
11/27/2005,Lugo,1251.37,Sweaters,No
11/27/2005,Paredes,194.07,Blouses,No
11/28/2005,Dixon,22.09,Lingerie,Yes
11/28/2005,Hernandez,312.53,Blouses,Yes
11/28/2005,Lugo,2510.71,Dresses,Yes
11/28/2005,Hernandez,1776.36,Dresses,No
11/30/2005,Dixon,3036.21,Dresses,Yes
12/2/2005,Dixon,1639.96,Dresses,No
12/2/2005,Lugo,1010.45,Sweaters,No
12/2/2005,Paredes,1345.72,Sweaters,No
12/2/2005,Paredes,56.97,Lingerie,No
12/2/2005,Dixon,472.85,Accessories,Yes
12/3/2005,Paredes,23.39,Lingerie,Yes
12/3/2005,Hernandez,1868.16,Dresses,Yes
12/4/2005,Dixon,566.24,Skirts and Pants,Yes
12/4/2005,Paredes,1139.18,Dresses,No
12/5/2005,Dixon,821.23,Skirts and Pants,No
12/5/2005,Hernandez,1809.48,Dresses,Yes
12/8/2005,Hernandez,2515.15,Dresses,No
12/8/2005,Hernandez,566.57,Skirts and Pants,No
12/8/2005,Lugo,689.29,Blouses,No
12/9/2005,Dixon,878.24,Blouses,No
12/9/2005,Paredes,889.96,Blouses,Yes
12/9/2005,Paredes,568.25,Blouses,No
12/9/2005,Paredes,955.92,Dresses,No
12/9/2005,Paredes,453.72,Skirts and Pants,No
12/10/2005,Dixon,820.81,Sweaters,No
12/11/2005,Dixon,1538.1,Sweaters,Yes
12/12/2005,Dixon,1046.19,Skirts and Pants,No
12/12/2005,Lugo,1856.71,Dresses,No
12/12/2005,Hernandez,421.42,Accessories,No
12/13/2005,Hernandez,127.11,Lingerie,Yes
12/13/2005,Paredes,1013.75,Skirts and Pants,No
12/13/2005,Paredes,481.94,Accessories,No
12/13/2005,Dixon,208.09,Blouses,No
12/13/2005,Dixon,208.09,Blouses,Yes
12/15/2005,Hernandez,863.3,Sweaters,No
12/15/2005,Lugo,138.48,Skirts and Pants,No
12/15/2005,Hernandez,138.48,Blouses,Yes
12/16/2005,Dixon,811.24,Dresses,No
12/16/2005,Paredes,224.41,Lingerie,No
12/17/2005,Paredes,311.55,Accessories,No
12/18/2005,Paredes,1480.12,Dresses,No
12/19/2005,Dixon,1660.08,Blouses,No
12/19/2005,Dixon,1232.64,Sweaters,Yes
12/19/2005,Hernandez,1132.48,Sweaters,Yes
12/19/2005,Hernandez,154.76,Blouses,No
12/19/2005,Hernandez,154.76,Blouses,No
12/19/2005,Lugo,998.75,Dresses,Yes
12/20/2005,Hernandez,1412.03,Sweaters,No
12/20/2005,Paredes,842.86,Blouses,No
12/22/2005,Dixon,1665.05,Dresses,Yes
12/22/2005,Lugo,67.41,Accessories,No
12/23/2005,Paredes,1775.32,Dresses,Yes
12/23/2005,Paredes,762.6,Accessories,No
12/24/2005,Hernandez,174.91,Blouses,No
12/24/2005,Paredes,530.04,Blouses,No
12/24/2005,Paredes,148.05,Blouses,No
12/24/2005,Paredes,148.05,Blouses,No
12/24/2005,Paredes,174.91,Blouses,Yes
12/25/2005,Lugo,223.86,Blouses,No
12/26/2005,Hernandez,2888.04,Dresses,Yes
12/26/2005,Hernandez,838.44,Sweaters,Yes
12/26/2005,Hernandez,233.4,Lingerie,Yes
12/26/2005,Lugo,320.75,Skirts and Pants,Yes
12/27/2005,Hernandez,1254.44,Sweaters,No
12/29/2005,Lugo,1975.13,Dresses,No
12/30/2005,Paredes,174.7,Blouses,Yes
12/30/2005,Hernandez,174.7,Blouses,No
12/31/2005,Dixon,846.81,Sweaters,Yes
12/31/2005,Paredes,1015.97,Sweaters,Yes
12/31/2005,Paredes,536.68,Skirts and Pants,No
12/31/2005,Dixon,133.35,Skirts and Pants,No
12/31/2005,Dixon,133.35,Skirts and Pants,Yes

Make a PivotTable of Salesperson against Category, showing the amount.

For an additional 5 points, do the same, but show the count of amount, rather than the sum.

CS88 Midterm 2

Date and Time (Ch 6)

1. Write a formula which will always evaluate to tomorrow's date. Put this in A1.

   In A2, calculate the date which is 2 years, 5 months, and 5 days after the date in A1.

2. In A3, put the current time. In A4, add 5 hours, 6 minutes and 3 seconds to that time,
 using time functions.

   In A5, calculate the same, based on A3. But use arithmetic rather than
date/time functions.

Counting and summing (Ch 7, 14)

3. Make up a bunch of data (text). Using an array formula, count up how many cells
contain precisely 2 words.

(Recall that you can count words by taking the len of the cell, substituting nothing for
 space, taking the len again, and if the difference is 1, then there was one space in
the cell, and thus two words.)

4. Make up a bunch of data (numbers). Sum the numbers which are in
the range of 10 to 20.

5. Separate the numbers into a group of bins, using either FREQUENCY or a bunch of array formulas.

Deliberate circular references (Ch 16)

6. Generate a bunch of test scores. Then, using the max function, calculate the
highest ever maximum (all time high).

7. Solve the recursive equation; x = cos(x)
8.

1)
Hi
I regularly import data from another program with part numbers =
identified in coloum A as:
R/RVI/0444
R/RVI/5362
R/VOL/3699 etc.

Is there a way of interogating the full part number and displaying only =
the middle three letters and the last digits with out the / between

Thanks in anticipation

Martin

2)
Hi team..pls help me here.

I have an excel sheet with cells having text as shown below:

[India]Delhi is the capital of India.[USA]Washington is the capital of USA.[China]Beijing is the capital of China.[Singapore]Singapore is the capital of Singapore.

This complete text is in one single cell. Similar text is in other cells.

I want to segregate these contents into separate columns so that:

[India]Delhi is the capital of India ->  goes into one column

[USA]Washington is the capital of USA -> goes into another column.. and so on....

3)
I have a long column of numerical data with occassional random
occurences of text in a cell, like so:

25
37.5
48
25.9
57
NAM
65
59
24
etc.

I want to replace every occurrence of "NAM" (always the same text)
with the contents of the cell immediately above.
In the example above I want to replace NAM with 57.

4) Define a name "Database" for the range A1:G6 on Sheet1. You can use absolute references for this.

5) Define a name "OneDown" which refers to the cell immediately below you

6) Define a name "SurroundMinimum" which will give you the minumum *value* of the cells which are OneUp, OneDown, OneLeft, and OneRight. It might help to first define names for all of the above.

7) Define a name "SurroundMinimum" which will give you the *cell reference* of the cell with the minumum value of the cells which are OneUp, OneDown, OneLeft, and OneRight. What I mean by cell reference is that it is the actual cell, such that e.g. if I call the Row() function on it, it will tell me which row it is in.

8) Put the value "hello there how are you doing" in a cell. For the cell formatting, make it bold, red, with a Red Accent 2 fill, with wrap text enabled.

pig

igpay

1. get first letter

2. find out if that letter is a vowel

3. if vowel, just give the word & "way"

4. else, take off the first letter, give rest of word, & first letter, & "ay"

=LEFT(B9, 1)

=OR(B10="A", B10="E", B10="I", B10="O", B10="U")

=B9&"way"

=MID(B9,2,1000)&B10&"ay"

=IF(B11, B12, B13)

  

40638

1. get the year

2. treat it like text

3. get len

4. substitute 0's for blanks, get len

5. subtract second len from first len

6. if 2 or more, say hooray. Else, say boo!

=YEAR(A18)

=TEXT(A27, "@")

=LEN(A27)

=LEN(SUBSTITUTE(A27, "0", "") )

=A29-A30

=IF(A31>=2, "hooray", "boo")

Sample exam

Part I -- Lab Material
___________________

Questions like you will find in a mid-level or capstone exercise.
Amount of material: about two mid-level exercises worth.


Part II
____________________

Not this many questions, but to give you a sampling of types of questions.

1) Make a table with a list of (three) car models and their average highway and city-street mileage. In a separate part of your spreadsheet, have three cells where one fills in the car model, how many miles you need to travel, and what percentage will be on the highway. Based on that, calculate the number of gallons for the entire trip. Use Functions, Absolute, and Relative references where appropriate.

2) What is the R1C1 reference formula for a relative reference, one cell up and three cells to the right?

3) Name a range A1:B6, whose scope is limited only to Sheet1.

4) Let us say you have five word sentence in A1. Using as many cells as you want, calculate for me how many words are longer than 3 letters. I should be able to switch the sentence, and your formulas should still work.

5) The rules for Pig Latin:
http://en.wikipedia.org/wiki/Pig_Latin

My simple rules are as follows: Basically, if a word begins with a consonant, strip off that consonant, give me the rest of the word, and add 'ay' to the end. Thus, 'pig' will become 'igpay' and 'latin' will become 'atinlay'. If a word begins with a vowel, just add 'way' to the end. Thus, 'eye' will become 'eyeway'.

Iway ovelay otay peaksay inway igpay atinlay
is the Pig Latin of
I love to speak in pig latin

Write a formula, or a series of formulas, which will take a single word and make it into its Pig Latin equivalent.

6) Calculate a date which will be 2 years and 35 days from today. By today, I mean TODAY().

7) Write a formula which will tell me if the year of a specific Serial Number contains two zeros in it.