Losing decimal places

[emanresu]

Can anybody explain this for me please?

I have a line of code which says avgval = aggval \ itemcnt in my procedure PopValues.
When, for example, aggval = 3.5 and itemcnt = 3 the result should be 1.1666
but it is showing as 1 only.

I have tried different data types (variant, single, double) but that doesn't help. In the code below you can see I have even tried Round.

Option Explicit
Dim x As Integer
Dim y As Integer
Dim i As Integer
Dim Z As Boolean

Dim itemcnt As Integer
Dim aggval As Double
Dim avgval As Double
Dim geoavg As Double
Dim prod As Double
Dim qdate As String
Dim newdate As String
Dim item As String
Dim newitem As String
Dim indbox As String
Dim price As Single
Dim gmArray(500)

Private Sub Main()
On Error Resume Next
Sheets("price_quotes").Select
Range("A2").Select
qdate = Range("A2").Value
item = Range("B2").Value
indbox = Range("F2").Value
itemcnt = 0
avgval = 0
aggval = 0

If InStr(" MNTZ", indbox) > 1 Then
    price = 0
Else
    price = Range("E2").Value
    aggval = aggval + price
    itemcnt = 1
    gmArray(itemcnt) = price
End If

x = 3
y = 5

GetNewValues

Do While (newdate <> "") And (newdate <> " ")
   If (newdate <> qdate) Or (newitem <> item) Then
       PopValues
       itemcnt = 0
       aggval = 0
       avgval = 0
       Erase gmArray
       If price <> 0 Then
           itemcnt = itemcnt + 1
           aggval = aggval + price
           gmArray(itemcnt) = price
       End If
   Else
       If price <> 0 Then
           itemcnt = itemcnt + 1
           aggval = aggval + price
           gmArray(itemcnt) = price
       End If
      ' qdate = newdate
      ' item = newitem
   End If
   qdate = newdate
   item = newitem
   x = x + 1
   Z = 0
   If x = 3174 Then Z = 1
   GetNewValues
Loop

MsgBox "End of procedure"

End Sub

Private Sub GetNewValues()
Sheets("price_quotes").Select
Range("A" & x).Select
newdate = Range("A" & x).Value
newitem = Range("B" & x).Value
indbox = Range("F" & x).Value
If InStr(" MNTZ", indbox) > 1 Then
    price = 0
Else
    price = Range("E" & x).Value
End If
End Sub


Private Sub PopValues()
' need to move over to correct column
Sheets("Bread and Cereals_master").Select
Range("A" & y).Select
avgval = Round(aggval \ itemcnt, 4)
CalcGeoMean (gmArray)
Range("A" & y).Select
ActiveCell.Value = item
Range("B" & y).Select
ActiveCell.Value = qdate
Range("C" & y).Select
ActiveCell.Value = avgval
y = y + 1
Range("C" & y).Select
ActiveCell.Value = geoavg
Selection.Font.Bold = True
y = y + 1
End Sub


Private Sub CalcGeoMean(rs)
prod = rs(1)
If itemcnt > 500 Then MsgBox "Array out of bounds"
For i = 2 To itemcnt
     prod = prod * rs(i)
Next i
geoavg = prod ^ (1 / itemcnt)
End Sub

Thanks!

 

[VoG]

Try

avgval = aggval / itemcnt

 

[emanresu]

Ha ha, I *knew* it would be something simple. Brain too frazzled this late in the evening.

Many thanks!

 

[Rick Rothstein]

avgval = aggval \ itemcnt

As Peter has told you, you want the / operator which performs "Normal Division". The backward slash is a special kind of operator that performs what is called "Integer Division", that is, it is used to divide two number and return an integer result (which is why you were getting 1 as an answer). The help files for the the "\ Operator" says this...

"Before division is performed, the numeric expressions are rounded
to Byte, Integer, or Long expressions.

Usually, the data type of result is a Byte, Byte variant, Integer,
Integer variant, Long, or Long variant, regardless of whether result
is a whole number. Any fractional portion is truncated. However, if any
expression is Null, result is Null. Any expression that is Empty is treated
as 0."

But that does not necessarily tell the whole story. When the help file says "rounded", it means it is rounded using "Banker's Rounding", that is, rounding of fives at the end of the decimal part of a number, when rounding to the position immediately to the left of the five, is rounded to the nearest even number. This can have consequences that are easily overlooked. Here is how I described it across the years in both the compiled VB newsgroups and the Excel newsgroups/forums...

If floating point numbers are involved, integer division may not return the answer you expect. Most people think x\y is short-hand for Int(x/y), probably because of its name “integer” division. However, VB applies Banker’s Rounding to the numerator and denominator before performing the division. Many would think that this…

MsgBox 4.5 \ 1.5

should display 3 in the MessgeBox, however, it prints out 2 instead. Banker’s Rounding rounds the 4.5 to 4 (the nearest even number) and rounds 1.5 to 2 (again, the nearest even number) and only then does it do the division and truncate the fractional part if any (4/2 producing 2 as the answer; in this case, no fractional part to truncate away).