# Can't get WorksheetFunction.Average to work.

• ### Question

• Hi all,

I am trying to compute the average of a range of cells and assign it to a double variable.

Partial code:

```Dim objAppExcel As Excel.Application
Dim objBook As Excel._Workbook = Nothing
Dim objBooks As Excel.Workbooks = Nothing
Dim objSheets As Excel.Sheets = Nothing
Dim objSheet As Excel._Worksheet = Nothing

objAppExcel = New Excel.Application()
objAppExcel.Visible = False
objBooks = objAppExcel.Workbooks
objBook = objAppExcel.Workbooks.Open(CurrentPath & "\myfile.xls")
objSheets = objBook.Worksheets
objSheet = objSheets(1)

Dim StartupAverage As Double = Application.WorksheetFunction.Average("B5:E5")
```

This returns and error, "Reference to a non-shared member requires an object reference."

Can anyone tell me the correct syntax to get the average of the range of cells?

Thanks,

Monday, July 16, 2018 5:13 PM

• You need to reference the worksheet that contains that range:

`Dim StartupAverage As Double = Application.WorksheetFunction.Average(objSheet.Range("B5", "E5"))`

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

• Marked as answer by Tuesday, July 17, 2018 3:40 PM
Monday, July 16, 2018 5:21 PM

### All replies

• You need to reference the worksheet that contains that range:

`Dim StartupAverage As Double = Application.WorksheetFunction.Average(objSheet.Range("B5", "E5"))`

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

• Marked as answer by Tuesday, July 17, 2018 3:40 PM
Monday, July 16, 2018 5:21 PM
• Thanks, Reed, that was the answer.  I ended up using this:

```Dim StartupAverage As Double = objAppExcel.WorksheetFunction.Average(objSheet.Range("B5:E5"))
```

Steve

Monday, July 16, 2018 5:47 PM
• OK, how would I reference the range numerically?

What I am actually trying to achieve is take the average of some cells identified using the Find method, which returns numeric values for columns.

So instead of a range ("B5, "E5"), what I want is a range ( (2,2), (2,5) ).

So I tried this:

`Dim StartupAverage As Double = objAppExcel.WorksheetFunction.Average(objSheet.Range(objSheet.Cells(2, 2), objSheet.Cells(2, 5)))`

But it fails with an error.

Any ideas?

Monday, July 16, 2018 6:17 PM
• Here's information on the algorithm to perform the conversion:

https://support.microsoft.com/en-us/help/833402/how-to-convert-excel-column-numbers-into-alphabetical-characters

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

Monday, July 16, 2018 6:31 PM
• I solved it thusly:

(code snippet, not complete)

```Dim FirstColumnLetter As String = NothingDim FoundFirstColumn As Integer = 0
Dim LastColumnLetter As String = NothingDim FoundNextColumn As Integer = 0
Dim GCell As Range
FoundFirstColumn = GCell.Column

While FoundNextColumn <> FoundFirstColumn
GCell = objSheet.Cells.FindNext(GCell)
FoundNextColumn = GCell.Column
End While

GCell = objSheet.Cells.FindPrevious(GCell)
FoundNextColumn = GCell.Column

'Display the first and last header cell locations.
Console.WriteLine("The first header column letter is " + FirstColumnLetter)
Console.WriteLine("The last header column letter is " + LastColumnLetter)

Dim StartupRange As String = FirstColumnLetter + "5:" + LastColumnLetter + "5"
Console.WriteLine("Startup is: " + StartupRange)
Dim StartupAverage As Double = objAppExcel.WorksheetFunction.Average(objSheet.Range(StartupRange))
Console.WriteLine("Average Startup Time: {0}", StartupAverage)
```

Note that in my case I know precisely the rows where certain data is always stored, so that is hard coded (5 in this case).  What I needed to dynamically identify was the number and location of the rows of data.  Each row corresponds to another set of time data, and the number of rows can vary depending on how many datasets of time are chosen (elsewhere) to be recorded.

The entire program basically identifies all the columns of data, inserts a new column, and then populates it with the averages from the identified columns.

Thanks for the help, Reed!

Monday, July 16, 2018 7:15 PM