Round function not working when decimal is .5

[mcomp72]

I have code that is supposed to round a number to the nearest integer. If the first number after the decimal is 5 or greater, it should round up to the next integer. For example:
14.5 would round to 15
14.4999 would round to 14

My code is working unless the number after the decimal is 5. Here's a screenshot I took of me stepping through the code.

You can see that it is reading a number from a cell on a worksheet into a variable called Average. That number is 14.5. It is then rounding that number and putting the new number into another variable called RoundedAverage. But in this case, RoundedAverage should be 15 (shouldn't it?), but instead it is 14. I cannot figure out why.

I tried making both Average and RoundedAverage into type Double, but that made no difference.

Any idea how I can make this work so that the result is 15 instead of 14?

 

[Bill Rockenbach]

mcomp72, How are you calculating the un-rounded number 14.5. I suspect the internal number is less than 14.5 e.g. 14.49999999

 

[mcomp72]

The code is reading the value of a cell on a worksheet. If you look at the image in my original post, you will see it. It's the third item in the Watch window. The value listed there is 14.5. If it was something like 14.4999, wouldn't it show the actual value in the Watch window?

In terms of how that number is generated: there's a formula in a cell on another sheet that uses the Average function. But all numbers that it is averaging are integers. Here's a screenshot of how it calculates the average of 10 integers.

The number then appears in Column T of the sheet my code is reading from using the VLOOKUP function. So the result of the VLOOKUP formula in this case is 14.5.

Also, please keep in mind that this is not happening just the one time. It happens anytime I have a number where the first and only number after the decimal is 5.

 

[Bill Rockenbach]

Excel does funny things with the internal numbers so what you see on the screen is not always the internal number.
Try typing in 14.5 in cell E29 and see if you get the same problem

 

[mcomp72]

Just tried it. Same problem occurred.

Before I tried it, I changed the number of decimal places the number is cell E29 was showing, to see what it would be. Here's a screenshot.

After I input 14.5 into the cell manually, here's what it looked like.

The value in RoundedAverage after the code ran was still 14.

 

[mcomp72]

I decided to do a test and create a new workbook with a simple sub in it, to see what happened when it did the ROUND function on several numbers. Take a look at what happened when I tried it the first time.

Just like what was happening to me, it rounded 14.5 to 14. Then I decided to try it again, but this time make the variable Num a Single instead of a Double. Look what happened then.

This time it worked!

So, Excel can't do math properly on a Double?

Unfortunately, in my real workbook, it didn't matter if the variables were of type Single. The problem still happened, so I am not sure what to do.

 

[sericom]

Does this work

RoundedAverage = Application.WorksheetFunction.Round(Average,0)

 

[mcomp72]

Does this work

RoundedAverage = Application.WorksheetFunction.Round(Average,0)

That worked!!

I'm totally confused as to why the VBA function of Round didn't work, but the Worksheet function did. Any idea why that is?

In any event, many thanks for the solution!!

 

[sericom]

Think I read vba round does Bank rounding, so the last digit will be rounded to and even number. If you did 15.5 that would go to 16