Jan 30, 2007 (T)

We covered the following topics:

• Converting english descriptions into Excel formulas. We went through the following questions:
  

  Write formulas to calculate the following values.

  1. Write a formula that evaluates to the average of the largest values from each of the first 3 rows of the spreadsheet.

     For example using the following spreadsheet the value of the formula should be the average of the numbers 3, 100 and 300.

     

     The formula should continue to work correctly even if the numbers in the spreadsheet change. Also the formula should work correctly even if additional numbers are added to other cells in the spreadsheet. For example if the number 4 were typed into cell D1 in the above spreadsheet (and nothing else in the spreadsheet changed) then the formula should evaluate to the average of the numbers 4, 100 and 300.

  2. Write a formula that evaluates to the sum of the averages of the values in the first three columns of the spreadsheet.

  3. Write a formula that evaluates to the larger value of the following two values:

     • The sum of the averages of the values in the first three columns of the spreadsheet
     • The average of the sums of the values in the first three rows of the spreadsheet
       

• Combining left, right, mid, upper and lower functions and concatenation operator (&) into large formulas

• Introduction to Boolean/Logical values and realtional operators
  

• For more info see the Excel Functions powerpoint on yrosenthal.com