Calculating an Average The spreadsheet calculates the average of a set of numbers； and then subtracts the average from each number。 The example demonstrates reading a plete spreadsheet to get a number and reading individual elements to perform a calculation。 …………………………………………………………Page 331…………………………………………………………… CH AP T E R 1 1 ■ L E A R N IN G AB O U T 。 N E T G E N E R I CS 309 Let’s use the following numbers to calculate the average。 Dim items As Double（） = New Double（） ｛ 1。0； 2。0； 3。0 ｝ The average number is 2。0； and if you subtract the average from each number； you will get the series –1； 0； and 1。 To make this work for a spreadsheet； the first step is to declare and then populate an IWorksheet（Of BaseType） instance。 To instantiate an IWorksheet（Of BaseType） instance； you use a factory that will instantiate the Worksheet（Of BaseType） class。 The code looks like this： Dim sheetAverage As IWorksheet（Of Double） = _ SpreadsheetManager。CreateEmptyWorksheet（Of Double）（〃〃） Dim items As Double（） = New Double（） ｛1； 2； 3｝ sheetAverage。Dimension（（items。Length ＋ 10）； 3） For row = 0 To items。Length 1 sheetAverage。SetCellState（row； 0； items（row）） Next row The worksheet is declared as being of type Double （IWorksheet（Of Double））； allowing you to manage a Double value。 To populate sheetAverage； the numbers are iterated in a For loop and assigned to the worksheet using the SetCellState（） method。 The method Dimension（） is needed to create a fixed…length worksheet。 With the worksheet populated； to make sure everything looks right； you could call the method ToString（） and see if all is as expected。 The next step is to assign the lambda expressions that will be used to calculate the average and then the individual differences from the average。 When you assign a calculation to the work sheet； you need to know whether the lambda expression will be stateful or stateless。 Remember that lambda expressions have some state。 It is just a question of whether you want a shared state lambda expression or an individual…state lambda expression。 In the case of the lambda expressions for the average calculations； a shared state is acceptable。 To calculate the average； you use a technique where the average calculation is the last element in the series of the array calculations。 Thus； when the average calculation is called； it knows how many elements there are because of the row in which the average calculation is stored。 Function Average（ByVal worksheet As IWorksheet（Of Double）； _ ByVal cellRow As Integer； ByVal cellCol As Integer） Dim runningTotal As Double = 0 Dim row As Integer For row = 0 To cellRow 1 runningTotal = （runningTotal ＋ worksheet。GetCellState（row； 0）） Next row Return （runningTotal / CDbl（cellRow）） End Function 。 。 。 sheetAverage。AssignCellCalculation（items。Length； 0； AddressOf Average） In the example； the average is calculated by using the variable cellRow as a maximum row。 Every cell （GetCellState（）） before cellRow is added to a running total （runningTotal）； and then； finally； an average is returned by dividing runningTotal by cellRow。 …………………………………………………………Page 332…………………………………………………………… 310 CH AP T E R 1 1 ■ L E A R N I N G A B OU T 。 N E T G E N E R I CS With the average calculated； the next step is to calculate the difference between the average and the individual items。 The result will be stored in a column to the right of the item cell state。 This is done by subtracting the average calculation cell state from the item value； as follows： For row = 0 To items。Length 1 sheetAverage。AssignCellCalculation（row； 1； _ Function（worksheet As IWorksheet（Of Double）； cellRow As Integer； _ cellCol As Integer） （worksheet。GetCellState（cellRow； 0） _ worksheet。Calculate（items。Length； 0））） Next row The number of cell…state calculations depends on the count of numbers in items。 Each cell calculation is assigned a locally declared lambda expression； meaning that the lambda expres sions of all cell states will be identical and share the same state。 The only shared variable is items。Length。 All the lambda expressions expect the same length； and so it is acceptable to share this variable。 The average difference is calculated by calculating the average and then subtracting it from the worksheet cell item value that is in the zeroth column。 Finally； when everything is assigned； you can call the worksheet。Calculate（） method to calculate the average and difference from the average。 sheetAverage。Calculate（） Console。WriteLine（sheetAverage。ToString（）） Understanding Why the Calculation Worked The cell calculations work because the spreadsheet has the ability to track what has been calculated and what has not been calculated。 In a typical spreadsheet； you can change one cell in a sheet and have everything magically recalculate。 There is no such feature for this spreadsheet。 However； this simpler spreadsheet version can make sure that when there are dependencies； they are not calculated multiple times。 Look back at the source code to calculate the difference between the average and a number。 The only reason the calculation worked is that the cell that contained the average was called using the Calculate（） method。 Had the GetCellState（） method been used； the average might not have been cal