# The Round function

#### amauer

##### New Member
Hello,
I have a PDF mail merge job that pulls values (currency with 4+ decimal places) from an Excel spreed sheet (.xls). I am using the Round() function to reduce the decimal places to 2. The number is then moved to a text box on a PDF form. All cells and text boxes (in pdf) are set to 2 decimal places, but the values on the pdf text boxes show 4 decimal places. The pdf document divides one text box by 2 then subtracts another number. I end up (sometimes) with a very long decimal value.

Does the Round() function just change the apparent value and store the old value in the document?

</SPAN>

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### oldbrewer

##### Well-known Member
similar to formatting a date time value to ddd it returns MON but date and time still there

value = 24.5887 add .005 =24.5937

suggest you use text(A1,"##.####") if A1 contains 24.5937 - then left(B1,5) gives you 24.59

then value (c1) gives you 24.59 - exactly 24.59

#### Rick Rothstein

##### MrExcel MVP
similar to formatting a date time value to ddd it returns MON but date and time still there

value = 24.5887 add .005 =24.5937

suggest you use text(A1,"##.####") if A1 contains 24.5937 - then left(B1,5) gives you 24.59

then value (c1) gives you 24.59 - exactly 24.59
Typo alert... this should have been...

Text(A1,"#.##")

#### amauer

##### New Member
Excellent! I will convert to text, Thanks!

#### xenou

##### MrExcel MVP

Round changes the value returned. The "old value" isn't stored (except of course if the values being rounded is still stored in the workbook, for instance in another cell).

#### amauer

##### New Member
Round changes the value returned. The "old value" isn't stored (except of course if the values being rounded is still stored in the workbook, for instance in another cell).

This appears to conflict with oldbrewer's answer. Can I get some clarification?

#### xenou

##### MrExcel MVP

My answer is correct, oldbrewer's is not.

This can be demonstrated:
=(ROUND(50.002,3)*1000)=(50.002*1000) ==> True (same numbers)
=(ROUND(50.002,2)*1000)=(50.002*1000) ==> False (the first number has been changed to 50.00)

However, if the text conversion works for you then it's moot.

*My answer is assuming you are somehow using the ROUND() function, based on your post title and the text of your original question. I guess I'm not sure how the pdf is getting these values or if they have their own properties for formatting and/or mathematical operations on the pdf side - what you are doing is not something I am familiar with.
ξ

Last edited:

#### mortgageman

##### Well-known Member
My answer is correct, oldbrewer's is not.

This can be demonstrated:
=(ROUND(50.002,3)*1000)=(50.002*1000) ==> True (same numbers)
=(ROUND(50.002,2)*1000)=(50.002*1000) ==> False (the first number has been changed to 50.00)

However, if the text conversion works for you then it's moot. The problem is not really about the values as about how they are formatted (which has to do with what's going on in these textboxes).

*My answer is assuming you are somehow using the ROUND() function, based on your post title and the text of your original question.
ξ

To add some worthless air into this discussion:
The round function is in fact a function. It "returns" a value into the cell just like any other function. Any function "changes" the input value (unless the function is a constant function and happens to return the input value)

Gene

#### oldbrewer

##### Well-known Member
Why is my answer incorrect - unless you impose user defined requirements for rounding.

#### xenou

##### MrExcel MVP
You can follow another demonstration like this:
Set A1 = 5.001
Set A2 = Round(A1,2)

Then copy the two cells, paste values, and view the result. Cell A1 will be the value 5.001, Cell A2 will be the value 5. This shows that the value of the cell in A2, after rounding, is really 5, not 5.001.

Replies
8
Views
259
Replies
2
Views
381
Replies
7
Views
414
Replies
3
Views
315
Replies
6
Views
263

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,951
Messages
5,834,552
Members
430,295
Latest member
amdis

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back