Excel VBA Round-function

**revanreal** · 04-18-2015, 07:37 AM

Hi!

In this case I have been calculating the values from column L to column M, then from column M to column N.
For every column I have done calculations the answers is presented with enough desimals until I reached column N.
Here I only get 1 decimal. As seen in the picture: (Dont mind the colours or the language)

Excel-ROUND.png

I get 0.8 instead of 0.79401 as I get with manual calculation in another spreadsheet

Basically cell N10 = M10 * (I10 - I9)*86400.

It is litre/sec * the date difference * 86400(s) = litre

I am a beginner at VBA but I have tried the following:

Range("M10").Select

Do Until ActiveCell.Value = ""

ActiveCell.Offset(0, 1).Value = Round((ActiveCell.Value * ((ActiveCell.Offset(0, -4).Value) - ActiveCell.Offset(-1, -4).Value) * 86400), 4)
ActiveCell.Offset(1, 0).Select

Loop

I hope you are able to understand my question

Thanks

**TMS** · 04-18-2015, 07:45 AM

https://support.microsoft.com/en-us/kb/194983

When using the Round() function in Visual Basic 6.0, a different result may be returned than when using it in a cell formula of an Excel spreadsheet.

The VBA Round() function uses Banker's rounding while the spreadsheet cell function uses arithmetic rounding.

The Round() function in an Excel spreadsheet uses Arithmetic rounding, which always rounds .5 up (away from 0). The Round() function in Visual Basic for Applications 6, uses Banker's rounding, which rounds .5 either up or down, whichever will result in an even number.

Steps to Reproduce Behavior

In Excel, open a new spreadsheet and type the following formula into one of the cells:
=Round(2.5, 0)

The result is 3.

In Visual Basic 6.0 or other applications using VBA 6, open a new project and type the following expression into the Debug or Immediate window:
? Round(2.5, 0)

The result is 2.

Regards, TMS

Excel VBA Round-function

LinkBack

Thread Tools

Rate This Thread

Display

Excel VBA Round-function

Re: Excel VBA Round-function

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Numbers in Excel won't actually round. How do I get them to round up

[SOLVED] Conditional Round up or Round up Function

Excel if or round function?

VB Function Round vs Excel function Round not behaving the same Od

[SOLVED] round function in excel 2000

Bookmarks

Bookmarks

Posting Permissions