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.
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)
TeamTotal=TeamTotal + 1
sicknessaverage = TotalSickness / TeamTotal 'This is where the variable is shortened to four places
Cells(1,1) = "Average " & sicknessaverage & " Days per Person"
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.