I have a column of numbers in decimal format. How can I apply ROUND function to the entire column of existing numbers, say a formula like
=ROUND(theCellValue,0)
If this was not specifically about rounding at all, only the ‘Fill’ tool is of interest here. It was recently moved from ‘Edit’ to ‘Sheet’.
The other aspects:
If the values in the column are calculated, the appropriate way to do it is exactly as you posted, except that it might be clearer to write: =ROUND(theOriginalExpression;0). You have to use the ‘Fill’ tool then to update the remaining cells of the column.
If the values are entered manually you have the option to simply enter them already rounded - unrounded values los then - or to use a helper column, say at the right, where you apply the formula posted in the question, or probably better the variant =IF(ISNUMBER(theCellValue);ROUND(theCellValue;0);""). If the data are imported, the helper column should be the only recommendable way.
There is one remaining dilemma. If you actually have in use very many cells of the column (>10000 e.g), the formulae filled into the adjacent column will increase the filesize and make the sheet less efficient.
If this gets very urgent, the only way I can think of is a bit of user programming. If applicable, ask again.
(There is also the option ‘Precision as shown’ in this context. I would, however, dissuade from using it, because there are unclear and probably not stable implications.)
Thank you for a detailed reply sir. Actually the data is imported from a text file. I can’t go on on adding the formula to each cell. So, I thought there would be an easier way to add a formula to the entire column. Creating a helper column and then copy paste with the new values is also tedious job as the file is a big one.
It is very simple to get a formula work in a huge range of cells. It is the ‘Fill’ tool that is made to do so. Virtually no formula is applied to “a complete column” in any spreadsheet. This would be 2^20 cells , much more than a million. Working with data at that scale is the job special programs or, of database applications.
Reading your statements I feel not quite sure that you regard the fact that a cell already containing data cannot take a formula working on tthe data at the same time.
I am still wondering what you actually have and what you actually want, precisely.
I thought I could add a formula to the already existing values.
But the “Decrease Decimals” button helps me out by decreasing the decimals.
Thanks for your valuable time @Lupp
@adam1969: Thank you for drawing my attention to that tool I never had used before.
Please note that the tool doesn’t do any actual rounding. Its label ‘Delete Decimal Place’ may be misleading with this respect. It only changes the ‘Numbers’ format setting for the cells concerned. The unrounded values remains content (or formula result) of the cells.
Reagards
Yes sir, I know that it won’t round the number actally. But is good enough to get a clear view of the numbers as I need. Unfortunately there is no easy way to add a formula to cells already containing values.