Round Function

[omletto]

Hello,

I have recently taken on a new role, and it involves using a lot of Spreadsheets set up by my predecessor. The problem I have is that he never used the Round,2 function and as a result i get a lot of "false" returns when using the information as the results are e.g. 13.000001. I can use a ctrl shift 1 to make it look correct to 2dp, but is there a way of mass formatting the spreadsheet to give results in 2DP.

Any help would be appreciated.

R's

Om

[oeldere]

Without a excel file, without confidential information, it's hard to tell.

Please also add the desired result in your file.

[omletto]

Please find attached example

[Sam Capricci]

for me the simplistic way I'd do it is just format the cell you want then use the format painter, double click on it and then highlight all the areas you want as 2 decimal places and you'll have it.

[omletto]

Wont that just make it look like two decimal places without changing it?

[Pepe Le Mokko]

Perhaps have a look at https://support.microsoft.com/kb/78113

[Sam Capricci]

Guess I misunderstood because you mentioned formatting in the first post so I went with a formatting answer.

[AlKey]

Try this on your sheet

=IFERROR(1/(1/IF(MOD(B4,1)=0,B4,TRUNC(B4,2))),"")

	H	I	J	K
4	2306	2456	2414	2111
5
6	2121	2541	2124	1875
7	42.42	50.82	42.48	37.5
8	82.71	99.09	82.83	73.12
9	121.32	145.34	121.49	107.25
10	53.66	64.28	53.73	47.43

[omletto]

Where would I type that?

[joeu2004]

Try this on your sheet
=IFERROR(1/(1/IF(MOD(B4,1)=0,B4,TRUNC(B4,2))),"")

Where would I type that?

Why would you even consider it?

First, it truncates; it does not round, as you requested. For example, if B4 is 42.415, the formula converts it 42.41 instead of rounding to 42.42.

Second, the formula has the potential for converting perfectly good integers to values that are infinitesimally different, which can screw up some dependent calculations. For example, if B4 is 3306 and the formula is in C4, then MATCH(3306,C4,0) returns #N/A (no match), and IF(B4-C4=0,TRUE) returns FALSE.

(Caveat: But IF(B4=C4,TRUE) returns TRUE(!), and =B4-C4 returns exactly zero(!). This is due to a dubious and inconsistent heuristic that allows Excel to treat some values as equal when, in fact, they are not.)

Frankly, it's the most baroque "solution" I've ever seen. Why not simply write =ROUND(B4,2)?

[AlKey]

On your example in H4

You can use this smaller version

=IFERROR(1/(1/TRUNC(B4,2)),"")

[omletto]

I would then be creating a duplicate set of data, I was hoping to change the original data.

[AlKey]

I would then be creating a duplicate set of data, I was hoping to change the original data.

This is an example! If you need to use with your calculations then just put your calculations in formula instead of cell reference.

=IFERROR(1/(1/TRUNC(B6*2%,2)),"")

[joeu2004]

I have recently taken on a new role, and it involves using a lot of Spreadsheets set up by my predecessor. The problem I have is that he never used the Round,2 function and as a result i get a lot of "false" returns when using the information as the results are e.g. 13.000001. I can use a ctrl shift 1 to make it look correct to 2dp, but is there a way of mass formatting the spreadsheet to give results in 2DP.

Yes, but I do not recommend it. Before trying anything, be sure to make a backup copy of the file.

You can set the calculation option "Precision as displayed" (PAD).

First, be sure the cells are formatted with 2 decimal places.

Then, in Excel 2010, click on File, Options, Advanced, then page down until you see the option, and select it.

One problem with PAD is: its effect is pervasive and indiscriminatory. For example, if you calculate a daily interest rate in a cell, and you format it to display 4 percentage decimal places (i.e. 6 decimal places numerically), when you set PAD, the actual value of the cell will be rounded to 4 pdp. And that will impact all subsequent calculations.

Normally, deselecting PAD or reformatting with more decimal places corrects such unintended conversions.

However, the biggest danger of PAD is: such conversions of constants are irreversible.

Another problem with PAD is: it only affects the final value of a cell. It does not affect subexpressions. For example, IF(10.1 - 10 = 0.1, TRUE) still returns FALSE(!). We still must correct such problems by explicitly rounding, i.e.
IF(ROUND(10.1 - 10, 2) = 0.1, TRUE).

There are many more issues with PAD. That is why I do not recommend its use.