How to remove trailing zeros in formula bar

Question

Hi,

I have summed up a particular column and the the total in the cell is appearing to be rounded (2 decimals) but upon looking in the formula bar, there are multiple trailing zeros. All the other columns that have been summed are correct except this one. It also appears in my pivot, as well. Please advise how to remove the trailing zeros and only have it formatted to 2 decimal places?

Answer 1

If the number is the result of a formula =SUM(...), change the formula to =ROUND(SUM(...),2)

Answer 2

If the number is the result of a formula =SUM(...), change the formula to =ROUND(SUM(...),2)

I did that as well. I inputted =Round(Roundup(A1,3),2) but it still shows trailing zeros after the decimal.

Answer 3

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

Answer 4

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

Unfortunately I am not authorized using another site but please see below. I have pasted the data and the one highlighted (at the bottom) when copied into a new worksheet will be shown as 768.000000000001.

FYI...No other column from the dataset has the same issue.

-3.24
-0.71
-3.24
-3.24
-5.09
-1.20
21.53
-0.84
-10.47
0.87
-0.74
-1.74
21.26
-8.38
-9.73
-3.59
1.27
2.58
5.13
10.55
-5.51
8.92
-7.58
-3.81
1.94
1.94
-10.78
7.35
35.20
-9.73
2.94
2.94
-9.31
14.37
0.90
6.70
-5.29
11.78
17.94
3.71
1.96
18.32
2.54
6.32
8.28
12.54
1.61
50.10
10.23
49.54
1.23
6.01
5.95
0.45
4.03
0.41
0.21
1.77
10.47
0.42
6.65
5.82
19.82
27.74
2.09
6.09
1.10
4.76
1.10
6.68
3.37
3.19
43.63
1.41
10.79
19.19
2.09
0.21
4.31
0.80
4.18
12.08
22.96
7.87
2.04
24.96
23.57
1.54
0.79
0.71
3.24
11.33
2.37
1.20
1.49
0.94
0.22
10.15
2.78
22.98
10.13
7.91
11.27
47.59
-9.73
0.44
0.62
0.36
4.40
8.07
3.37
0.86
6.73
5.56
3.71
0.36
8.90
13.70
1.79
8.83
0.70
13.24
0.10
0.14
0.45
-4.42
0.83
6.71
0.94
0.27
16.07
1.87
768
768

Answer 5

Thanks for posting the data. Do any of the cells contain a formula? If so, which ones, and which formula?