I'm getting unwanted rounding to integer for an average of range of cells.

  • Thread starter Thread starter angst987
  • Start date Start date
A

angst987

I'm trying to return an average value for a variable range of cells using this line of code:

rangeavg = WorksheetFunction.Average(Range(n1 & o1 & ":" & n1& o2))

The values in the range have up to 3 decimal places, and the "rangeavg" variable is defined as "Long" but the routine keeps returning the average as an integer value. I need it to maintain the decimal place data. Am I missing something in this line of code or the variable definition or something?
 
rangeavg = WorksheetFunction.Average(Range(n1 & o1 & ":" & n1 & o2))

The values in the range have up to 3 decimal places, and
the "rangeavg" variable is defined as "Long" but the routine
keeps returning the average as an integer value. I need it
to maintain the decimal place data.
Click to expand...

Well, duh!, whadaya think type Long is?

Define rangeavg as type Double, as follows:

Dim rangeavg As Double

By the does not "maintain" the number of decimal places; that is, it is not
limited to 3 decimal places.

If you want to force rounding to 3 decimal places, you might do this one of
two ways:

rangeavg = Round(WorksheetFunction.Average(Range(n1 & o1 & ":" & n1 &
o2)),3)

rangeavg = WorksheetFunction.Round(WorksheetFunction.Average(Range(n1 & o1 &
":" & n1 & o2)),3)

There is a subtle difference between VBA Round and Excel Round. It is
demonstrated by the following example:

MsgBox Round(1.2345,3) & vbNewLine & WorksheetFunction.Round(1.2345,3)
 
Back
Top