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? |
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) |
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) |
All times are GMT +1. The time now is 06:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com