The Round function

[amauer]

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>

 

[oldbrewer]

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]

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]

Excellent! I will convert to text, Thanks!

 

[xenou]

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]

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]

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.
ξ

 

[mortgageman]

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]

Why is my answer incorrect - unless you impose user defined requirements for rounding.

 

[xenou]

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.