# VBA Decimal Places in String Variable

In this post I hope to show you a work around for reducing a string to two decimal places.

I was working on a project just recently for a reporting system for staff sickness.  Part of this had to work out an average sickness for a team , and then output it as part of a sentence.  The average figure would be outputted for example as 1.25372190.  Because it’s part of the sentence I couldn’t  just format the cell to two decimal places.   A work around for this was to modify the variable when declaring it.  I was working with data in excess of 1000 lines, which would change every month.  So to keep it simple, let’s assume that in Sheet 1 in Column A there are four names, in cells A1 to cell A4.  Then cells B1 to B4 detail their sickness days.

Let’s look at the code.

`Sub SicknessAverage()`

`Dim sicknessaverage as String * 4 'This reduces the string to four characters`
`Dim TotalSickness as String 'Total sickness for the whole team`
`Dim TeamTotal as String`

`For A = 3 to 6 Step 1`
`If Cells(A,1) > 0 Then`
`TotalSickness = TotalSickness + Cells(A,1)`
`End If`
`TeamTotal=TeamTotal + 1`
`Next A`

```sicknessaverage = TotalSickness / TeamTotal 'This is where the variable is shortened to four places ```

`Cells(1,1) = "Average " & sicknessaverage & " Days per Person"`

`End Sub`

The line highlighted in green is the line that outputs the sentence with the average sickness figure.  It uses the sicknessaverage variable and assigns the answer to TotalSickness divided by TeamTotal, which in this case is 15/4 = 3.75.  So the sentence would read Average 3.75 Days per Person.

By declaring sicknessaverage as Dim sicknessaverage As String * 4 you are actually saying take the first four characters. So if the the answer was 10.75, the variable would be displayed as 10.7 as the decimal point is counted as a character.