Variation in actual cell value and formula bar value

[popvel]

Hi:

I have two small questions pertaining to formulas/display formats.

1. When I enter any date, say for example 3-Apr-2011, in the cell, in the formula bar it appears as 03/04/2011. Why it is so? How can we make it appear in the formula bar exactly as it is appearing in the cell? I follow US English format.

2. In the cell, the value is 97.64 but in the formula bar, it appears as 97.640000042. It happens for all the numbers entered in the cells. The cells are formated to show only 2 decimal points but I want to convert all the numbers to this format. There should be no variation in the value in te cell and in the formula bar.

[Richard Buttrey]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[Ace_XL]

1. Excel stores the date as a number, hence the display in the formula bar will not be text as you desire, although the display is!

2. Formatting of cells is for display purposes only, the value underlying will display in the formula bar (as in 1 above). You could use the Round(A1,2) function to round numbers to 2 decimal places. Also, below is some reading on rounding and precision

http://www.cpearson.com/excel/rounding.htm

[popvel]

Hi Ace_XL:

Thanks for your explanation and also the link which was quite useful.

I tried doing Round(A1,2) on values in Column B, to convert all Column B values to 2 decimal numbers but got errors (circular reference or something of that sort). How can I do the conversion in the same column itself?

[Ace_XL]

Do the rounding off in a separate column (Say Column C), then copy column C and paste special values in your original column (Column B). Delete Column C after that!