Do not Round but only display 2 decimal places

[ukbill]

Hi

I want to show the results of multiplying a number by a factor that often results in 6 decimal places but I only want to display the first 2 decimal places WITHOUT rounding up or down.

Whatever I try on formatting with # or zeros rounds up the display. Excel will treat the value as the original in other calculations but it is important for me to only SHOW the first two decimal places correcltly.

Anyone any ideas - its driving me mad.

 

[Juan Pablo González]

You 'show' the values by using cell formatting, but, that won't change the underlaying value of the cell. So, if you want to 'use' only the first 2 decimals, you WILL have to use some rounding formulas...

 

[Blue Hornet]

If I understand you correctly, you want to show the product of two numbers correct to two decimal places, but you don't want to round up or down. So simply changing the display to show two decimal places won't work, because that does round. Right so far?

I haven't tested this exhaustively, but it seems to work as you wish:
=FLOOR(D16*C16, IF(D16*C16>0, 0.01, -0.01))

Just change the display to only show two decimal places, and this should not round.

HTH,
Chris

 

[Travis]

Note, that this will change all calculations to not include the dropped decimals.

Book1
	A	B	C	D
1	5.239	5.23
2	100.227	100.22
Sheet1