The Round function

amauer

New Member
Joined
May 11, 2012
Messages
33
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>
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
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
Joined
Apr 18, 2011
Messages
36,711
Office Version
  1. 2010
Platform
  1. Windows
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,"#.##")
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 11, 2012
Messages
33
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
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 30, 2005
Messages
2,015
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

Board Regular
Joined
Apr 11, 2010
Messages
11,005
Why is my answer incorrect - unless you impose user defined requirements for rounding.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top