How to set (not display) significant figure for cells/columnss?

Hi there,

I tried to set significant figures for a number column but couldn't do it. For instance, to set 100.123456789 as 100.12. I can limit the number to show up in cells (to display as 100.12), but on the formula bar, it is still the same (100.123456789). I like to set certain significant figures in both formula bar and cell.

Please help.

Thanks,
Ning

You are wanting to round all the cell values, not change the display.

I dont know of a way, but I also dont see the need. If you want to change the raw values then change them, if you want to change the formatting then change it. Why are you trying to do half of one and half of another? it just doesnt make sense.

Excel stores values in cells as double precision variables, and does not really have a "currency" data type the way some programming languages do.

You could try setting the Excel option for "precision as displayed". Then, when you format to 2 digits past the decimal, that is the value Excel will use in calculations. However, precision as displayed is a global option (cannot be restricted to a column) and it may or may not have an effect on the values sent to the downstream financial system.

Almost always, the best solution for something like this is to use one of Excel's ROUND() functions. If you cannot modify the data given to you, and you must change it so that it fits into the downstream system, I think you will need to program a spreadsheet/column/tab/file that will take the data as given, employ some appropriate rounding functions in helper cells, and output data that the downstream system can use. Kind of "bridge" between the data you are given and the data you need to send. If it really is as simple as rounding the input data to 2 decimal places, this "bridge" might be as simple and easy as =ROUND(A1,2) [copied as necessary]. Of course, in many ways this is equivalent to "copying"/"pasting" the data, which you have indicated you would not be allowed to do.

Perhaps if we better understood the nature of your requirements and restrictions. One way or another, it seems like you will need to employ a rounding function somewhere to do this well. It sounds like you have very few options available, and figuring out a suitable approach will depend on the exact nature of those limitations.