PDA

View Full Version : RoundDown value in Application.WorksheetFunction



alienscript
03-05-2008, 02:07 AM
Hello VBA experts,
I tried in vain to round down the floating point decimal to a whole number. In the active sheet, I have:

A1 B1 C1 D1 E1 F1 G1
6 3 6 8 5 2 1

From Range("J1").Value = Application.WorksheetFunction.Average(Cells(1, 7).Offset(, -6).Resize(, 6))
I get the result of 4.285714286

I tried to use Round, Format, FormatNumber as follow to round the value to whole number, but both doesn't work:

If Range("K1") = Application.WorksheetFunction.Round(Format(Average(Cells(1, 7).Offset(, -6).Resize(, 6))) Then
... statement1

If Range("K1") = Application.WorksheetFunction.Format(Average(Cells(1, 7).Offset(, -6).Resize(, 6)),"##0") Then
... statement1
or
Application.WorksheetFunction.Average(Cells(1, 7).Offset(, -6).Resize(, 6)).NumberFormat = "##0"
If Range("K1") = Application.WorksheetFunction.Average(Cells(1, 7).Offset(, -6).Resize(, 6)) Then
...statement1

I hope someone can give me a hand of how I can round it down to 4 ? Many many thanks.

Bob Phillips
03-05-2008, 02:51 AM
With Application
Range("J1").Value = .RoundDown(.Average(Cells(1, 7).Offset(, -6).Resize(, 6)), 0)
End With

alienscript
03-05-2008, 08:54 AM
Thanks Bob