Only want 2 decimal spaces

[StreekyD]

Hello Group,
Column A is a series of prices. Column B is the result I am looking for.
When you click in column A several decimal places come up. I only want two to show rounded.
Formatting in excel does not do what I want and someone gave me this formula with instructions that does not seem to function.


In the first cell of your new column add so lets assume you are copying/converting data from column A, in the new column B add:
=TEXT(A1,”0.00”)
Then copy down the whole length of the document. This will give you the numbers in a 3 digit form without rounding up……but it will still have a formula, so you need to them insert another column. Then copy the whole new column B with the formula you just made, and paste, when you paste the whole column with excel it will ask you special paste, and you choose VALUES. Then without un-highlighting the column it will say error, format ! when you hit the options it will say values. Click values, you need to spot check the document, you will see your final column will only have 0.00 values in number no rounding, no formulas. You can then delete your other 2 columns.

[MrShorty]

Grabbing the low hanging fruit here -- why are you not simply using =ROUND(A2,2)? Or =ROUND(current formula in A2,2)?

[StreekyD]

WELL, if you must know why. it is because I didn't know to do that. I was told to use the formula as described by someone else.

I will assume you are not insulting me and I will thank you for the solution.

You have saved me a lot of time. I am NOT a formula expert and use this site quite often and find it extremely helpful.

[StreekyD]

MR Shorty Maybe I will ask for something more difficult later

[MrShorty]

I certainly did not mean any disrespect, and I apologize if I came across that way. As often as we talk about Excel's different rounding functions, it just gets to be too easy to assume that everyone is aware of Excel's battery of rounding functions.

[StreekyD]

Thank you, I did try to research this question because I thought this must have been addressed. BUT you have to know what to "ask" for. I was asked not to round the data off because it may alter the end result. Because your approach works problem solved. Only 500 rows to check.

I have a lot of data that I work some in ACCESS and some in EXCEL and I only know a few tricks. I come here for help when I need it. Sometimes I ask my questions a different way and get better results. I appreciate all the help I get here. Some of the data i work with contains 2.5 million rows and more.Then I have to message that into reports and guides for various people. I know what I want I just don't know how to get there sometimes. Look at my questions along the way.

Thank you once again.