Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm getting unwanted rounding to integer for an average of range of cells.
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm getting unwanted rounding to integer for an average of range of cells.
wrote:
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. 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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm getting unwanted rounding to integer for an average of rangeof cells.
ahhhhhhh... I overlooked the fact that Long does not include decimals =P
Thanks! 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
average with out rounding up in a group of cells | Excel Worksheet Functions | |||
Unwanted Rounding | Excel Discussion (Misc queries) | |||
help with rounding up to whole integer | Excel Discussion (Misc queries) | |||
Rounding to nearest integer | Excel Worksheet Functions | |||
Unwanted Rounding Errors | Excel Programming |